Losing Oracle user name and password

I have written a simple SQL Server 2005 package to pull some data from Oracle (using ODBC) and pumping it into SQL Server. When I run it from the server in debug mode in VS it works fine. When I schedule the job it errors out with "ora-01005: null password given; logon denied." The password is there. Has anyone experienced this? Is there a security setting somewhere preventing me from saving passwords? Is there a work around? Thanks.

Passwords are not saved in a package unless in an encrypted format. Check the ProtectionLevel property of your package to set this up.

-Jamie

|||Thanks, that was it.sql

Losing double quotes?

Hi,

I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.

I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").

The vast majority of the records are formatted properly, and have the double quotes in the expected locations.

The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...

i apologize for being the worst ever at posting questions here, please let me know if i can add anything

Could you please post your code, its hard to uess what is goind wron without seeing your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

it is over 500 lines, but i could certainly post it once i get to work...

in the meantime, however, perhaps this might be useful:

I set a breakpoint at my function's final return statement and have observed the return value as follows:

Author of "Some random book." Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

This is how I'd like, and expect, the value to appear in my database, but after SELECTING the particular row, I get:

Author of Some random book. Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

I doubt this is much more revealing, and will be happy to post my code later, if thought to be any help.

|||

Another thing to add (probably useless):

The problem only occurs during an UPDATE. If I do:

SELECT dbo.EventBuilder([Title], [Author], [Published]) FROM MyTable

I get the correct output.

...getting desperate here

|||

Check your database hold the same result.

I can't able to understand where & what is your problem occurs..

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

Looping in stored procedures

Hi

On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.

All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.

What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.

What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.

I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.

Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!

Thanking you in advance.

SteveSteve:

You have to use a FETCH statement to loop through a result set in SQL Server. However this is VERY inefficient. It is VERY slow. I would not reccomend it to anyone unless it is absolutly necessary.

On the other hand, you should be able to accomplish the search using a query. If you can post some data from your tables and the struc of the tables then I would be glad to help you write a query to get what you need.

HTH|||Hi Wes

Thanks for the reply and offer to help.

I have got two tables:
tblSTATISTCS and tblFACTFILES

tblSTATISTICS is made up of four columns which are ID(Primary Key), Type (different methods of finding a course i.e. search, courselist ect..), Name (what user typed in or selected) and Date

I have now run a view called queSEARCH which picks out all the distinct names which have 'search' as its type. So in effect tblSTATISTIC ahs gone from

ID Type Name Date
1 Search it 11/11/03
2 Courselist Business 11/11/03
3 Search Art 11/11/03
4 Search Web Design 11/11/03

to... queSEARCH

 Name
 it
 Art
Web Design

So I guess what I need to do now is take each row in queSEARCH and use a CONTAINSTABLE statement for tblFACTFILE in a loop?

tblFACTFILE contains quite a few rows so I have shorted this down to the important ones

Course Code CourseTitle Keywords
10001 Art for beginners Artist, painting, drawing
10002 Computing for beginners Web Design, IT, PC
10003 HND in Computing IT, PC, ASP

I hope this makes sence and thanks again for you offer of help.

Steve|||Steve:

I was following you up until the end. What is the end result that you are looking for? I am sure we can do this in one query rather than a loop.|||Wes

What I want is to find all of the word in queSEARCH which do not match anything in either coursetitle or Keywords. So if the word potatoes was added to the list of words people searched with and it was run against the courses I listed earlier, potatoes would remain in the list. Where as everything else (art, it, web design) wouldn't because it matches at least one of the factfiles.

In effect I would like to produce a page of results to show potential keywords that we are not using already within our factfiles.

I hope this is a little clearer.

Cheers
Steve|||You might be better off constructing a SQL string and sp_executing it.|||I think I am now at the point you were at when you started this thread. Due to the result set that you want it can't be done using a sub query (which I am sure you already knew). I now understand you question perfect, in that you must use a Fetch loop to go through the queSearch table one row at a time and then query to tblFactFile to see if there are any matches. You are going to have to use the CONTAINS clause rather than the CONTAINSTABLE because you can use a variable with the CONTAINS where you can't with the CONTAINSTABLE. Here is the code that you were originally looking for:

DECLARE @.currentName as varchar(50)
DECLARE myCursor CURSOR FOR
SELECT [name] FROM queSearch
--your cursor is now filled with the queSearch results
OPEN myCursor
-- Perform the first fetch.
FETCH NEXT FROM myCursor INTO @.currentName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
 Select CourseCode FROM tblFactFile WHERE CONTAINS(Keywords,@.currentName) OR CONTAINS(CourseTitle,@.currentName)
 IF @.@.rowcount = 0
 BEGIN
-- IT did not find any matches, therefore do what you need to do
 END
 -- This is executed as long as the previous fetch succeeds.
 FETCH NEXT FROM myCursor INTO @.currentName
END
CLOSE myCursor
DEALLOCATE myCursor
GO

|||Thanks Wes.

I will give this a try tomorrow and let you know how I get on.|||Wes

That peice of code work great. Thanks very much for taking the time to help.|||I am glad to help.

Looking For Examples Of Stored Procedures in C# or VB.Net.

Hi,

Does anyone have any links to examples of stored procedures written in C# or VB.Net instead of T-Sql. I'd like to see how it looks and works. Thanks! Ken.Hi Ken,

Here are some links for writing stored procedures in .Net compatible language.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://www.sqlteam.com/item.asp?ItemID=21927

If you have any concerns please revert back, i will send you the sample code.

Best Regards,
Swamy|||The SQL Server Hands On Labs have examples:

http://msdn.microsoft.com/sql/2005/2005labs/default.aspx

See the SQL CLR lab.|||There are several examples available if you installed the samples and documentation. They are located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR by default.