Orphaned Sessions and locks: command

Showing posts with label command. Show all posts

Wednesday, March 21, 2012

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh

I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:

http://support.microsoft.com/kb/911716

Buck Woody

Out of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

Content of the .asp file is
<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

Thanks in advance
Dinesh
You need to troubleshoot from ASP side. It is unlikely this has anything to do with SQL or the batch file itself.

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

Thanks in advance
Dinesh

http://support.microsoft.com/kb/911716

Buck Woody

Tuesday, March 20, 2012

Other ways to execute Integration package?

Hi all,
I am just wondering is there any other ways to execute an integration service package other than using sql server agent, dos command and manually execute the package in BIDS? I am thinking of running the package on web, is this possible?
What I am trying to do is let user to run the integration service when they need to, so instead using SQL Server Management Studio to run the task being set in SQL Server Agent, I am thinking of something where user can log into a website (like the report server) and then run the integration, so that the data for the report server's reports are being update. Please help out if any know a solution to it. Thanks in advance.

Daren
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=110020&SiteID=1

Friday, March 9, 2012

OSQL restore Database

I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!

Best Regards
PhilipI'd use a VBS script. Easier then a full application...|||Thanks

OSQL question

From a command prompt I try to connect to my newly installed isntance of
MSDE. I use the following:
osql -U sa
and I get:
[SHARED MEMORY] Invalid Connection
[SHARED MEMORY] ConnectionOpen(InvalidInstance())
What am I doing wrong here?
Thanks for the information.
Brad
Did you install a named instance?
Specify the server name on the osql command line.
Also, I hope you didn't setup your MSDE instance with a blank password.
Jim
"Brad" <allcomppc@.sbcglobal.net> wrote in message
news:OsYsz1dyEHA.1204@.TK2MSFTNGP10.phx.gbl...
> From a command prompt I try to connect to my newly installed isntance of
> MSDE. I use the following:
> osql -U sa
> and I get:
> [SHARED MEMORY] Invalid Connection
> [SHARED MEMORY] ConnectionOpen(InvalidInstance())
> What am I doing wrong here?
> Thanks for the information.
> Brad
>
|||I did install a named instance with also setting the sa password.
I tried using the -S <server> but I get the same message.
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
> Did you install a named instance?
> Specify the server name on the osql command line.
> Also, I hope you didn't setup your MSDE instance with a blank password.
> Jim
> "Brad" <allcomppc@.sbcglobal.net> wrote in message
> news:OsYsz1dyEHA.1204@.TK2MSFTNGP10.phx.gbl...
>
|||try OSQL.EXE -L
[vbcol=seagreen]
>I did install a named instance with also setting the sa password.
> I tried using the -S <server> but I get the same message.
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
|||Try -S <server>\<Instance name>
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Brad" <allcomppc@.sbcglobal.net> wrote in message
news:%23PKaFvmyEHA.2316@.TK2MSFTNGP15.phx.gbl...
>I did install a named instance with also setting the sa password.
> I tried using the -S <server> but I get the same message.
> Brad
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
>

OSQL query command

