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 package with dtexec.exe

Hi,
when I run the following from the command prompt I am getting an error saying that can not set the variable Status as int.

dtexec /File "C:\work\SSIS Packages\Dataflow\ControlRoom.dtsx" /SET

\Package.Variables[ChannelCode].Value;"test" /SET

\Package.Variables[Status].Value;1

both variables as you can see are in the top level.
the problem is that ChannelCode is string but Status is Integer.
if I take out the /SET \Package.Variables[Status].Value;1 part from the command line it will work fine. I think th eproblem is down to diffenrt types of variable as there are two types of String and Interger.

any ideas on what the problem migth be.
p.s. I am runnning SQL server 2005 Dev edition (without any SP)
CheersAre you sure [Status] is an integer in your package?

What happens when you run it with:
/SET \Package.Variables[Status].Value;"1"|||yes it is Integer and don't want to change it to string as I have ament that in few places in my package|||Have you tried DTexecUI to set those values and see how the command line gets generated?|||this is what get's generated

/FILE "C:\work\SSIS Packages\Dataflow\ControlRoom.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[ChannelCode].Value";Test /SET "\Package.Variables[Status].Value";1
but I'm getting the same problem
back to square one|||Can you please post the exact error?|||I'm getting

Started: 4:56:31 PM
Error: 2007-03-26 16:56:33.37
Code: 0xC0017006
Source: ControlRoom
Description: The expression "@.Status == 1" must evaluate to True or False. Ch
ange the expression to evaluate to a Boolean value.
End Error
Error: 2007-03-26 16:56:33.37
Code: 0xC0014021
Source: ControlRoom

as it does a check against the Status value that I'm loading from the command prompt.
if I specify (hardcode) the variable value inside the package it will work fine, this happens only when I try to pass the value from command prompt
Thanks|||Do you have EvaluateAsExpression set to false when you need to have it set to true somewhere?

I don't think we have all of the pieces of the puzzle here, but it seems that "@.Status == 1" is being passed in as a string, not a conditional check.|||sorry, I think it's my fault as I didn't plain a clear picture.
I am passing the variable Status from command prompt and inside the package it trys to check if the passes value is 1
as the Status value is not getting passed from the command prompt and the default value of @.Status in the package is to 0, the condition fails.

now if I hardcode the default value of @.status in the package to 1 and do not try to pass the value for status from command prompt. it work perfectly!
I hope this makes it clear|||And the scope of the variable is correct? You don't have two variables of the same name in different scopes, do you? This can happen.

For instance, you could have a Status variable of package scope and have a Status variable of data flow scope in the same package.|||I thought that this might be the issue but I'm looking at all the variables across the whole package and there isn't any other one with the same name. @.Status is the only and as you can see it is at the top level so it is visible by all the sub tasks.
cheers|||

Kolf wrote:

I thought that this might be the issue but I'm looking at all the variables across the whole package and there isn't any other one with the same name. @.Status is the only and as you can see it is at the top level so it is visible by all the sub tasks.
cheers

Yep, I can see that, but just because it's visible to subtasks doesn't mean that the subtasks can't have their own variable of the same name, that's why I asked.|||

Kolf wrote:

... and inside the package it trys to check if the passes value is 1 ...

How are you doing this? Where are you doing this? Control flow, data flow, conditional split, derived column, etc...|||in the varaible windows I click on a button called show user variable and that displays all the variables define with in that package and I have just one variable called @.Status which is Int32
Thanks

Executin SQL statement from variable in functions

Hello All

Within my function depends on value forming a SQL statement

As Example

set @.SqlString = 'select ' + @.ColumnName + ' from ' + @.tableName + ' where ' + @.whereCond

How can i execute this Statement. within function

Thanks in advance

RK

> Within my function depends on value forming a SQL statement

Sorry, no can do, you cannot execute dynamic SQL in a function. Maybe you

meant to use a stored procedure, not a function.

|||

I can change function to stored procedure within storedprocedure how can i execute a dynamic SQL

My requirement is to open cursor with dynamic SQL.
table name, columns list and conditions are depends on user parameters

|||

> My requirement is to open cursor with dynamic SQL.

You need a cursor and dynamic SQL. Fantastic. First, please read this:

http://www.smmarskog.se/dynamic_sql.html

Then, if you can give better specs, maybe we can help.

