Running a Stored procedure or a query

Hi Guys
I woud like to run either a stored procedure before runing the actual
report. The stored procedure will create and update a table and on the second
run the report query will run and produce the report.
Is it possible to achieve this?
Regards
Tofigh> I woud like to run either a stored procedure before runing the actual
> report. The stored procedure will create and update a table and on the
second
> run the report query will run and produce the report.
> Is it possible to achieve this?
I guess your report is scheduled. Just edit the scheduled job and insert a
step that executes the stored procedures before the step that causes the
report to run.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks for the reply, the report is not scheduled. the users will run the
report on demand.
Regards
Tofigh
"Dejan Sarka" wrote:
> > I woud like to run either a stored procedure before runing the actual
> > report. The stored procedure will create and update a table and on the
> second
> > run the report query will run and produce the report.
> >
> > Is it possible to achieve this?
> I guess your report is scheduled. Just edit the scheduled job and insert a
> step that executes the stored procedures before the step that causes the
> report to run.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Then maybe you can use two SPs - one outer that is called from the report
and returns the data, and inside it you first make a call to the second
procedure that prepares the tables.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"TA" <TA@.discussions.microsoft.com> wrote in message
news:9C1789C5-6FF7-469B-888A-6B0E7547F2BA@.microsoft.com...
> Thanks for the reply, the report is not scheduled. the users will run the
> report on demand.
> Regards
> Tofigh
> "Dejan Sarka" wrote:
> > > I woud like to run either a stored procedure before runing the actual
> > > report. The stored procedure will create and update a table and on the
> > second
> > > run the report query will run and produce the report.
> > >
> > > Is it possible to achieve this?
> >
> > I guess your report is scheduled. Just edit the scheduled job and insert
a
> > step that executes the stored procedures before the step that causes the
> > report to run.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > www.SolidQualityLearning.com
> >
> >
> >

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straight
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.
Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straigh
t
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a Sql query from Crontab.

Hi,

I want to run a sql query from a crontab.
I hav written a script which enters SQLPLUS , executes the query and directs the output to a flat file.

My problem is when Iam palcing the script in crontab it is throwing an error .But when executed in the prompt ,it is running.

pls give ur suggestions on that.If you don't spell the name of the script correctly, then SQL Plus will throw an error.

As you probably guessed, I'm trying to be funny. ;) Without you providing information about the error being thrown, we have to guess at it. There's a pretty wide area for guessing!

Please be more specific. Cut the error message from the log (SQL Plus output) file, and post it here. If possible (without security or related problems), cut the whole command you are using, and post that here too.

With all of the people that contribute here, I'm sure that someone can help you if you can give them the information that they need to do that!

-PatPsql

running a query within a DTs package

IF a run a query from within a DTS package will I have to use complete instructions, i.e. will I have to include in the query the name of the table that i am importing from or will the DTS package assume this?
thanks!Are you referring to performing a sql or transact/sql statement from within an execute sql task?

If so, despite you establishing a connection with one or more servers/dbs (transform data task or not) you should have to specify the table you are referencing... when you are in the execute sql task properties (ie when you double click) you need to specify which server connection you are using in order to reference that specific table. There is a drop down box in the task's properties window called existing connection... pick the connection which references the server/db that contains the table which you will be performing a query on... You don't want to perform an update statement on tblcustomer (located in db1) and not specify the correct connection in which to find it!

I hope that is what you were asking for if not, give a little shout back with a little more detail and we'll see what we can do.sql

Running a proc. on a certain date help?

DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 3

SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber

I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current dateUse SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) to schedule it ?

-PatP|||DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = day(GetDate())
If @.returnDay = 3
begin
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
endsql

Running a DLL in the Query Analyzer

Hi,

I have a DLL that I want to run from the Query Analyzer. I tried the
following:

USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'

and I get the error:

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.

What can I do?

Thanks!One other thing...

The DLL can be called from Visual Basic. That was installed some time
ago and works great.

On Nov 23, 9:15 am, Jim <jsh...@.datamann.comwrote:

Quote:

Originally Posted by

Hi,
>
I have a DLL that I want to run from the Query Analyzer. I tried the
following:
>
USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'
>
and I get the error:
>
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).
>
The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.
>
I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.
>
What can I do?
>
Thanks!

|||The DLL was written in Cobol. The "Other-Server" in the Path above is

Quote:

Originally Posted by

the server that Cobol (and the DLL) is located on.

Not just any DLL can run as a SQL Server extended stored procedure.
Extended stored procedures are usually written in C/C++, although I've heard
that some have been written using Delphi. See Creating Extended Stored
Procedures in the Books Online for details.

Separately, COM DLLs can be invoked using sp_OA* procs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jim" <jshain@.datamann.comwrote in message
news:8d9db5fa-a277-400f-9761-7886f9607c55@.g21g2000hsh.googlegroups.com...