I am trying to execute a job through the OSQL command but
the job never executes. Can anyone help me with the
syntax ?. I am executing it on the server:
C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
Job for DB Maintenance Plan TEST'"
If there is a way, I would also like to execute this
remote.
Thanks for any help.Hi,
Try firing the below query from Query analyzer .
msdb..sp_start_job @.job_name = 'DB Backup Job for DB Maintenance Plan TEST'"
Thanks
Hari
MCDBA
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
> I am trying to execute a job through the OSQL command but
> the job never executes. Can anyone help me with the
> syntax ?. I am executing it on the server:
> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
> Job for DB Maintenance Plan TEST'"
> If there is a way, I would also like to execute this
> remote.
> Thanks for any help.|||Thanks. I found what was wrong. This syntax works:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan TEST'
But now I have another problem. The job name actually is:
DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
Default when you create a maintenance plan)
so when I enter
'DB Backup Job for DB Maintenance Plan 'TEST''
it doesn't work.
How do I deal with the single quotes. Should I just rename
the jobs without single quotes ?
Thanks.
>--Original Message--
>Hi,
>Try firing the below query from Query analyzer .
>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
Maintenance Plan TEST'"
>Thanks
>Hari
>MCDBA
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
>> I am trying to execute a job through the OSQL command
but
>> the job never executes. Can anyone help me with the
>> syntax ?. I am executing it on the server:
>> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB
Backup
>> Job for DB Maintenance Plan TEST'"
>> If there is a way, I would also like to execute this
>> remote.
>> Thanks for any help.
>
>.
>|||I got that too: ''TEST''' would do it.
Thanks.
>--Original Message--
>Thanks. I found what was wrong. This syntax works:
>osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
>= 'DB Backup Job for DB Maintenance Plan TEST'
>
>But now I have another problem. The job name actually is:
>DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
>Default when you create a maintenance plan)
>so when I enter
>'DB Backup Job for DB Maintenance Plan 'TEST''
>it doesn't work.
>How do I deal with the single quotes. Should I just
rename
>the jobs without single quotes ?
>Thanks.
>
>>--Original Message--
>>Hi,
>>Try firing the below query from Query analyzer .
>>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
>Maintenance Plan TEST'"
>>Thanks
>>Hari
>>MCDBA
>>"Rick" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
>> I am trying to execute a job through the OSQL command
>but
>> the job never executes. Can anyone help me with the
>> syntax ?. I am executing it on the server:
>> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB
>Backup
>> Job for DB Maintenance Plan TEST'"
>> If there is a way, I would also like to execute this
>> remote.
>> Thanks for any help.
>>
>>.
>.
>

OSQL query command

I am trying to execute a job through the OSQL command but
the job never executes. Can anyone help me with the
syntax ?. I am executing it on the server:
C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
Job for DB Maintenance Plan TEST'"
If there is a way, I would also like to execute this
remote.
Thanks for any help.
Hi,
Try firing the below query from Query analyzer .
msdb..sp_start_job @.job_name = 'DB Backup Job for DB Maintenance Plan TEST'"
Thanks
Hari
MCDBA
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
> I am trying to execute a job through the OSQL command but
> the job never executes. Can anyone help me with the
> syntax ?. I am executing it on the server:
> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
> Job for DB Maintenance Plan TEST'"
> If there is a way, I would also like to execute this
> remote.
> Thanks for any help.
|||Thanks. I found what was wrong. This syntax works:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan TEST'
But now I have another problem. The job name actually is:
DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
Default when you create a maintenance plan)
so when I enter
'DB Backup Job for DB Maintenance Plan 'TEST''
it doesn't work.
How do I deal with the single quotes. Should I just rename
the jobs without single quotes ?
Thanks.

>--Original Message--
>Hi,
>Try firing the below query from Query analyzer .
>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
Maintenance Plan TEST'"
>Thanks
>Hari
>MCDBA
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
but[vbcol=seagreen]
Backup
>
>.
>
|||I got that too: ''TEST''' would do it.
Thanks.

>--Original Message--
>Thanks. I found what was wrong. This syntax works:
>osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
>= 'DB Backup Job for DB Maintenance Plan TEST'
>
>But now I have another problem. The job name actually is:
>DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
>Default when you create a maintenance plan)
>so when I enter
>'DB Backup Job for DB Maintenance Plan 'TEST''
>it doesn't work.
>How do I deal with the single quotes. Should I just
rename
>the jobs without single quotes ?
>Thanks.
>
>Maintenance Plan TEST'"
>message
>but
>Backup
>.
>

OSQL query command

I am trying to execute a job through the OSQL command but
the job never executes. Can anyone help me with the
syntax ?. I am executing it on the server:
C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
Job for DB Maintenance Plan TEST'"
If there is a way, I would also like to execute this
remote.
Thanks for any help.Hi,
Try firing the below query from Query analyzer .
msdb..sp_start_job @.job_name = 'DB Backup Job for DB Maintenance Plan TEST'"
Thanks
Hari
MCDBA
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
> I am trying to execute a job through the OSQL command but
> the job never executes. Can anyone help me with the
> syntax ?. I am executing it on the server:
> C:\osql -E -d msdb -q "sp_start_job @.job_name = 'DB Backup
> Job for DB Maintenance Plan TEST'"
> If there is a way, I would also like to execute this
> remote.
> Thanks for any help.|||Thanks. I found what was wrong. This syntax works:
osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
= 'DB Backup Job for DB Maintenance Plan TEST'
But now I have another problem. The job name actually is:
DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
Default when you create a maintenance plan)
so when I enter
'DB Backup Job for DB Maintenance Plan 'TEST''
it doesn't work.
How do I deal with the single quotes. Should I just rename
the jobs without single quotes ?
Thanks.

