Executing SP inside SP dynamically

I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!In Books OnLine, look up the keywords OUTPUT variable and RETURN.|||Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.|||I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!

Try This

DECLARE @.sql nvarchar(2048)
SET @.sql = ' SET @.count = ( SELECT COUNT(*) FROM table1 )'
DECLARE @.temp int
EXEC sp_executesql @.sql, N'@.count int OUTPUT', @.temp OUTPUT

Jamessql

Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.

executing a dymamically built string with an OUTPUT parameter

Hi friends,

I have a dynamically built string that i need to execute and set a parameter to. I have come accross help in the books online but I need to set a variable to the answer of the dynamically executed string. It looks as follows:

DECLARE @.sRCDQueryString nvarchar(1000)
DECLARE @.sActivityName varchar(100)
DECLARE @.sRCDParmDefinition nvarchar(500)
DECLARE @.lGLCodeID int
DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID = 1--391
SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT ' + @.sActivityName +
' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'
SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';
EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

When I execute this code, I get a value by the line in red but it doesn't seem to be allocating a the result correctly to @.rRCDUnitValue since this variable is null

How can i get the result correctly allocated to the variable?

Regards

Use the following statement, you missed the value assignment,

Code Snippet

DECLARE @.sRCDQueryString nvarchar(1000)

DECLARE @.sActivityName varchar(100)

DECLARE @.sRCDParmDefinition nvarchar(500)

DECLARE @.lGLCodeID int

DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID =1--391

SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT @.Value=' + @.sActivityName +

' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'

SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';

EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

|||

You are declaring a parameter, but that parameter is not being used at all inside the dynamic statement. See this example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.orderid int

declare @.order_total money

set @.sql = N'select @.order_total = sum(quantity * unitprice * (1.00 - discount)) from dbo.[order details] where orderid = @.orderid'

set @.orderid = 10250

exec dbo.sp_executesql @.sql, N'@.orderid int, @.order_total money OUTPUT', @.orderid, @.order_total OUTPUT

select @.order_total

go

AMB

|||

You got the answers for how to fix yoru code. But why do you need dynamic SQL in the first place? Are you aware of the risks and performance problems (benefits in some cases) with dynamic SQL code?

|||Hi Manivannan,

The @.sActivityName is actually the name of an unknown column that is looked up on another table, wich has been pivoted on the current table i'm trying to do a lookup on.

Thanks for the response
Mike
|||Hi Umachandar,

Yes I am aware of the performance knock but I have no choice in this matter to use a dynamic string since the columns I need to lookup/use are unknown at the point of execution since they are looked up from another table..

Essentially they are the result of a table that had been pivoted.

Regards
Mike
|||Ahhh, Thank you indeed sir, you have showed me the mistake and I appreciate it coz my variable now returns a value.

Kind Regards
Mike
sql

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 SQL dynamically

Hi,
I want to execute some SQL statement that has more than 4000 characters, is
there any way to do it?
I'm using the exec sp_executesql command as following:
declare @.SQLString nvarchar(4000)
select @.SQLString = 'SQL statement more than 4000 characters'
exec sp_executesql @.SQLString
ThanksFerreira wrote:

> Hi,
> I want to execute some SQL statement that has more than 4000 characters, i
s
> there any way to do it?
> I'm using the exec sp_executesql command as following:
> declare @.SQLString nvarchar(4000)
> select @.SQLString = 'SQL statement more than 4000 characters'
> exec sp_executesql @.SQLString
> Thanks
declare @.var1 navarchar(4000)
declare @.var2 navarchar(4000)
set @.var1 = 'bla bla ..................'
set @.var2 = 'aaaabbbb ...............'
exec (@.var1 + @.var2)
Regards
Amish Shah|||http://www.sommarskog.se/dynamic_sql.html
Madhivanan
amish wrote:
> Ferreira wrote:
>
> declare @.var1 navarchar(4000)
> declare @.var2 navarchar(4000)
> set @.var1 = 'bla bla ..................'
> set @.var2 = 'aaaabbbb ...............'
> exec (@.var1 + @.var2)
> Regards
> Amish Shah|||Ferreira (Ferreira@.discussions.microsoft.com) writes:
> I want to execute some SQL statement that has more than 4000 characters,
> is there any way to do it? > I'm using the exec sp_executesql command as
> following:
> declare @.SQLString nvarchar(4000)
> select @.SQLString = 'SQL statement more than 4000 characters'
> exec sp_executesql @.SQLString
In addition to the other posts, note that if you are on SQL 2005, you can
use the new data type nvarchar(MAX) instead. This data type is unlimited
like ntext, but does not have all the restrictions of ntext.
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 RDL dynamically...