Quote:

Originally Posted by

>
Hi,
>
I have a DLL that I want to run from the Query Analyzer. I tried the
following:
>
USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'
>
and I get the error:
>
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).
>
The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.
>
I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.
>
What can I do?
>
Thanks!
>
>
>
>

|||Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?

The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

Thanks alot!

Jim

On Nov 24, 9:52 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

>
Not just any DLL can run as a SQL Server extended stored procedure.
Extended stored procedures are usually written in C/C++, although I've heard
that some have been written using Delphi. See Creating Extended Stored
Procedures in the Books Online for details.
>
Separately, COM DLLs can be invoked using sp_OA* procs.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Jim" <jsh...@.datamann.comwrote in message
>
news:8d9db5fa-a277-400f-9761-7886f9607c55@.g21g2000hsh.googlegroups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have a DLL that I want to run from the Query Analyzer. I tried the
following:

>

Quote:

Originally Posted by

USE master;
EXEC sp_addextendedproc BLAH, '\\Other-Server\Path\To\Blah\Blah.dll'

>

Quote:

Originally Posted by

and I get the error:

>

Quote:

Originally Posted by

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\Other-Server\Path\To\Blah\Blah.dll, or one of
the DLLs it references. Reason: 126(The specified module could not be
found.).

>

Quote:

Originally Posted by

The DLL was written in Cobol. The "Other-Server" in the Path above is
the server that Cobol (and the DLL) is located on.

>

Quote:

Originally Posted by

I looked at the dependencies for the DLL and it includes (not
surprisingly) Cobol Dll's. It should find those on the other server.

>

Quote:

Originally Posted by

What can I do?

>

Quote:

Originally Posted by

Thanks!- Hide quoted text -

>
- Show quoted text -

|||(jimshain@.gmail.com) writes:

Quote:

Originally Posted by

Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?
>
The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

Yes, you could write one or more extended stored procedures to access
the DLL. Or implement COM methods.

On SQL 2005, you could to a COM-interop and call it from CLR.

Whatever, it's a bit of work, and and if you have errors in your
code that causes access violation, you could crash SQL Server.

To get information about writing extended stored procedures, please
see in Books Online for details. If you have never done it before,
expect at least 50 hours of development.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the pointers!

Jim

On Nov 26, 5:35 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(jimsh...@.gmail.com) writes:

Quote:

Originally Posted by

Since I can call it from a VB program, do you know if I can create a
new DLL using the VB program and use that to access the needed DLL?
If not... If I can call my needed DLL from a C/C++ program, can I
create a new DLL that I could call to get at my needed DLL?

>

Quote:

Originally Posted by

The DLL that I need to call, was bought from a different company, so I
don't have access to the code.

>
Yes, you could write one or more extended stored procedures to access
the DLL. Or implement COM methods.
>
On SQL 2005, you could to a COM-interop and call it from CLR.
>
Whatever, it's a bit of work, and and if you have errors in your
code that causes access violation, you could crash SQL Server.
>
To get information about writing extended stored procedures, please
see in Books Online for details. If you have never done it before,
expect at least 50 hours of development.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Running a DB2 Stored Procedure

I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.

How do i call a DB2 stored procedure?

Sorry, I don't have the answer to your question, but, you may be able to help me to create a linked server to DB2/400 from 64 bit sql 2005. I am using IBM client access to create a system DSN and trying to use that System DSN as a data source for the linked server. Problem is "Oledb provider for Odbc" is not listed as a provider in sql2005. How did you solve that problem? Just FYI.. I tried to use the same method as I did in sql2000 environment, which have been working great for last 3 years.

Running a DB2 Stored Procedure

I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.

How do i call a DB2 stored procedure?

Sorry, I don't have the answer to your question, but, you may be able to help me to create a linked server to DB2/400 from 64 bit sql 2005. I am using IBM client access to create a system DSN and trying to use that System DSN as a data source for the linked server. Problem is "Oledb provider for Odbc" is not listed as a provider in sql2005. How did you solve that problem? Just FYI.. I tried to use the same method as I did in sql2000 environment, which have been working great for last 3 years.sql

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT Dev
File->Open, browse and find the file, and when it is open hit F5...
If you're looking for an non-interactive way to run a file, you might check
out the OSQL command-line tool that ships with SQL Server. It is documented
in Books Online.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:7DE905C4-EF0B-45CB-B109-6E63894D38BA@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
|||IT Developer wrote:

> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
Why don't you?
|||Hello,
You will have to use XP_CMDSHELL if you need to execute the .BAT from Query
Analyzer. There is no other alternative.
Thanks
Hari
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:7DE905C4-EF0B-45CB-B109-6E63894D38BA@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT Dev
I answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT DevI answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running a Batch File in Query Analyzer

