Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

Can u please check this queary?

Dear Friends
Hi!!!
I have written this query having a few join
its showing "syntax error in FROM clause" when i am trying to execute
it
If u can help me i would be very Glad
Here is my code
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
(LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID = Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID = Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID = dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
Thanks in advance
TakeCare
Love
Amitdev.amit
At first glance (utested)
SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
dbo_Test_Components.Test_Name
FROM
(
SELECT * FROM LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID
) AS LV_CBAY_TEST_PROPERTY
LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID =Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID =Test_Comp_Prop_Map.Property_ID
LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID =dbo_Test_Components.Test_Component_ID
WHERE LV_CBAY_TEST_PROPERTY.TEMPLATEID = txt_template_code. value
"dev.amit" <agrawal.solutions@.gmail.com> wrote in message
news:1143113410.483444.240350@.i40g2000cwc.googlegroups.com...
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>|||Are you sure about this namee : dbo_Test_Components ?
must be perhaps : dbo.Test_Components
and where does " txt_template_code.valuedev.amit " come from ?
SELECT TST.TESTID,
TST.PROPERTYID,
TCP.Test_Name
FROM LV_CBAY_TEMPLATE_PROPERTY AS TMP
INNER JOIN LV_CBAY_TEST_PROPERTY AS TST
ON TST.PROPERTYID = TMPLATE_PROPERTY.PROPERTYID
AND TST.TESTID = TMP.TESTID
LEFT OUTER JOIN Test_Comp_Prop_Map AS CPM
ON TST.TESTID = CPM.Test_ID
AND TST.PROPERTYID = CPM.Property_ID
LEFT OUTER JOIN dbo_Test_Components AS TCP
ON CPM.TestComp_ID = TCP.Test_Component_ID
WHERE TMP.TEMPLATEID = txt_template_code.valuedev.amit
A +
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||There is an open ( bracket at this line.
(LEFT JOIN Test_Comp_Prop_Map.
Either the query is incomplete or it needs restructing.
"dev.amit" wrote:
> Dear Friends
> Hi!!!
> I have written this query having a few join
> its showing "syntax error in FROM clause" when i am trying to execute
> it
> If u can help me i would be very Glad
> Here is my code
> SELECT LV_CBAY_TEST_PROPERTY.TESTID, LV_CBAY_TEST_PROPERTY.PROPERTYID,
> dbo_Test_Components.Test_Name
> FROM (LV_CBAY_TEMPLATE_PROPERTY INNER JOIN LV_CBAY_TEST_PROPERTY ON
> LV_CBAY_TEST_PROPERTY.PROPERTYID = LV_CBAY_TEMPLATE_PROPERTY.PROPERTYID
> AND LV_CBAY_TEST_PROPERTY.TESTID = LV_CBAY_TEMPLATE_PROPERTY.TESTID)
> (LEFT JOIN Test_Comp_Prop_Map ON LV_CBAY_TEST_PROPERTY.TESTID => Test_Comp_Prop_Map.Test_ID AND LV_CBAY_TEST_PROPERTY.PROPERTYID => Test_Comp_Prop_Map.Property_ID
> LEFT JOIN dbo_Test_Components ON Test_Comp_Prop_Map.TestComp_ID => dbo_Test_Components.Test_Component_ID
> WHERE LV_CBAY_TEMPLATE_PROPERTY.TEMPLATEID = txt_template_code. value
> Thanks in advance
> TakeCare
> Love
> Amit
>sql

Can stored procs run after handle is closed?

I have written a stored proceedure for MSSQL that needs to run for hours at
a time. I need to execute it from C++ code. The current code does:

nRet = SQLDIRECTEXEC(hstmt, "exec stored_proc", SQL_NTS)

followed shortly after by a

Free_Stmt_Handle(hstmt) //roughly

The stored proc currently dies with the statement handle, not fully
populating the table I need it to.

I need to either know when the proc finishes so I can close the handle after
that, or allow the proc to run independently on the server no matter what
the program is doing (is exited, etc), either of these is fine.

Please Help! Thanks in advance!
JosephI know nothing about C++, but if the proc runs for a very long time, it
might be better to implement it as a scheduled job. The client could
set a flag or insert a row into a 'queue' table, then you have a job
which runs every few minutes or whatever, and if the flag is set, it
then starts the stored proc.

Simon|||That is an interesting approach, ideally I would like to stay as far away
from the database as I can but it sounds like this could be the best way...
my stored procedure is running for the exact same number of instructions and
then dying, whereas if I run it via Query Analyzer it runs to completion.

I finally caved and just copy-pasted from Q.Analyzer into code to confirm
this. I will investigate a little further before taking that plunge.

Thanks
Joseph

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1112950699.762977.19700@.o13g2000cwo.googlegro ups.com...
>I know nothing about C++, but if the proc runs for a very long time, it
> might be better to implement it as a scheduled job. The client could
> set a flag or insert a row into a 'queue' table, then you have a job
> which runs every few minutes or whatever, and if the flag is set, it
> then starts the stored proc.
> Simon

can send email using dbmail

Hi

I can't send email using sp_send_dbmail stored proc

when I execute this stored proc in msdb I face no error but executing it in other databases raises an error saying this stored proc does not exist.

when I executed this query

select * from sys.transmission_queue

I recived no result set.

please tell me what to do.

thanks

pooyan.

yes,

sp_send_dbmail should be run in msdb

Can one exec a stored procedure in a case statement in a stored procedure?

Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***No, although if you can convert the proc into a user-defined function, you
could use the UDF.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Colette Horter" <coletten@.gmail.com> wrote in message
news:e67fw6RlGHA.1340@.TK2MSFTNGP02.phx.gbl...
Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***|||>> Can one execute a stored procedure and get a value back from it in a CASE stat
ement [sic] in a stored procedure? <<
Learn the basics and life is so easy:
There is no CASE statement in SQL. There is a CASE expression. What
does an expression do? It returns a scalar value of a known data type.
Does a stored procedure return a scalar value of a known data type?
NO! But a scalar function call does!

>From you psuedo-code posting, it looks like you also need to learn is
that rows are not records; fields are not columns; tables are not
files. You might also want to learn Standard SQL's AS for alias
assignments, too. That will make your code portable and readable to
the next guy to maintain it.
I will ignore the remark about pulling out your hair :)|||>>> Does a stored procedure return a scalar value of a known data type?
YES !! The data type is INTEGER.
I mean - really, please read the manual for MICROSOFT SQL SERVER and be
aware of the group you are posting to which is for MICROSOFT SQL SERVER.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150904673.628439.120580@.g10g2000cwb.googlegroups.com...
> Learn the basics and life is so easy:
> There is no CASE statement in SQL. There is a CASE expression. What
> does an expression do? It returns a scalar value of a known data type.
> Does a stored procedure return a scalar value of a known data type?
> NO! But a scalar function call does!
>
> that rows are not records; fields are not columns; tables are not
> files. You might also want to learn Standard SQL's AS for alias
> assignments, too. That will make your code portable and readable to
> the next guy to maintain it.
> I will ignore the remark about pulling out your hair :)
>|||>> The data type is INTEGER [return from T-SQL stored procedure] <<
No, that is a completion flag and it is no more a result data type than
a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
-- because of internal representation, but they are VERY different
domains. Think abstract, not current dialect implementation.
You might not remember the C programming language, but all statements
returned such flags (it was part of the DEC PDP-11 hardware that the
language was based on). If you want a scalar result value, you use a
function.|||Thank you so much! I put it in a function and now my sproc actually
saves!!! Just need to finish it up now. c",)
*** Sent via Developersdex http://www.examnotes.net ***|||> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
WRONG! WILL YOU PLEASE READ THE MANUAL AND ACTUALLY USE THE PRODUCT!!!!!
You can use RETURN to pass back any INTEGER, e.g. RETURN( 1 ) etc...
AGAIN: CHECK THE MANUAL UNDER STORED PROCEDURE!!!!

> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
Yes, I remember - I also remember PL/1, C++ and PASCAL.....
But, have you actually used these languages outside of a book / class room?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150925049.724707.252820@.r2g2000cwb.googlegroups.com...
> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
>|||>> - I also remember PL/1, C++ and PASCAL.. But, have you actually used thes
e languages outside of a book / class room? <<
PL/I at Coca Cola World HQ in Atlanta, Pascal at Southern Califrnia
Edison and I nver learned C++.