Cursor

Hi
I am creating a cursor which runs through a table of database names and
execute a script using xp_cmdshell on every database in that table.
Declare @.DataBaseName varchar(255)
Declare DBcursor CURSOR FOR
Select dbname From master..upgradedb
Declare @.command varchar(255)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @.DataBaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Upgrading.... ' + @.DataBaseName
set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
exec master..xp_cmdshell @.command
FETCH NEXT FROM DBCursor INTO @.DataBaseName
END
CLOSE DBCursor
DEALLOCATE DBCursor
I get the followinf results - Can anyone guide me in the right direction
please.
Upgrading.... QFM161R23AMEYAA
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)
Upgrading.... QFM161R23GLSCHOOLS
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
If you had printed out the command before trying to execute it for the first
time, you'd have noticed that there's a syntax error in it. A space is
missing before the -i switch.
But I'm just guessing here.
ML|||Check if sp_MSforeachdb System Stored Procedure can be used.
This will simplify the code. U may not require cursor at all.
Rakesh
"Jaco" wrote:

> Hi
> I am creating a cursor which runs through a table of database names and
> execute a script using xp_cmdshell on every database in that table.
> Declare @.DataBaseName varchar(255)
> Declare DBcursor CURSOR FOR
> Select dbname From master..upgradedb
> Declare @.command varchar(255)
>
> OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Upgrading.... ' + @.DataBaseName
> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
> c:\temp\Complete(7-9).sql'
> exec master..xp_cmdshell @.command
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> END
> CLOSE DBCursor
> DEALLOCATE DBCursor
> I get the followinf results - Can anyone guide me in the right direction
> please.
> Upgrading.... QFM161R23AMEYAA
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
> Upgrading.... QFM161R23GLSCHOOLS
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
>sql

ct_connect errors in xp_cmdshell

I am running a bcp from xp_cmdshell, it was working good
EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.t xt -c -t~ -SSYN0001
-UUserName -Ppassword '
The server is Microsoft SQL Server 2000 - 8.00.760
Then I installed sybase client in the PC inorder to get replication from
sybase to sql server.
It is giving the following error message.
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
NULL
Any help why is this happenning?
The server, username, password are all correct and I am able to work through
the enterprise manager and query analyser, its gving error only in the
xp_cmdshell and bcp.
After the install, the xp_cmdshell and bcp is not working anymore.
Hi
The Sybase BCP.exe is now being used by your call due to the environment
path settings written by the Sybase setup.
Fully qualify the BCP.exe path in the call and it will work. osql.exe is
also affected in the same way.
Regards
Mike
"Yog" wrote:

> I am running a bcp from xp_cmdshell, it was working good
> EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.t xt -c -t~ -SSYN0001
> -UUserName -Ppassword '
> The server is Microsoft SQL Server 2000 - 8.00.760
> Then I installed sybase client in the PC inorder to get replication from
> sybase to sql server.
> It is giving the following error message.
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> NULL
> Any help why is this happenning?
> The server, username, password are all correct and I am able to work through
> the enterprise manager and query analyser, its gving error only in the
> xp_cmdshell and bcp.
> After the install, the xp_cmdshell and bcp is not working anymore.
>
>
|||Thank you Mike, that worked.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> The Sybase BCP.exe is now being used by your call due to the environment
> path settings written by the Sybase setup.
> Fully qualify the BCP.exe path in the call and it will work. osql.exe is
> also affected in the same way.
> Regards
> Mike
> "Yog" wrote:

ct_connect errors in xp_cmdshell

I am running a bcp from xp_cmdshell, it was working good
EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
-UUserName -Ppassword '
The server is Microsoft SQL Server 2000 - 8.00.760
Then I installed sybase client in the PC inorder to get replication from
sybase to sql server.
It is giving the following error message.
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
NULL
Any help why is this happenning?
The server, username, password are all correct and I am able to work through
the enterprise manager and query analyser, its gving error only in the
xp_cmdshell and bcp.
After the install, the xp_cmdshell and bcp is not working anymore.Hi
The Sybase BCP.exe is now being used by your call due to the environment
path settings written by the Sybase setup.
Fully qualify the BCP.exe path in the call and it will work. osql.exe is
also affected in the same way.
Regards
Mike
"Yog" wrote:
> I am running a bcp from xp_cmdshell, it was working good
> EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
> -UUserName -Ppassword '
> The server is Microsoft SQL Server 2000 - 8.00.760
> Then I installed sybase client in the PC inorder to get replication from
> sybase to sql server.
> It is giving the following error message.
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> NULL
> Any help why is this happenning?
> The server, username, password are all correct and I am able to work through
> the enterprise manager and query analyser, its gving error only in the
> xp_cmdshell and bcp.
> After the install, the xp_cmdshell and bcp is not working anymore.
>
>|||Thank you Mike, that worked.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The Sybase BCP.exe is now being used by your call due to the environment
> path settings written by the Sybase setup.
> Fully qualify the BCP.exe path in the call and it will work. osql.exe is
> also affected in the same way.
> Regards
> Mike
> "Yog" wrote:
> > I am running a bcp from xp_cmdshell, it was working good
> >
> > EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> > c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
> > -UUserName -Ppassword '
> >
> > The server is Microsoft SQL Server 2000 - 8.00.760
> >
> > Then I installed sybase client in the PC inorder to get replication from
> > sybase to sql server.
> >
> > It is giving the following error message.
> >
> > CTLIB Message: - L6/O8/S5/N3/5/0:
> > ct_connect(): directory service layer: internal directory control layer
> > error: Requested server name not found.
> > Establishing connection failed.
> > NULL
> >
> > Any help why is this happenning?
> >
> > The server, username, password are all correct and I am able to work through
> > the enterprise manager and query analyser, its gving error only in the
> > xp_cmdshell and bcp.
> >
> > After the install, the xp_cmdshell and bcp is not working anymore.
> >
> >
> >