Hi,
I have to run a batch file in query analyzer. How do I do that.
I don't want to use 'xp_cmdshell'
Please advise
(An example would be great)
Regards
IT DevI answered this question in the microsoft.public.sqlserver.programming.
Please don't post the same question independently to different groups
because that causes duplicate effort among the parties involved. You can
instead post the question once to all relevant groups so that the all
responses are tracked as a single thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"IT Developer" <ITDeveloper@.discussions.microsoft.com> wrote in message
news:5D65C0C7-EABB-4A33-8C24-118C3CAC08F7@.microsoft.com...
> Hi,
> I have to run a batch file in query analyzer. How do I do that.
> I don't want to use 'xp_cmdshell'
>
> Please advise
> (An example would be great)
> Regards
> IT Dev
>

Running 2 queries inside third query

Good morning, all.. your mission today (if you accept it) is to
decipher what I am trying to do and offer assistance to the poster.
(me!) I have searched newsgroups but can find no topics similar to my
needs.
Background:
Trying to create a 'dqy' (MS Query) file for distribution to field
personnel. Initial dqy files (when double-clicked) open Excel, connect
to database, and display the data based on the dqy's SQL.
Database structure is:
tblUser.PKID = tblUser_Role.User_ID_FK
tblUser_Role.Role_ID_FK = tblRole.PHID
tblRole_Course.Role_ID_FK = tblRole.PHID
tblRole_Course.Course_ID_FK = tblCourse.PKID
[Don't blame me... I didn't create this!]
Since other user's data requirements are more involved, I created an
Access database that has tables corresponding to the actual data
source. I 'develop' my queries in Access, copy the SQL into Notepad,
add appropriate connection information, and save file as ####.dqy.
This procedure has worked until now.
I cannot get the SQL for QueryA to work when copied into the dqy file.
QueryA works fine in Access!!! QueryA is composed of
qryUserAndTheirRoles and qryRolesAndTheirCourses.
Here is SQL for QueryA:
SELECT DISTINCTROW qryUserAndTheirRoles.PKID,
qryUserAndTheirRoles.Name, qryUserAndTheirRoles.LastName,
qryUserAndTheirRoles.Agency_ID_FK, qryRolesAndTheirCourses.Name
FROM qryUserAndTheirRoles INNER JOIN qryRolesAndTheirCourses ON
qryUserAndTheirRoles.Role_ID_FK = qryRolesAndTheirCourses.Role_ID_FK
SQL for qryUserAndTheirRoles:
SELECT tblUser.PKID, tblUser.Name, tblUser.LastName,
tblUser.Agency_ID_FK, tblUser_Role.Role_ID_FK
FROM tblUser LEFT JOIN tblUser_Role ON tblUser.PKID =
tblUser_Role.User_ID_FK
WHERE (((tblUser.Agency_ID_FK)=13))
SQL for qryRolesAndTheirCourses:
SELECT tblRole_Course.Role_ID_FK, tblCourse.Name
FROM tblCourse LEFT JOIN tblRole_Course ON tblCourse.PKID =
tblRole_Course.Course_ID_FK
I have been unable to find ANY reference source that describes usage
and syntax. Therefore, I have tried MANY combinations of syntax and
structure. None worked. Is there anyone in this esteemed group who
can modify QueryA -- replacing references to the other queries with
working SQL -- so that the code works when placed in a dqy file'
I bow to your expertise... and await salvation! Thanks
gary b
sfrvn AT earthlink.net<sfrvn@.earthlink.net> wrote in message
news:1133362941.170208.75080@.o13g2000cwo.googlegroups.com...
> Good morning, all.. your mission today (if you accept it) ...
I'm still waiting for this message to auto-destruct.sql

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>> Hi,
>> I have got multiple .sql files(text files) having T-SQL Statements. I
>> want
>> to run them all together from query analyzer...how can i do that...
>> Your help will be appreciated
>

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciated
hI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek
|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek
|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>
sql

Running .Sql Files in a batch