I know you can create RDL programatically...but is it possible to execute
the RDL on a report server without having to publish it to the server? I
want to be able to generate some RDL on the fly and execute it on the report
server and display the report to a user. Thanks for any help!!
Sunish AbrahamNo, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and work
with RS 2005) will have a web and winform control that can run in local
mode, you give it a dataset and the report and away it goes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>I know you can create RDL programatically...but is it possible to execute
>the RDL on a report server without having to publish it to the server? I
>want to be able to generate some RDL on the fly and execute it on the
>report server and display the report to a user. Thanks for any help!!
> Sunish Abraham
>|||Hi Bruce!
Thanks for the quick response! Is what you are saying is that you can
generate RDL and a dataset and execute the report using the dataset without
having to "publish" the report to the reporting server? Is this control
part of the SQL2005 RS samples (if so what is the name of the control)?
Thanks again!!
Sunish
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
> work with RS 2005) will have a web and winform control that can run in
> local mode, you give it a dataset and the report and away it goes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to execute
>>the RDL on a report server without having to publish it to the server? I
>>want to be able to generate some RDL on the fly and execute it on the
>>report server and display the report to a user. Thanks for any help!!
>> Sunish Abraham
>>
>|||It is part of Widbey. They were there with the last beta but I am not sure
on whether or not there were any problems with it. Anyway, these controls do
not ship with RS, only with VS.
In local mode you don't even have to have a server around. The controls work
with a RS server if you have one but you also have the option of using the
controls in local mode.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi Bruce!
> Thanks for the quick response! Is what you are saying is that you can
> generate RDL and a dataset and execute the report using the dataset
> without having to "publish" the report to the reporting server? Is this
> control part of the SQL2005 RS samples (if so what is the name of the
> control)? Thanks again!!
> Sunish
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to execute
>>the RDL on a report server without having to publish it to the server? I
>>want to be able to generate some RDL on the fly and execute it on the
>>report server and display the report to a user. Thanks for any help!!
>> Sunish Abraham
>>
>>
>|||I see that there is a ReportViewer subdirectory under the VS.NET install
directory which contains compiled files (Microsoft.ReportViewer.Common.dll,
Microsoft.ReportViewer.WebForms.dll,
Microsoft.ReportViewer.WinForms.dll)...are these the controls that you are
referencing? Is there documentation on how to use these controls? Thanks!
Sunish
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uYFFMy9sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> It is part of Widbey. They were there with the last beta but I am not sure
> on whether or not there were any problems with it. Anyway, these controls
> do not ship with RS, only with VS.
> In local mode you don't even have to have a server around. The controls
> work with a RS server if you have one but you also have the option of
> using the controls in local mode.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
> news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi Bruce!
>> Thanks for the quick response! Is what you are saying is that you can
>> generate RDL and a dataset and execute the report using the dataset
>> without having to "publish" the report to the reporting server? Is this
>> control part of the SQL2005 RS samples (if so what is the name of the
>> control)? Thanks again!!
>> Sunish
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to
>>execute the RDL on a report server without having to publish it to the
>>server? I want to be able to generate some RDL on the fly and execute
>>it on the report server and display the report to a user. Thanks for
>>any help!!
>> Sunish Abraham
>>
>>
>>
>|||Sorry, I don't know much more that what I have told you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunish Abraham" <sunisha@.comcast.net> wrote in message
news:OtOVKJ%23sFHA.664@.tk2msftngp13.phx.gbl...
>I see that there is a ReportViewer subdirectory under the VS.NET install
>directory which contains compiled files (Microsoft.ReportViewer.Common.dll,
>Microsoft.ReportViewer.WebForms.dll,
>Microsoft.ReportViewer.WinForms.dll)...are these the controls that you are
>referencing? Is there documentation on how to use these controls? Thanks!
> Sunish
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uYFFMy9sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>> It is part of Widbey. They were there with the last beta but I am not
>> sure on whether or not there were any problems with it. Anyway, these
>> controls do not ship with RS, only with VS.
>> In local mode you don't even have to have a server around. The controls
>> work with a RS server if you have one but you also have the option of
>> using the controls in local mode.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:ODG8nv9sFHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi Bruce!
>> Thanks for the quick response! Is what you are saying is that you can
>> generate RDL and a dataset and execute the report using the dataset
>> without having to "publish" the report to the reporting server? Is this
>> control part of the SQL2005 RS samples (if so what is the name of the
>> control)? Thanks again!!
>> Sunish
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23JJWAx7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>> No, you cannot (with RS 2000). VS 2005 (controls ship with VS 2005 and
>> work with RS 2005) will have a web and winform control that can run in
>> local mode, you give it a dataset and the report and away it goes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Sunish Abraham" <sunisha@.comcast.net> wrote in message
>> news:eTf3Ye7sFHA.904@.tk2msftngp13.phx.gbl...
>>I know you can create RDL programatically...but is it possible to
>>execute the RDL on a report server without having to publish it to the
>>server? I want to be able to generate some RDL on the fly and execute
>>it on the report server and display the report to a user. Thanks for
>>any help!!
>> Sunish Abraham
>>
>>
>>
>>
>

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp