Executing SP for all results

I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.comLooping through resultsets with cursors is usually a bad idea and there
is normally a better set-based solution. Can you post your proc, your
select query and some schema so we can advise on a better approach
(maybe some test data & expected results too)?
*mike hodgson*
http://sqlnerd.blogspot.com
Doc Parker wrote:

>I would like to have my stored procedure executed for each item returned by
>a Select query
>
>Ex.
>Declare @.file_id varchar(5)
>Select @.file_id = file_id from GEN where this_value <> ''
>execute sp_mystored_procedure @.file_id
>go
>Currently it only works for one record
>How do I get it to do it for all returned records?
>The top select statement may return 1000 records, but only one pass is made
>through the stored procedure
>Hints Please
>Thanks
>Darrell
>darrellp@.btmcpa.com
>
>
>|||The procedure in question actually runs as part of an exsiting TRIGGER. I am
installing this TRIGGER into a table of a database and want to call it in o
rder to extract data all ready in the table. Otherwise it works great for re
cords currently being added. If I were to use a CURSOR to run this SP it wou
ld be a one time deal. Nothing permanent.
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uWo%23nF6JGHA.340
8@.TK2MSFTNGP12.phx.gbl...
Looping through resultsets with cursors is usually a bad idea and there is n
ormally a better set-based solution. Can you post your proc, your select qu
ery and some schema so we can advise on a better approach (maybe some test d
ata & expected results too)?
mike hodgson
http://sqlnerd.blogspot.com
Doc Parker wrote:
I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.com

executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

Executing Multi-lines SQL Scripts From Command-line

Hello,

I'm trying to execute a file containing few SQL statements.

CREATE VIEW test1 AS SELECT * FROM table1;
CREATE VIEW test2 AS SELECT * FROM table2;

The standard SQL way is to end a statement with semi-colon.
But doing that,it doesn't work in SQL Server.
After changing ";" to "GO", it works fine.

Is there anyway we can stick to ";" to indicate the end of statement.
I don't want to create scripts which works only in SQL Server.

Please comment.

Thanks in advance.Simon Hayes (sql@.hayes.ch) writes:
> Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by the
> SQL Server client tools as a batch delimiter. The semi-colon is the Oracle
> equivalent, as far as I know.

And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
He has semi-colons all over the place.