Hi,
I have got multiple .sql files(text files) having T-SQL Statements. I want
to run them all together from query analyzer...how can i do that...
Your help will be appreciatedhI
Let me say you have a file as
Use Pubs
GO
CREATE PROCEDURE spMyProc
AS
SELECT * FROM Titles
--Run this command in QA
EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
"D:\ORD1.sql"'
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
But bear in mind that "D:\" is a drive on the __server__, not on the client.
Regards
Wojtek|||"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
> Hi,
> I have got multiple .sql files(text files) having T-SQL Statements. I want
> to run them all together from query analyzer...how can i do that...
> Your help will be appreciated
Why from QA? Why not write a batch and use isql.exe within?
Regards
Wojtek|||Hi,
I recommend you to run the .SQL files from command prompt using OSQL
redirecting the output to a log file.
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name.sql >
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name1.sql >>
output.log
OSQL -SSERVER_NAME -Uuser -Ppassword -d db_name -i file_name2.sql >>
output.log
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eS%23FL1oaFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hI
> Let me say you have a file as
> Use Pubs
> GO
> CREATE PROCEDURE spMyProc
> AS
> SELECT * FROM Titles
> --Run this command in QA
> EXEC master..xp_cmdshell 'osql.exe -S SERVERNAME -U sa -P pass -i
> "D:\ORD1.sql"'
>
> "kailux4" <kailux4@.discussions.microsoft.com> wrote in message
> news:067CC39F-41E2-4170-B132-927FDB458C9C@.microsoft.com...
>

Run Time parameter query

Why is it that I can create a parameter query in Enterprise Manager but
cannot repeat this in query analyser? How could I create a run time parameter
query (in my parlance a query that allows users to input values)!?
Thanks
SamOn Tue, 18 Jan 2005 03:29:03 -0800, Sam wrote:
>Why is it that I can create a parameter query in Enterprise Manager but
>cannot repeat this in query analyser? How could I create a run time parameter
>query (in my parlance a query that allows users to input values)!?
>Thanks
>Sam
Hi Sam,
This can't be done. Interaction with the end user (like asking for a
parameter) must be handled by the client application.
EM apparently has some support for asking parameter values. QA doesn't.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SQL Server is a back-end server. It cannot ask a user for any parameters since SQL Server runs only
on the server. Possibly EM has some facility to ask for a parameter value, but that would be a
feature in EM. In SQL Server, you typically use stored procedures, where a stored procedure can take
a number of parameters, where the parameters are used inside the query or queries inside the
procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:3755656C-5C1E-4AB5-9018-68B80F3FA855@.microsoft.com...
> Why is it that I can create a parameter query in Enterprise Manager but
> cannot repeat this in query analyser? How could I create a run time parameter
> query (in my parlance a query that allows users to input values)!?
> Thanks
> Sam
>sql

Run Time parameter query

Why is it that I can create a parameter query in Enterprise Manager but
cannot repeat this in query analyser? How could I create a run time parameter
query (in my parlance a query that allows users to input values)!?
Thanks
Sam
On Tue, 18 Jan 2005 03:29:03 -0800, Sam wrote:

>Why is it that I can create a parameter query in Enterprise Manager but
>cannot repeat this in query analyser? How could I create a run time parameter
>query (in my parlance a query that allows users to input values)!?
>Thanks
>Sam
Hi Sam,
This can't be done. Interaction with the end user (like asking for a
parameter) must be handled by the client application.
EM apparently has some support for asking parameter values. QA doesn't.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||SQL Server is a back-end server. It cannot ask a user for any parameters since SQL Server runs only
on the server. Possibly EM has some facility to ask for a parameter value, but that would be a
feature in EM. In SQL Server, you typically use stored procedures, where a stored procedure can take
a number of parameters, where the parameters are used inside the query or queries inside the
procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:3755656C-5C1E-4AB5-9018-68B80F3FA855@.microsoft.com...
> Why is it that I can create a parameter query in Enterprise Manager but
> cannot repeat this in query analyser? How could I create a run time parameter
> query (in my parlance a query that allows users to input values)!?
> Thanks
> Sam
>

Run Time parameter query

Why is it that I can create a parameter query in Enterprise Manager but
cannot repeat this in query analyser? How could I create a run time paramete
r
query (in my parlance a query that allows users to input values)!?
Thanks
SamOn Tue, 18 Jan 2005 03:29:03 -0800, Sam wrote:

>Why is it that I can create a parameter query in Enterprise Manager but
>cannot repeat this in query analyser? How could I create a run time paramet
er
>query (in my parlance a query that allows users to input values)!?
>Thanks
>Sam
Hi Sam,
This can't be done. Interaction with the end user (like asking for a
parameter) must be handled by the client application.
EM apparently has some support for asking parameter values. QA doesn't.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SQL Server is a back-end server. It cannot ask a user for any parameters sin
ce SQL Server runs only
on the server. Possibly EM has some facility to ask for a parameter value, b
ut that would be a
feature in EM. In SQL Server, you typically use stored procedures, where a s
tored procedure can take
a number of parameters, where the parameters are used inside the query or qu
eries inside the
procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:3755656C-5C1E-4AB5-9018-68B80F3FA855@.microsoft.com...
> Why is it that I can create a parameter query in Enterprise Manager but
> cannot repeat this in query analyser? How could I create a run time parame
ter
> query (in my parlance a query that allows users to input values)!?
> Thanks
> Sam
>