http://www.aspfaq.com/5006

|||looks like the site was down (http://www.smmarskog.se/dynamic_sql.html)

is there a way i can a open cursor with dynamic sql.

Our requirement is we have set of related tables with different data but all integer columns

We are trying to create few statistical functions/procs So user can pass Table name and column name and optionally any conditions, so that my function/procs returns results.

|||

Sorry, typo

http://www.sommarskog.se/dynamic_sql.html

> looks like the site was down

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,

Execute stored proc dynamically (stored a variable)

Hi,
Can someone please show me how to execute a stored proc which has been
assigned to a variable. I have a table of stored procedures and I want to
pick out a certain sp, based on a set of criteria, and execute it
dynamically. I'm thinking of setting up a CURSOR to loop through the selecte
d
sp, assign each one to a variable and then execute it but I don't know how
yet.
I would also like to know how to pass a variable of type TABLE to the above
stored proc. This table type variable contains a list of parameters in a for
m
of "key-value" pairs.
Example
DECLARE @.myTable TABLE(
paramName Varchar(100),
paramValue Varchar(100))
INSERT INTO @.myTable(paramName, paramValue)
VALUES ('param1', '123')
DECLARE @.myStoredProc Varchar(100)
DECLARE myCursor CURSOR FOR
SELECT StoredProcName
FROM tableOfStoredProcs
WHERE something = somethingelse
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @.myStoredProc
...
EXEC @.myStoredProc(@.myTable) -- this is what i want to do but syntactically
incorrect.
I'm using SQL Server 2000.
Any suggestion is greatly appreciated.
CalvinCalvin
CREATE PROC myProc
@.parameter1 VARCHAR(...),
@.parameter2 INT
AS
CREATE TABLE #t
(
spnames SYSNAME PRIMARY KEY
)
INSERT INTO #t VALUES ('sp1')
INSERT INTO #t VALUES ('sp2')
INSERT INTO #t VALUES ('sp3')
SELECT 'EXEC '+spnames+' '''+@.parameter1 +''''+','+cast(@.parameter2 as
varchar(10)) FROM #t
"Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@.microsoft.com...
> Hi,
> Can someone please show me how to execute a stored proc which has been
> assigned to a variable. I have a table of stored procedures and I want to
> pick out a certain sp, based on a set of criteria, and execute it
> dynamically. I'm thinking of setting up a CURSOR to loop through the
> selected
> sp, assign each one to a variable and then execute it but I don't know how
> yet.
> I would also like to know how to pass a variable of type TABLE to the
> above
> stored proc. This table type variable contains a list of parameters in a
> form
> of "key-value" pairs.
> Example
> DECLARE @.myTable TABLE(
> paramName Varchar(100),
> paramValue Varchar(100))
> INSERT INTO @.myTable(paramName, paramValue)
> VALUES ('param1', '123')
> DECLARE @.myStoredProc Varchar(100)
> DECLARE myCursor CURSOR FOR
> SELECT StoredProcName
> FROM tableOfStoredProcs
> WHERE something = somethingelse
> OPEN myCursor
> FETCH NEXT FROM myCursor
> INTO @.myStoredProc
> ...
> EXEC @.myStoredProc(@.myTable) -- this is what i want to do but
> syntactically
> incorrect.
> I'm using SQL Server 2000.
> Any suggestion is greatly appreciated.
> Calvin|||Thanks so much for your quick response. I also like to pass the parameters t
o
the stored proc in a form of TABLE type variable, as I demo earlier. This is
because it's a lot more flexible this way. Do you know of a way to do this?
Thanks again.
Calvin
"Uri Dimant" wrote:

> Calvin
>
> --
> CREATE PROC myProc
> @.parameter1 VARCHAR(...),
> @.parameter2 INT
> AS
> CREATE TABLE #t
> (
> spnames SYSNAME PRIMARY KEY
> )
> INSERT INTO #t VALUES ('sp1')
> INSERT INTO #t VALUES ('sp2')
> INSERT INTO #t VALUES ('sp3')
> SELECT 'EXEC '+spnames+' '''+@.parameter1 +''''+','+cast(@.parameter2 as
> varchar(10)) FROM #t
>
>
>
> "Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
> news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@.microsoft.com...
>
>|||Hi, Calvin
You cannot pass a table variable as a parameter. You should use a
temporary table or a permanent (normal) table instead. If you expect
that this procedure may be called simultaneously by more users, you can
use @.@.SPID to separate parameters of different processes.
For example:
CREATE TABLE Parameters (
SPID smallint,
ParamName varchar(100),
ParamValue sql_variant,
PRIMARY KEY (SPID,ParamName)
)
GO
CREATE PROCEDURE sp1 AS
SELECT ParamValue FROM Parameters
WHERE SPID=@.@.SPID AND ParamName='param1'
GO
CREATE TABLE tableOfStoredProcs (
StoredProcName sysname PRIMARY KEY
)
INSERT INTO tableOfStoredProcs VALUES ('sp1')
GO
INSERT INTO Parameters (SPID, ParamName, ParamValue)
VALUES (@.@.SPID, 'param1', 123)
DECLARE @.myStoredProc Varchar(100)
DECLARE myCursor CURSOR LOCAL READ_ONLY FOR
SELECT StoredProcName FROM tableOfStoredProcs
--WHERE ...
OPEN myCursor
WHILE 1=1 BEGIN
FETCH NEXT FROM myCursor INTO @.myStoredProc
IF @.@.FETCH_STATUS<>0 BREAK
EXEC @.myStoredProc
END
CLOSE myCursor
DEALLOCATE myCursor
DELETE Parameters WHERE SPID=@.@.SPID
GO
This usage of EXEC, without parentheses (i.e. "EXEC @.ProcedureName"),
is less vulnerable to SQL Injection attacks than using "EXEC
(@.SQLString)".
Razvan|||Calvin
Read those articles
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
"Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
news:F367EEC4-4B38-4F21-836D-2ADBF2910C9B@.microsoft.com...
> Thanks so much for your quick response. I also like to pass the parameters
> to
> the stored proc in a form of TABLE type variable, as I demo earlier. This
> is
> because it's a lot more flexible this way. Do you know of a way to do
> this?
> Thanks again.
> Calvin
> "Uri Dimant" wrote:
>|||Thank you Razvan for your quick response. That's pretty much what i was
after. My original idea was to pass in a "data structure" as a parameter to
the stored procs and then each stored proc picks out what it needs for
processing.
Anyway, it's a good start.
Thanks.
Calvin
"Razvan Socol" wrote:

> Hi, Calvin
> You cannot pass a table variable as a parameter. You should use a
> temporary table or a permanent (normal) table instead. If you expect
> that this procedure may be called simultaneously by more users, you can
> use @.@.SPID to separate parameters of different processes.
> For example:
> CREATE TABLE Parameters (
> SPID smallint,
> ParamName varchar(100),
> ParamValue sql_variant,
> PRIMARY KEY (SPID,ParamName)
> )
> GO
> CREATE PROCEDURE sp1 AS
> SELECT ParamValue FROM Parameters
> WHERE SPID=@.@.SPID AND ParamName='param1'
> GO
> CREATE TABLE tableOfStoredProcs (
> StoredProcName sysname PRIMARY KEY
> )
> INSERT INTO tableOfStoredProcs VALUES ('sp1')
> GO
> INSERT INTO Parameters (SPID, ParamName, ParamValue)
> VALUES (@.@.SPID, 'param1', 123)
> DECLARE @.myStoredProc Varchar(100)
> DECLARE myCursor CURSOR LOCAL READ_ONLY FOR
> SELECT StoredProcName FROM tableOfStoredProcs
> --WHERE ...
> OPEN myCursor
> WHILE 1=1 BEGIN
> FETCH NEXT FROM myCursor INTO @.myStoredProc
> IF @.@.FETCH_STATUS<>0 BREAK
> EXEC @.myStoredProc
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> DELETE Parameters WHERE SPID=@.@.SPID
> GO
> This usage of EXEC, without parentheses (i.e. "EXEC @.ProcedureName"),
> is less vulnerable to SQL Injection attacks than using "EXEC
> (@.SQLString)".
> Razvan
>|||Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Thanks so much for your quick response. I also like to pass the
> parameters to the stored proc in a form of TABLE type variable, as I
> demo earlier. This is because it's a lot more flexible this way. Do you
> know of a way to do this?
Uri's suggestion is far too complex. Just say:
EXEC @.spname @.par1, @.par2, @.par3
Uri suggested some aritcles on my web site, but not the one which appears
to be the most pertinent to your problem,
http://www.sommarskog.se/share_data.html. This article discusses techniques
to share data between stored procedures. Unforunately, table variables
cannot do that task.
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|||Thank you all for your responses. I have certainly learnt a few new things.
As for my quest, I think I'll just have to pass the same number of parameter
s
to each stored proc (even though some aren't used) unless we can come up wit
h
a better solution.
The problem we're facing is that because we have a long list of stored procs
(which will expand over the years) that we want to exec., and exactly which
stored proc will be exec. depends on certain criteria for each year. Some
will be "On" in one year and may be "Off" the next.
Example:
tblStoredProcs
=========
StoredProcID
StoredProcName
Year
Active
Anyway, if anyone has an idea, please let me know. All suggestions are
greatly welcomed.
Cheers,
Calvin
"Erland Sommarskog" wrote:

> Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Uri's suggestion is far too complex. Just say:
> EXEC @.spname @.par1, @.par2, @.par3
> Uri suggested some aritcles on my web site, but not the one which appears
> to be the most pertinent to your problem,
> http://www.sommarskog.se/share_data.html. This article discusses technique
s
> to share data between stored procedures. Unforunately, table variables
> cannot do that task.
>
> --
> 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
>|||Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Thank you all for your responses. I have certainly learnt a few new
> things. As for my quest, I think I'll just have to pass the same number
> of parameters to each stored proc (even though some aren't used) unless
> we can come up with a better solution.
It sounds like an excellent solution to me! After all, that is as
close to the implemention of a the O-O concept of a virtual class you
can come in T-SQL.
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

Execute Statement..

Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @.query varchar{500)
Set @.query = 'Select * from table'

if exists (exec (@.query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit ChatterjeeHi

EXISTS requires a sub-query as the test, and EXEC does not do that.

If you have to do this dynamically then you may want to look at
sp_executesql to return a count or move everything into @.query.

If you posted more precise detail it may be easier to offer advice.

John

arijitchatterjee123@.yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0310302205.2ca98658@.posting.google.com>...
> Dear Friens,
> I am writing a SP.But storeing a query in a variable.But at the time
> of execution generating error.Exam
> ===================
> Declare @.query varchar{500)
> Set @.query = 'Select * from table'
> if exists (exec (@.query))
> print 'Hi'
> ====================
> But "if exists" line giving error.How do I solve this.Please help me
> out.
> Reagrds
> Arijit Chatterjee|||Create proc sp_test
as
Declare @.query varchar{500)
Declare @.var varchar(10)
set @.var='Test'
Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
exec (@.query)--> Working fine
if exists (exec (@.query))--> Sending error
print 'Hi'
Now tell me how to solve this.
Regards
Arijit Chatterjee|||See inline

"Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> Create proc sp_test
> as
> Declare @.query varchar{500)
This will not compile

> Declare @.var varchar(10)
> set @.var='Test'
> Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
If you were doing this correctly the value in @.var would be enquoted

> exec (@.query)--> Working fine
> if exists (exec (@.query))--> Sending error
> print 'Hi'
> Now tell me how to solve this.

Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.

> Regards
> Arijit Chatterjee

John|||What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),column2
nvarchar(10))) and then build your @.query string so that it inserts
the results of your dynamic select statement into the temp table. You
can then play the whole "exists" game on the results of a query
against your #temp table.

Make sure that you do not build your temp table dynamically. And
remember, you can't insert results from a dynamically built select
statement into a normal variable, but you can insert them into a temp
table.

Good Luck!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bo2oar$89l$1@.hercules.btinternet.com>...
> See inline
> "Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
> news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> > Create proc sp_test
> > as
> > Declare @.query varchar{500)
> This will not compile
> > Declare @.var varchar(10)
> > set @.var='Test'
> > Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
> If you were doing this correctly the value in @.var would be enquoted
> > exec (@.query)--> Working fine
> > if exists (exec (@.query))--> Sending error
> > print 'Hi'
> > Now tell me how to solve this.
> Reading books online would be the first place to look.
> Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
> justify the use of dynamic SQL.
> You should then be able to work out how to return the count using
> sp_executesql to get what is require.
> > Regards
> > Arijit Chatterjee
> John

execute ssis

Hi,

In the SSIS package, I have made the filename dynamic i.e. set as a variable.

How is it possible to do the same thing for the database name in the oledb connection?

I looked at the connectionString property for the OLEDB connection. ConnectionString looks long and the databasename is in this text.

Not sure how to make this databasename inside the connectionstring dynamic?

Thanks

hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +
' \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'
print @.cmd

error is:
Option "\Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1\databasename" is not valid.

please note I just retyped the data source name here.

|||

Get the raw command line working first then port it to SQL version. You are setting two properties, so try giving each it's own /SET. Also try quoting the property ID string bit.

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

I just mocked that up using DTEXecUI, as it can almost act like a command line builder tool for you.

|||

Hi,

It works if I only pass the filename variable but not with both parameters.

As you see I am using the /set option.

Can you see what is wrong with what I sent initially please?

Thanks

|||

You can just replace the entire connection string. Perhaps save the package with a default connection in a variable and then use an expression and REPLACE to change the place holder database for the real one, or just save a connnection string wout the database and add in via and expression. So I guess the trick is to manage the connection string differently, perhaps as sections, such that you can construct what you need with expressions latter. Not ideal, but no doubt driven by the nature of connection types, and connection string properties rather than the known common properties.

If using configurations you can change the InitialCatalog property, but unfortunatey this is not available as the target for a property expression.

|||

I can see you used one set option, but passed two sets against it.

Look at my example again and you will see that I have two /SET options in there, one for each property to be set.

Error -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" "\Package.Variables [User::ConnectionPath].Properties[Value]";"XX XX"

Option "\Package.Variables[User::ConnectionPath].Properties[Value];XX XX" is not valid.

OK -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

|||

I am getting close to what I was after.

Basically I am passing a variable but not sure how to pass two variables. See below. Do you see what is wrong with the below query please?

It works if filename variable is used but not if the second variable is included.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

' /set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

|||

Get the same error.

This is what I have:

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

'/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

|||Can you show us the result of "print @.cmd"?|||

dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\TradeCreds.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\CollateralEx.csv"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=server1\instance1, 2025;Initial Catalog=database1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||What is this?

Data Source=server1\instance1, 2025;|||

I replaced the actual servername\instance

|||

arkiboys wrote:

I replaced the actual servername\instance

What is ", 2025"|||

port no.
This is how I connect to the database.

|||

I think I worked it out:

set @.cmd = 'dtexec /f ' + @.FullPackagePath +

' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

Execute SQL task to update variables

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek

What are the symptoms of this not working? Are there errors or warnings?

Donald

|||

Hi Donald

No symptoms or problems, the package executes successfully. Apart from the fact that the variables aren't updated at the end of the package when I look at them in the variable screen.

Derek

|||

Hi Derek,

The Variable Screen will always show the Initial Value set for that variable.

To check whether your program works fine -

1) Change the DateTime variable to 'Package' Scope.
2) Place a breakpoint for the PreExecute Event for the following task
3) Execute the Package and When the program stops for the BreakPoint - Select Debug Option -> Windows -> Locals
4) Scroll through the list and Locate the DateTime Variable.
5) You will find that the DatTime Variable is updated with the value returned by the SQL Task.

Alternatively -

1) Change the DateTime variable to 'Package' Scope.
2) On Success of the SQL Task - Have a Script Task
3) Put that DateTimeVariable in ReadOnlyVariables list in Script Task
4) Have the following lines of code in the Public Sub Main() method in the script task

Windows.Forms.MessageBox.Show(Dts.Variables("<DateTimeVariable>").Value.ToString())
Dts.TaskResult = Dts.Results.Success

5) You will observe that the datetime value returned by the SQL Statement in SQL Task will be poped up in a small popup window.

After the successful execution of the package - if you go to the Variables window - you will observe that it will only show the initial value. The runtime value assigned to the DateTimeVaribale isn't stored permanently and the runtime value will only be available during execution.

Thanks,
Loonysan

|||

Thanks Loonysan

you were right, it was working just couldn't see it.

Cheers,

Derek

Execute SQL Task Error: no result rowset associated...

I am getting the following error when I execute my sql task:

An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "

I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:

sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @.[User::SnapShotDate] + "', NULL"
NullVar (string)

In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.

So I am not sure what I am missing here. Anyone have any suggestions?

Thanks!
John

I would try to catch the SP output via parameter mapping in the execute sql task.

For that, you have to change ResultSet=None; delete the result set from result set tab; and add 2 entries in the parameter mapping page; these 2 entries have to be in the same order you are using the parameters in the Exec t-sql command. If you are using OLE DB connection; the parameter name in the parameter mapping page should be 0 and 1.

The SQLSourceType should be DirectInput and the SQLStatment: EXEC RBCprcsInsertWmsInvTransactionRecords @.yourInputParameterName = ?, @.YourOutputParameterName = ? OUTPUT

|||

Rafael,

I was missing how to correctly specify the parameters. Thanks for giving me the correct syntax! I had tried something similar to this before using the variable to pass the sql statement but didn't have it quite right.

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Thanks again!
John

|||

laker_42 wrote:

Can i add one more question onto this? Do I have to turn on logging inorder to capture this output variable to a log file?

Package logging would give you logging information about package execution; I don’t think is the way to go for getting the value of a SSIS variable written into a file. I cannot think on a quick way to do so; but perhaps you could use a script task.

|||Ok, i will do some more digging on that. Thanks for your help!

John

Execute SQL Task (catching results)

Hi,

I'm trying to use an execute SQL task with a simple select to get results and scan them.

I have to create a variable for each column to get results? or may i create something like a resultset variable?

Does this task return only one row and i have to loop manually? (maybe on al script task..) or can i get all returned data on a result set to be the input for next step?

Thanks!

Hi ya,

It depends on the result set property of the Execute sql task. The property can be set to xml, single row, full result set or none.
So whatever variable you're creating would be entirely dependent on the result set and how you want to achieve it.

Cheers

Rizwan

|||

I have set the property to "full result set", and create a variable which its type is "Object", but how can i get and scan the results in the next step?|||

You would need to point the resultset to a variable. Take a look at this article on Sql IS.

http://www.sqlis.com/58.aspx

This has how return a full row set in a variable.

Hope that helps

Cheers

RIzwan

|||

Thanks RizwanKhan,

I have read the article and its very interesting, but there is'nt nothing regarding how to manage the output (rowset).

Do you have any article about it? Can i get this rowset to be the input of another component or script?

Thanks!!

|||

You can use a For Each Loop to iterate the resultset in the control flow, or you can use a script component in the data source to "shred" the recordset.

A couple of examples (there are many, many more, if you search for them).

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/writing-a-resultset-to-a-flat-file.aspx

Execute SQL Task - Assign the result to a variable

Hi,

Let's say that the query in my SQL Task returns a single integer number.

How can I put that single number in a variable?

Thank you.

You should read through Books On-Line for SSIS.

Here's another resource for the Execute SQL Task: http://www.sqlis.com/58.aspx

|||Thank you.

Execute Sql Statement Stored in Table Type Variable

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.hi
Just In the end of your Storedprodure type
print @.your SQl Statment|||

Quote:

Originally Posted by hisham123

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.

Hi,
select the four rows in to four variables @.a,@.b,@.c,@.d,
remember while assigning you have to put the row values in single quotes
then execute the query by using this technic
[CODE]
exec(@.a+' '+@.b+' '+@.c+' '+@.d)

Execute Process Task; How to use user variable as an argument

I am trying to call a executable that takes an argument. I am using an "execute process task" and have declared a user string variable "file_name" (c:\file.txt)

How do I use this variable so that the executable will see it as an argument.

You can use expressions to define the path and parameters of you executable... Then you don't pass the variable but construct the "command line" with an expression including the parameter (which is the value of your variable)...

Execute package results different to step by step execution - uses raw file

I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.

Here's the rub:

If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).

I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.

Any help is greatly appreciated.

I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)

Thanks

B.

Hi Brian,

Interesting.

A workaround question/suggestion: Would staging the data between tasks in a database staging table work for you?

Just curious,
Andy

|||

Hi Andy,

I was hoping someone has encountered this and has a quick fix - that said....

Short answer - yes - would probably work, but I would prefer not to (I don't really like using temporary tables if I can avoid them).

The data that is being stored in the files are new guids and their relationship to old composite ids, generated during the processing of the records. I guess that storing that information in a staging database is a possibility, but I would hate to have to re-work that entire block of code.

Ideally, if it can't be stored in raw files, then connection specific temporary tables would have been my next option - but those are not really available either.

Thanks,

B.