>--Original Message--
>Hi,
>Try firing the below query from Query analyzer .
>msdb..sp_start_job @.job_name = 'DB Backup Job for DB
Maintenance Plan TEST'"
>Thanks
>Hari
>MCDBA
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b6e101c4377f$989bfb30$a601280a@.phx.gbl...
but[vbcol=seagreen]
Backup[vbcol=seagreen]
>
>.
>|||I got that too: ''TEST''' would do it.
Thanks.

>--Original Message--
>Thanks. I found what was wrong. This syntax works:
>osql -S MYSERVER -E -d msdb -Q "sp_start_job @.job_name
>= 'DB Backup Job for DB Maintenance Plan TEST'
>
>But now I have another problem. The job name actually is:
>DB Backup Job for DB Maintenance Plan 'TEST' (Microsoft
>Default when you create a maintenance plan)
>so when I enter
>'DB Backup Job for DB Maintenance Plan 'TEST''
>it doesn't work.
>How do I deal with the single quotes. Should I just
rename
>the jobs without single quotes ?
>Thanks.
>
>Maintenance Plan TEST'"
>message
>but
>Backup
>.
>

Wednesday, March 7, 2012

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VMIt shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
> Thanks,
> VM

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VM
It shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM
|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:

> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
> lists all the SQL Servers in the network or just windows servers within the
> network?
> Thanks,
> VM

OSQL -L

Hi All,
Can anyone please tell me if we issue "OSQL -L " at command prompt, does it
lists all the SQL Servers in the network or just windows servers within the
network?
Thanks,
VMIt shows you all broadcasted SQL Server instances over the net, whereas the
instances are hided for discovery.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"VM" <VM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:695DABDF-AE47-430A-9AF8-1E495BBF6C7D@.microsoft.com...
> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does
> it
> lists all the SQL Servers in the network or just windows servers within
> the
> network?
> Thanks,
> VM|||> Can anyone please tell me if we issue "OSQL -L " at command prompt, does it">
> lists all the SQL Servers in the network or just windows servers within th
e
> network?
SQL Servers. But be carefull, because if you create an alias using Client
Network Utility, this command will list it even if the alias is not
associated with a physical server.
AMB
"VM" wrote:

> Hi All,
> Can anyone please tell me if we issue "OSQL -L " at command prompt, does i
t
> lists all the SQL Servers in the network or just windows servers within th
e
> network?
> Thanks,
> VM

osql in a cmd file, with SQL statements coming from the same cmd f

I want to run osql in a command file. Instead of having the SQL statements
in a separate .sql file, using "osql -i file.sql..." I want to have the SQL
statements right there in the command file. This way my command file is
self-contained; only one file to worry about instead of separate cmd and sql
files.
In unix (or more precisely in the bash shell) this would be done by what is
called a "here document". Conceptually:
osql <<END_OF_SQL
select * from customers
select * from suppliers
END_OF_SQL
Any way to do this with OSQL, or by some CMD.EXE trick?
forestial wrote:
> I want to run osql in a command file. Instead of having the SQL
> statements in a separate .sql file, using "osql -i file.sql..." I
> want to have the SQL statements right there in the command file.
> This way my command file is self-contained; only one file to worry
> about instead of separate cmd and sql files.
> In unix (or more precisely in the bash shell) this would be done by
> what is called a "here document". Conceptually:
> osql <<END_OF_SQL
> select * from customers
> select * from suppliers
> END_OF_SQL
> Any way to do this with OSQL, or by some CMD.EXE trick?
Sure. You can create a CMD file to run the batch with contents like
this: Substitute -E to use a trusted connection which is recommended
over putting user id and password in the file. Use a capital Q to exit
OSQL immediately. Separate batches with Go and use -O for an output
file.
osql -Uuser -Ppassword (or -E) -Sserver -Q"Select id from sysobjects go
select id from sysindexes" -ooutput.txt
David Gugick
Imceda Software
www.imceda.com

OSQL- How to know the length of each column in the output file?

Hi,

I would like to use OSQL command to spool the output of a query to a text file. However, the length of the column in the text file is not same as the table column length declared. As I need to process the output file again , I would like to know exactly the start and the end position in the output file for each column. Delimiter doesn't help as the the logic will break if the value of one of the columns is having the delimiter.

My questions are:
1) How does the sql server calculate the length of each column when it spooled to a text file using OSQL?
2) How can I enclose the value with ""? For e.g., "record1"|"record2"|"record3"
3) If I use trial an error to get the length of each column in the output file, will the length of each column changes according to the value each time it runs?

drop table test
create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

osql -S servername -d dbname -E -Q "select * from test" -o "c:\test.log" -h-1 -n -w 8000 -s "|"

Thanks.FIRST!

EXCELLENT POST!

Having sample code makes it sooooooooooooo much easier

Second, I didn't test the bcp, but I did test the execution of the SQL...

Try this

USE Northwind
GO

create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

DECLARE @.cmd varchar(4000) DECLARE @.SQL varchar(4000)

SELECT @.SQL = 'SELECT ''"''+RTRIM(col1)+''"|"''+RTRIM(col2)+''"|"''+RTRIM(col3)+''"'' FROM test'
SELECT @.SQL
EXEC(@.SQL)

SELECT @.cmd = 'osql -S servername -d dbname -E -Q "'+ @.SQL + '" -o "c:\test.log" -h-1 -n -w 8000 -s "|"'
EXEC(@.cmd)
GO
DROP TABLE test
GO

Let us know how it works out...|||BTW, I would use the SQL with bcp with queryout...|||Hi,

First of all, thanks for your reply.

I can't use bcp as i need to call stored procedure (which reside externally in another database and I can't make any code change).

The logic behind is after calling the stored procedure and get the records, I need to spool all records into a text file and process the data -> load it to Oracle database. I can't use middle tier to write to a text file per records due to performance issue. As a result, I need to use OSQL to get the output.

Right now, I am having problem to identify the columns in the text file. Can you help with me quesitons above?

Any idea on that?

Thanks.|||Originally posted by Brett Kaiser
BTW, I would use the SQL with bcp with queryout...

and a format file along with it

osql command reference?

Hi!
I have been looking for a complete command reference to osql. A document
that describes all possible SQL commands that you acn use. Unfortunately,
I seem too stupid :/
I haven't found:
* the complete desciption of BACKUP and RESTORE
* a way to show the table definitions.
TIA,
Stefan
At command prompt, run the following command and it will show you all
switches available for OSQL.
OSQL /?
Also see the topic "osql utility" in SQL Server 2000 Books Online.
Similarly, SQL Server Books Online has complete documentation on BACUP and
RESTORE commands.
sp_help will give you table definitions and you can generate table creation
scripts in Enterprise Manager.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsbdo75yjs9ddfw@.news.individual.de...
Hi!
I have been looking for a complete command reference to osql. A document
that describes all possible SQL commands that you acn use. Unfortunately,
I seem too stupid :/
I haven't found:
* the complete desciption of BACKUP and RESTORE
* a way to show the table definitions.
TIA,
Stefan
|||On Mon, 19 Jul 2004 11:09:11 +0100, Narayana Vyas Kondreddi
<answer_me@.hotmail.com> wrote:

> At command prompt, run the following command and it will show you all
> switches available for OSQL.
> OSQL /?
These, I know, thanks

> Also see the topic "osql utility" in SQL Server 2000 Books Online.
> Similarly, SQL Server Books Online has complete documentation on BACUP
> and RESTORE commands.

> sp_help will give you table definitions and you can generate table
> creation scripts in Enterprise Manager.
Thanks, that helped me to find my way through. I found an online reference
at
<http://manuals.sybase.com/onlinebook...sg1250e/sqlug/>.
Stefan
|||Hi,
Books online is the best option to learn all commands and usage.
http://www.microsoft.com/sql/techinf...2000/books.asp
* the complete desciption of BACKUP and RESTORE
See backup and Restore in books online
* a way to show the table definitions.
sp_help <table_name>
* OSQL
See OSQL in books online
Thanks
Hari
MCDBA
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsbdo75yjs9ddfw@.news.individual.de...
> Hi!
> I have been looking for a complete command reference to osql. A
document
> that describes all possible SQL commands that you acn use. Unfortunately,
> I seem too stupid :/
> I haven't found:
> * the complete desciption of BACKUP and RESTORE
> * a way to show the table definitions.
> TIA,
> Stefan
|||The online reference you found is for Sybase and is not valid for Microsoft
SQL Server. Follow Hari's link.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opsbdq5udms9ddfw@.pluto...
On Mon, 19 Jul 2004 11:09:11 +0100, Narayana Vyas Kondreddi
<answer_me@.hotmail.com> wrote:

> At command prompt, run the following command and it will show you all
> switches available for OSQL.
> OSQL /?
These, I know, thanks

Stefan
|||osql is primarily a utility for running Transact-SQL statements on an
instance of SQL Server, including MSDE 2000. The primary reference for most
of the statements you can run using osql is the Transact-SQL Reference in
the SQL Server 2000 Books Online.
You can download the latest version of the SQL Server 2000 Books Online
from:
http://www.microsoft.com/sql/techinf...2000/books.asp
The latest version of the SQL Server 2000 Books Online is also published in
the MSDN Library at:
http://msdn.microsoft.com/library/?u...asp?frame=true
These are topics about running osql that are in the copy of the Books Online
in MSDN:
http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
This is the start of the Transact-SQL Reference in the MSDN copy of the
Books Online:
http://msdn.microsoft.com/library/de...asp?frame=true
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

OSQL command for checking to see if a DB exists

What is the OSQL command for checking to see if a database exists (for MSDE
2000)
I am getting an error after my install that says database does not exist or
access denied. Not sure about the best way to troubleshoot this.
Thanks.
STom
Select DB_ID('Northwind')
6
(1 row(s) affected)
Select DB_ID('SomeDBThatDoesntexists')
NULL
(1 row(s) affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> What is the OSQL command for checking to see if a database exists (for
> MSDE 2000)
> I am getting an error after my install that says database does not exist
> or access denied. Not sure about the best way to troubleshoot this.
> Thanks.
> STom
>
|||Do you mean from the command line:
osql Select DB_ID('Northwind') ...I'm pretty sure you meant to preface it
with more commands.
The first thing I would like to do is make sure that the password and
username that I think is supposed to be used is being used. If this is
correct, then I would like to check to see if the database exists.
What is the command line for this?
Thanks!
STom
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uv91zDDbFHA.348@.TK2MSFTNGP14.phx.gbl...
> Select DB_ID('Northwind')
> --
> 6
> (1 row(s) affected)
>
> Select DB_ID('SomeDBThatDoesntexists')
> --
> NULL
> (1 row(s) affected)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
> news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>
|||hi STomn
STom wrote:
> I am getting an error after my install that says database does not
> exist or access denied. Not sure about the best way to troubleshoot
> this.
the error you probably get is "SQL Server does not exists or access denied",
which is a generic MDAC error..
please have a look at
http://support.microsoft.com/default...06&Product=sql
if helps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
STom wrote:
> Do you mean from the command line:
> osql Select DB_ID('Northwind') ...I'm pretty sure you meant to
> preface it with more commands.
actually Jens intended for sure you were already logged in with oSql.exe..

> The first thing I would like to do is make sure that the password and
> username that I think is supposed to be used is being used. If this is
> correct, then I would like to check to see if the database exists.
> What is the command line for this?
there's non command line for that...
you can execute a call to oSql.exe providing a -Q"SELECT ..." and inspect
results, like
c:\..\>osql -Usa -Ppwd -Q"SELECT DB_ID('Northwind')" -oC:\err.txt
wich will output, in the C:\err.txt file, something like
--<--
6
(1 row affected)
if the correct userid and pwd are provided, else something like
--<--
Login failed for user 'sa'.
will be reported if a wrong pwd is provided...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

OSQL command - Execute all files in a folder;

Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran ArulmozhiCheck out a little free tool called Nant.

http://nant.sourceforge.net/

We use it for all of our product deployments. My Nant scripts go through all of the sql files in a directory and executes them using osql. The one little issue I have with it has to do with nested views because Nant will execute all of the files alphabetically, but there ways around this.|||You could cheat and use the FORFILES (http://technet2.microsoft.com/WindowsServer/en/library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx?mfr=true) command.

-PatP|||you could use xp_cmdshell and file system objects with sp_OACreate.|||Thanks for your responses !!

Saturday, February 25, 2012

osql - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the file.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006 2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Since the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

osql - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the fi
le.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmSince the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/20
06
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/20
06
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

osql - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the file.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006 2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmSince the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

Wednesday, March 21, 2012

Out Of Memory

Out of Memory

Out Of Memory

Tuesday, March 20, 2012

Other ways to execute Integration package?

Friday, March 9, 2012

OSQL restore Database

OSQL question

OSQL query command

OSQL query command

OSQL query command

Wednesday, March 7, 2012

OSQL -L

OSQL -L

OSQL -L

osql in a cmd file, with SQL statements coming from the same cmd f

OSQL- How to know the length of each column in the output file?

osql command reference?

OSQL command for checking to see if a DB exists

OSQL command - Execute all files in a folder;

Saturday, February 25, 2012

osql - creating a DB - permission problem.

osql - creating a DB - permission problem.

osql - creating a DB - permission problem.

Orphaned Sessions and locks

Blog Archive

About Me