Semicolon as a statement terminator is indeed standard SQL, and it is a
pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
Microsoft added semicolons as a optional terminator in SQL7, but it would
constitute a major blow to existing code to make it mandatory. (But if
MS would supply a tool that added all missing semicolons to existing
code, it could be worth the effort.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||James (ehchn1@.hotmail.com) writes:
> I'm trying to execute a file containing few SQL statements.
> CREATE VIEW test1 AS SELECT * FROM table1;
> CREATE VIEW test2 AS SELECT * FROM table2;
> The standard SQL way is to end a statement with semi-colon.
> But doing that,it doesn't work in SQL Server.
> After changing ";" to "GO", it works fine.
> Is there anyway we can stick to ";" to indicate the end of statement.
> I don't want to create scripts which works only in SQL Server.

This is not legal T-SQL:

CREATE VIEW test1 AS SELECT * FROM table1;
go
CREATE VIEW test2 AS SELECT * FROM table2;
go

For some explicable reason ; is not permitted here. (Probably because
CREATE VIEW must be alone in a batch.

However, if you change the batch separator to with the -c option as
Simon Hayes suggested, this works:

CREATE VIEW test1 AS SELECT * FROM table1
;
CREATE VIEW test2 AS SELECT * FROM table2
;

And it still legal in ANSI-compliant engines.

Since the batch-separator must be alone on a line, this solution
can work decently. Of course if a developer for some reason puts a
lone semicolon in the middle of a stored procedure, he effectively
splits that procedure in two.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93D3E36904A63Yazorman@.127.0.0.1...
> Simon Hayes (sql@.hayes.ch) writes:
> > Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by
the
> > SQL Server client tools as a batch delimiter. The semi-colon is the
Oracle
> > equivalent, as far as I know.
> And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
> He has semi-colons all over the place.
> Semicolon as a statement terminator is indeed standard SQL, and it is a
> pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
> Microsoft added semicolons as a optional terminator in SQL7, but it would
> constitute a major blow to existing code to make it mandatory. (But if
> MS would supply a tool that added all missing semicolons to existing
> code, it could be worth the effort.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Interesting, I didn't know there was any standard at all in that area. It's
a good point about Celko's posts, though, given his insistence on
platform-independent code - I guess I should have worked that one out...

Simon

Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing Dynamic SQL with out Select Permission

I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_executesq
l
@.sql I am executing the dynamic sql, few of my procedures are getting input
parameter for table name and/or column names also. Now the database user is
modified with privileges, he has assigned only execute Permission. How to
solve this problem.You can't. If you use dynamic SQL you need permissions on the underlying
tables. Passing in table names and column names as parameters to a stored
procedure is not a good idea anyway, and the problem you have run into is
only one of the issues (see http://www.sommarskog.se/dynamic_sql.html). If
you can explain what you are actually trying to do, someone here can come up
with a better solution.
Jacco Schalkwijk
SQL Server MVP
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
>I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
>sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
> input
> parameter for table name and/or column names also. Now the database user
> is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||Unfortunatly you can't, with dynamic SQL you must have Select Permission on
the table.
If you tell us what you are tryng to do we could possible sugest an
alternative.
Peter
Do not arouse the sleeping dragon, for you are crunchy and taste good with
ketchup.
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also.
Care to explain just WHY you are doing that? The usual reasons are poor
database design and/or poor coding practices. The solution is almost always
not to do it. Dynamic SQL comes with a lot of incovenient baggage: security
vulnerabilities; performance implications; maintenance and reliability
issues; cost to develop and support.
David Portas
SQL Server MVP
--
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||I don't know if this applies in your case, but it helped avoid dynamic SQL
on a project of mine. If you need to query across multiple partitioned
tables (ex: SALES_2004, SALES_2003, etc), then consider using a partitioned
view (basically a view of unionized tables). When a new table is added, then
you can re-create the view that includes the new table reference.
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
input
> parameter for table name and/or column names also. Now the database user
is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

|||etetetet

executing arbitrary statements in mdx

for example if I want to get a feel for how a particular function operates, can I do something like this ...

select {cdate("1/1/2005")} on columns

from [itdev1 hk]

what would be the correct syntax?

Hello! My recommendation is to start with simple MDX expression like calculated members.

Here is a good site wih a lot of MDX tutorials for beginners and advanced users:

http://www.databasejournal.com/features/article.php/3593466

Look for William.E.Pearson

HTH

Thomas Ivarsson

|||It usually looks something like this:

Code Snippet

WITH MEMBER [Measures].[TestMeasure] AS 'cdate("1/1/2005")'
SELECT {[Measures].[TestMeasure]} ON COLUMNS

I can't remember if you need a FROM when you aren't referring to any real dimensions/measures. If it complains, just point it at a cube.
|||

note: i did need to put in a cube name, and after that it worked great.

nice one!

Executing a variable inside a stored procedure

Hello :) I need to do something like this:

CREATE PROCEDURE SelectCostumers @.name varchar(100)

Declare @.SQL = "SELECT Id, Name FROM Costumers"

AS

IF (@.name IS NULL)

@.SQL

ELSE

@.SQL += "WHERE Name LIKE @.name"

See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.

Thank you

Hi,

Try as below.

CREATE PROCEDURE dbo.SelectCostumers
@.name varchar(100)

AS
BEGIN


DECLARE @.SQL NVARCHAR(500)
SET @.SQL = N'SELECT CustomerID, ContactName FROM Customers'


IF (@.name IS NULL)
BEGIN
EXEC sp_executesql @.SQL
END
ELSE
BEGIN
SET @.SQL = @.SQL + ' WHERE ContactName=''' + @.name + ''''
EXEC sp_executesql @.SQL
END

END

HTH

|||Thank You!!

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

ExecuteSQL task has changed

Since the last IDW.

The column "ParameterName" has been added to the ParameterMapping tab of the ExecuteSQL task.

I enter a statement of

SELECT * FROM TABLE WHERE COLUMN = ?

I map ? to a input variable. The default name of the parameter supplied is "NewParameterName"

My task now fails with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * FROM TheBigOne WHERE HostName = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
What parameter? The one the task supplies?

Allan

Allan
I was able to get passed this by putting ? in the paramter name column.

Norman P.|||I cannot get it to work even using that and it would kinda make no sense either because what if I had

SELECT X FROM TABLE WHERE DATE BETWEEN ? AND ?

What would be the Parameter Names?

Allan|||Ok so i just got it to work

say I have a statement of

SELECT AddressID
FROM Person.Address
WHERE City = ?

The parmeter name should be @.City

I could not find this anywhere in the docs.

Allan|||Allan,

Are you using a managed provider?

ash|||Allan
I tried it your way which works but also used ? in both paramter names and it also worked provided the paramters where in the correct order.

Norman P.

ExecuteSQL Fails from Variable

I am executing the following statement to setup a database:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.

I get this error:

SSIS package "BuildTemplates.dtsx" starting.

Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.

Thanks,

executeScalar - count(*)

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!ow... i forgot the .Text of the textboxes...
it's now solved!|||please don't write code like this unless you want criminals to steal your data.

see http://www.dbforums.com/showpost.php?p=6263508&postcount=7|||ow... tnx 4 ur concern! actually i don't know securities like that because im just doing a project in my subject and don't care about those things. but i really appreciate ur response and i will study those links. tnx again!

Execute Stored Procedure Using Select Statement As Parameters

Hi,
I'm trying to run a SP with a select statement. Example below:
Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
Will this work?
--
Thanks,
Jig PatelYou can store the result of the query to a temporary variable and execute th
e
sp using the result
declare @.user VARCHAR(50)
select @.user = Username
FROM TempUser
EXEC uspR_InsertUsername @.user
it will work when
select Username
FROM TempUser
returns only one user, otherwise you will have to declare @.user as a table
variable and loop through it to execute the procedure for each row
Let me know if it helps
"Jig" wrote:

> Hi,
> I'm trying to run a SP with a select statement. Example below:
> Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
> SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
> Will this work?
> --
> Thanks,
> Jig Patel|||Could you be more specific on what you want to reach.

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

execute statement

hi all
this is basically in SQL
create procedure sp
begin
if iMode = 16
then
exec('Select DeptCd as DepartmentCode from '+ sCheckValue+' Group by
DeptCd');
end
how can we wright the above query in Oracle
Plz help me
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!On Fri, 18 Mar 2005 00:16:11 -0800, Param N wrote:
(snip)
>how can we wright the above query in Oracle
>
>Plz help me
Hi Param8,
You'd better ask in a newsgroup for Oracle. This group is for Microsoft
SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execute SQL Task question

I need to get the curent date from within a Execute SQL Task step,

I tried this

Declare @.Date as datetime
set @.Date = SELECT GETDATE()

..and this

Declare @.Date as datetime

set @.Date = Date

...without any luck.

I need the date as mm/dd/yyyy, anyone know how?

Thanks,
JavawabaSELECT CONVERT(varchar, GETDATE(), 101)

Greg.|||

Hello,

You can write a query like : "select convert(varchar,getdate(),101) as r_currentdt" and assign this result to some variable in the result mapping. Note:R_currentdt is a resultset name which you have to assign to some variable of the type string and can use any where within the scope.

Regards,

Raju