EXECUTE AS question: based

Showing posts with label based. Show all posts

Thursday, March 29, 2012

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

Wednesday, March 7, 2012

Execute SQL based on number rows in two tables

I have a table that I would like to backup each morning only if the number o
f
records in this table is greater than in another table. How can achieve
this? Basically, I would like it to be like this:
IF (rowcount in A) > (rowcount in B) then
Execute SQL
ELSE --NOTHING
END IF"examnotes" <Pasha@.discussions.microsoft.com> wrote in
news:DA8B4552-A94A-4974-9C4A-9A639CC7C1EC@.microsoft.com:

> I have a table that I would like to backup each morning only if the
> number of records in this table is greater than in another table. How
> can achieve this? Basically, I would like it to be like this:
> IF (rowcount in A) > (rowcount in B) then
> Execute SQL
> ELSE --NOTHING
> END IF
Something like this?
if ((select count(*) from sys.tables) > (select count(*) from sys.views))
begin
print 'More tables than views'
end
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Below is what I was able to create. Not sure if it is the best solution
though:
if ((select rows FROM sysindexes WHERE id = OBJECT_ID('Daily_Fin_Apps') AND
indid < 2) > (select rows FROM sysindexes WHERE id =
OBJECT_ID('BACKUP_Daily_Fin_Apps') AND indid < 2))
begin
truncate table [BACKUP_Daily_Fin_Apps]
insert into [BACKUP_Daily_Fin_Apps]
select * from [Daily_Fin_Apps]
end
Thanks,
Pasha
"Ole Kristian Bang?s" wrote:

> "examnotes" <Pasha@.discussions.microsoft.com> wrote in
> news:DA8B4552-A94A-4974-9C4A-9A639CC7C1EC@.microsoft.com:
>
> Something like this?
> if ((select count(*) from sys.tables) > (select count(*) from sys.views))
> begin
> print 'More tables than views'
> end
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

Friday, February 24, 2012

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas?
nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas?
|||On Mar 14, 10:35Xam, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. XApparently the login used by your ASP report
> still has access to the database, but not to that stored procedure. XIt
> could be that someone changed the rights being granted the report user. You
> can check what rights it still has by:
> EXEC sp_helprotect @.username = 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedure
> was created , but the rights were not regranted. XThis is a problem whena
> stored procedure is dropped and recreated. XYou can check by:
> select name, crdate
> from sysobjects
> where name = 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
>
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.

EXECUTE permission denied

I'm running an ASP based report. It's always worked, but for some
reason now I get the following:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
'dbo'.
Any ideas'nick,
The error is pretty specific. Apparently the login used by your ASP report
still has access to the database, but not to that stored procedure. It
could be that someone changed the rights being granted the report user. You
can check what rights it still has by:
EXEC sp_helprotect @.username = 'YourReportUserAccount'
However, a more likely suspect is that a new version of the stored procedure
was created , but the rights were not regranted. This is a problem when a
stored procedure is dropped and recreated. You can check by:
select name, crdate
from sysobjects
where name = 'IFMSpVisitbyStatus'
If that is the case, some one will need to:
GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
Also, if that is the problem then the process for deploying updated SQL
Server objects apparently needs to be tightened up a bit to ensure that
rights are preserved or regranted.
RLF
"nick" <cipher7836@.gmail.com> wrote in message
news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
> I'm running an ASP based report. It's always worked, but for some
> reason now I get the following:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> 'dbo'.
> Any ideas'|||On Mar 14, 10:35=A0am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> nick,
> The error is pretty specific. =A0Apparently the login used by your ASP rep=ort
> still has access to the database, but not to that stored procedure. =A0It
> could be that someone changed the rights being granted the report user. Yo=u
> can check what rights it still has by:
> EXEC sp_helprotect @.username =3D 'YourReportUserAccount'
> However, a more likely suspect is that a new version of the stored procedu=re
> was created , but the rights were not regranted. =A0This is a problem when= a
> stored procedure is dropped and recreated. =A0You can check by:
> select name, crdate
> from sysobjects
> where name =3D 'IFMSpVisitbyStatus'
> If that is the case, some one will need to:
> GRANT EXECUTE ON IFMSpVisitbyStatus TO YourReportUserAccount
> Also, if that is the problem then the process for deploying updated SQL
> Server objects apparently needs to be tightened up a bit to ensure that
> rights are preserved or regranted.
> RLF
> "nick" <cipher7...@.gmail.com> wrote in message
> news:73eed503-cf29-422f-8d03-f168b1c723ca@.f63g2000hsf.googlegroups.com...
>
> > I'm running an ASP based report. It's always worked, but for some
> > reason now I get the following:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
> > denied on object 'IFMSpVisitbyStatus', database 'Paradigm', owner
> > 'dbo'.
> > Any ideas?... Hide quoted text -
> - Show quoted text -
Thanks for the information! Sad to say, but I know next to nothing
about SQL. I just wanted to help the user run an already created
report.

Execute permission cannot be acquired?

I have an assembly which uses file I/O to stream in some text based reports
into a container in a report. I have set the FileIOPermissionAttribute and
it works in the design environment but when I deploy the dll to the report
server it only works on that box. If I try to run it through the preview
window I get the following error: "Execute permission cannot be acquired"
Anybody help?Did you get this resolved? Assuming that the security policies are updated
correctly and you assert the FileIOPermission in the custom assembly, it
could be a file system permission issue. Basically, the executing user does
not have permissions to access the file. Check the NTFS permissions on the
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:3FA6A470-1E7D-421D-AF3E-226ADAC397D9@.microsoft.com...
> I have an assembly which uses file I/O to stream in some text based
reports
> into a container in a report. I have set the FileIOPermissionAttribute
and
> it works in the design environment but when I deploy the dll to the report
> server it only works on that box. If I try to run it through the preview
> window I get the following error: "Execute permission cannot be acquired"
> Anybody help?
>

Sunday, February 19, 2012

Execute package on conditions

Hi,

I need to write a condition on the start of my package. Based on the condition different task has to execute.

Example :

I will create a package level var MyVar bool. if it's true task a has to execute else task b.

I thought of using conditional split, But it asks for input connection as it's a transform control.

How to do this?

Any Ideas?

You need to use this condition in control flow, not in data flow. At the control flow you may define precedence constraints between tasks, and have conditional expressions on the precedence constraints.

So, if a task does not need to be executed, double click precedence constraint leading to it, and type expression !MyVar.

Friday, February 17, 2012

Execute Job from another Job based on failure

I have two jobs A and B. I want to execute Job B is job
A fails but only if the error number is 3013. How can I
make this happen?
Thanks,
Vichow about sp_start_job?
"Vic" <vduran@.specpro-inc.com> wrote in message
news:e9fa01c3f183$e79f4350$a101280a@.phx.gbl...
> I have two jobs A and B. I want to execute Job B is job
> A fails but only if the error number is 3013. How can I
> make this happen?
> Thanks,
> Vic

Execute Job from another Job based on failure

Execute external process from CLR based Stored procedure

Hi All,

I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine.

Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet.

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void RunProc(string arg)

{

SqlPipe pipe = SqlContext.Pipe;

pipe.Send("Hello");

Process.Start("E:\test.exe");

}

CREATE ASSEMBLY [RunProcess]

FROM 'RunProcess.dll'

CREATE PROCEDURE dbo.sqlclr_RunProc

(

@.arg nvarchar(1024)

)

AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]

Thanks

Sid

Also the Error that i get is

Msg 6522, Level 16, State 1, Procedure sqlclr_RunProc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'sqlclr_RunProc':

System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at StoredProcedures.RunProc(String arg)

|||hi

i have done my sp to write eventlog in .txt i too faced some problems try these queries

use master
go
exec sp_dbcmptlevel 'databasename', 90
go

to know more about it see

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

then make the sql server databae trustworthy

ALTER DATABASE databasename SET TRUSTWORTHY ON

try this and let me now|||Your assembly the you deploy to the the database has to have the unsafe permission set:

Code Snippet

CREATE ASSEMBLY some_name
FROM 'some_path'
WITH PERMISSION_SET = UNSAFE

However, in order to create an unsafe assembly some other permissions need to be in place. This can be done in two ways:
1. by using certificates
2. by setting the database to be trustworthy and to grant UNSAFE ASSEMBLY to the login of the owber of the database.

The second way is the easiest, but I would not recommend it for production. Assuming the database is created by dbo and the dbo is also admin on the box, you do something like this:

Code Snippet

use master;
go

GRANT UNSAFE ASSEMBLY to [Builtin\Administrators];
go

ALTER database your_db_name
set trustworthy on;
go

Oh, and notice that when you then finally execute your proc and your external process runs, it will run under the SQL Server Service account.

Hope this helps.

Niels

|||

Thanks Neils, That helps.

|||Hi

altering database to trustworthy only for executing sql clr is very easy but not advicable method too
see my below post u will get some more option to do this without enabling trustworthy for the database

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1

Thanks

Execute external process from CLR based Stored procedure

Hi All,

I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine.

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void RunProc(string arg)

{

SqlPipe pipe = SqlContext.Pipe;

pipe.Send("Hello");

Process.Start("E:\test.exe");

}

CREATE ASSEMBLY [RunProcess]

FROM 'RunProcess.dll'

CREATE PROCEDURE dbo.sqlclr_RunProc

(

@.arg nvarchar(1024)

)

AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]

Thanks

Sid

Also the Error that i get is

Msg 6522, Level 16, State 1, Procedure sqlclr_RunProc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'sqlclr_RunProc':

System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at StoredProcedures.RunProc(String arg)

|||hi

i have done my sp to write eventlog in .txt i too faced some problems try these queries

use master
go
exec sp_dbcmptlevel 'databasename', 90
go

to know more about it see

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

then make the sql server databae trustworthy

ALTER DATABASE databasename SET TRUSTWORTHY ON

try this and let me now|||Your assembly the you deploy to the the database has to have the unsafe permission set:

Code Snippet

CREATE ASSEMBLY some_name
FROM 'some_path'
WITH PERMISSION_SET = UNSAFE

The second way is the easiest, but I would not recommend it for production. Assuming the database is created by dbo and the dbo is also admin on the box, you do something like this:

Code Snippet

use master;
go

GRANT UNSAFE ASSEMBLY to [Builtin\Administrators];
go

ALTER database your_db_name
set trustworthy on;
go

Oh, and notice that when you then finally execute your proc and your external process runs, it will run under the SQL Server Service account.

Hope this helps.

Niels

|||

Thanks Neils, That helps.

|||Hi

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1

Thanks

Wednesday, February 15, 2012

Execute Different Stored Procedure Based on Parameter Value

I have been trying to determine if I can run a different stored procedure
base on a parameter value. I have a stored procedure on each of my SQL
Server machines that lists the SQL Server jobs. I would like to be able use
a parameter to list the SQL Server machines, select one of the SQL Server
machines and execute the appropriate stored procedure using a linked server
that I already have setup.
How can this be accomplished?
Thanks in advance!Use the generic query designer and put in T-SQL statements in the designer.
Here is some code as an example that I was just messing around with one day.
declare @.SQL varchar(255)
select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
= ''U'' order by name'
exec (@.SQL)
It should give you an idea on how to do what you want to do.
The above had a report parameter of Database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> I have been trying to determine if I can run a different stored procedure
> base on a parameter value. I have a stored procedure on each of my SQL
> Server machines that lists the SQL Server jobs. I would like to be able
use
> a parameter to list the SQL Server machines, select one of the SQL Server
> machines and execute the appropriate stored procedure using a linked
server
> that I already have setup.
> How can this be accomplished?
> Thanks in advance!|||Hi,
If this can be done without the usage of the stored procedure on the
remote server, you could try something like this:
DECLARE @.SQLString NVARCHAR(4000)
SELECT @.SQLString = N'SELECT * FROM OPENQUERY (' +
RTRIM(@.ParamServerName) + ',SELECT ..,.., FROM dbo.sysjobs '')'
EXEC sp_executesql @.SQLString
as long as your server is set-up to access the msdb database...|||Bruce,
Thanks for the quick response! I ran into a problem. I put the following
in designer:
DECLARE @.SQL varchar(255);
SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
EXEC (@.SQL)
I have a report parameter setup called Server and it has two values listed
to select. When I try to preview the report, I get compilation errors saying:
The value expression for the textbox â'job_nameâ' refers to the field
â'job_nameâ'. Report item expressions can only refer to fields within the
current data set scope or, if inside an aggregate, the specified data set
scope.
There is one error for each column that is used in the report.
It's like Reporting Services does not recognize the data that the stored
procedure is returning because it is executing a dynamically prepared SQL
statement.
Is there anything I can do to get around this?
"Bruce L-C [MVP]" wrote:
> Use the generic query designer and put in T-SQL statements in the designer.
> Here is some code as an example that I was just messing around with one day.
> declare @.SQL varchar(255)
> select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> = ''U'' order by name'
> exec (@.SQL)
> It should give you an idea on how to do what you want to do.
> The above had a report parameter of Database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > I have been trying to determine if I can run a different stored procedure
> > base on a parameter value. I have a stored procedure on each of my SQL
> > Server machines that lists the SQL Server jobs. I would like to be able
> use
> > a parameter to list the SQL Server machines, select one of the SQL Server
> > machines and execute the appropriate stored procedure using a linked
> server
> > that I already have setup.
> >
> > How can this be accomplished?
> >
> > Thanks in advance!
>
>|||Try clicking on the refresh fields button (to the right of the ...) it looks
like the refresh button for IE.
Just to check I took the one I sent you and changed it and clicked on the
field refresh and it worked.
One point, what you have there does not look to me like it will work, you
need to have another period. It looks to me like you will get this:
exec someservermsdb..USP_ListSQLServerJobs
the way you currently have it.
Here is another trick you can do when working on something like this.
Replace your exec to this:
select @.SQL as SQLString
Excute the query and you can now see what you have. Good way to figure out
what is happening.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> Bruce,
> Thanks for the quick response! I ran into a problem. I put the following
> in designer:
> DECLARE @.SQL varchar(255);
> SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> EXEC (@.SQL)
> I have a report parameter setup called Server and it has two values listed
> to select. When I try to preview the report, I get compilation errors
saying:
> The value expression for the textbox 'job_name' refers to the field
> 'job_name'. Report item expressions can only refer to fields within the
> current data set scope or, if inside an aggregate, the specified data set
> scope.
> There is one error for each column that is used in the report.
> It's like Reporting Services does not recognize the data that the stored
> procedure is returning because it is executing a dynamically prepared SQL
> statement.
> Is there anything I can do to get around this?
> "Bruce L-C [MVP]" wrote:
> > Use the generic query designer and put in T-SQL statements in the
designer.
> > Here is some code as an example that I was just messing around with one
day.
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
xtype
> > = ''U'' order by name'
> > exec (@.SQL)
> >
> > It should give you an idea on how to do what you want to do.
> >
> > The above had a report parameter of Database.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > I have been trying to determine if I can run a different stored
procedure
> > > base on a parameter value. I have a stored procedure on each of my
SQL
> > > Server machines that lists the SQL Server jobs. I would like to be
able
> > use
> > > a parameter to list the SQL Server machines, select one of the SQL
Server
> > > machines and execute the appropriate stored procedure using a linked
> > server
> > > that I already have setup.
> > >
> > > How can this be accomplished?
> > >
> > > Thanks in advance!
> >
> >
> >|||Bruce,
> Try clicking on the refresh fields button (to the right of the ...) it looks
> like the refresh button for IE.
I am not getting a <Refresh> button on the Preview tab for the report.
> One point, what you have there does not look to me like it will work, you
> need to have another period. It looks to me like you will get this:
> exec someservermsdb..USP_ListSQLServerJobs
> the way you currently have it.
I am adding the extra period that I need to the value that is returned for
the @.Server parameter.
> Here is another trick you can do when working on something like this.
> Replace your exec to this:
> select @.SQL as SQLString
> Excute the query and you can now see what you have. Good way to figure out
> what is happening.
When I add the "select @.SQL as SQLString" and execute it through Query
Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
works perfectly.
Do you have any other possibilities?
Thanks in advance!
Scott
"Bruce L-C [MVP]" wrote:
> Try clicking on the refresh fields button (to the right of the ...) it looks
> like the refresh button for IE.
> Just to check I took the one I sent you and changed it and clicked on the
> field refresh and it worked.
> One point, what you have there does not look to me like it will work, you
> need to have another period. It looks to me like you will get this:
> exec someservermsdb..USP_ListSQLServerJobs
> the way you currently have it.
> Here is another trick you can do when working on something like this.
> Replace your exec to this:
> select @.SQL as SQLString
> Excute the query and you can now see what you have. Good way to figure out
> what is happening.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > Bruce,
> > Thanks for the quick response! I ran into a problem. I put the following
> > in designer:
> >
> > DECLARE @.SQL varchar(255);
> > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > EXEC (@.SQL)
> >
> > I have a report parameter setup called Server and it has two values listed
> > to select. When I try to preview the report, I get compilation errors
> saying:
> >
> > The value expression for the textbox 'job_name' refers to the field
> > 'job_name'. Report item expressions can only refer to fields within the
> > current data set scope or, if inside an aggregate, the specified data set
> > scope.
> >
> > There is one error for each column that is used in the report.
> >
> > It's like Reporting Services does not recognize the data that the stored
> > procedure is returning because it is executing a dynamically prepared SQL
> > statement.
> >
> > Is there anything I can do to get around this?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Use the generic query designer and put in T-SQL statements in the
> designer.
> > > Here is some code as an example that I was just messing around with one
> day.
> > >
> > > declare @.SQL varchar(255)
> > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
> xtype
> > > = ''U'' order by name'
> > > exec (@.SQL)
> > >
> > > It should give you an idea on how to do what you want to do.
> > >
> > > The above had a report parameter of Database.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> message
> > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > I have been trying to determine if I can run a different stored
> procedure
> > > > base on a parameter value. I have a stored procedure on each of my
> SQL
> > > > Server machines that lists the SQL Server jobs. I would like to be
> able
> > > use
> > > > a parameter to list the SQL Server machines, select one of the SQL
> Server
> > > > machines and execute the appropriate stored procedure using a linked
> > > server
> > > > that I already have setup.
> > > >
> > > > How can this be accomplished?
> > > >
> > > > Thanks in advance!
> > >
> > >
> > >
>
>|||The refresh fields button is on the dataset tab. You need to be showing the
fields for your dataset. My understanding of the issue you are having is
that you do not have fields showing for the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> Bruce,
> > Try clicking on the refresh fields button (to the right of the ...) it
looks
> > like the refresh button for IE.
> I am not getting a <Refresh> button on the Preview tab for the report.
> > One point, what you have there does not look to me like it will work,
you
> > need to have another period. It looks to me like you will get this:
> >
> > exec someservermsdb..USP_ListSQLServerJobs
> >
> > the way you currently have it.
> I am adding the extra period that I need to the value that is returned for
> the @.Server parameter.
> > Here is another trick you can do when working on something like this.
> > Replace your exec to this:
> >
> > select @.SQL as SQLString
> >
> > Excute the query and you can now see what you have. Good way to figure
out
> > what is happening.
> When I add the "select @.SQL as SQLString" and execute it through Query
> Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
> works perfectly.
> Do you have any other possibilities?
> Thanks in advance!
> Scott
> "Bruce L-C [MVP]" wrote:
> > Try clicking on the refresh fields button (to the right of the ...) it
looks
> > like the refresh button for IE.
> >
> > Just to check I took the one I sent you and changed it and clicked on
the
> > field refresh and it worked.
> >
> > One point, what you have there does not look to me like it will work,
you
> > need to have another period. It looks to me like you will get this:
> >
> > exec someservermsdb..USP_ListSQLServerJobs
> >
> > the way you currently have it.
> >
> > Here is another trick you can do when working on something like this.
> > Replace your exec to this:
> >
> > select @.SQL as SQLString
> >
> > Excute the query and you can now see what you have. Good way to figure
out
> > what is happening.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > Bruce,
> > > Thanks for the quick response! I ran into a problem. I put the
following
> > > in designer:
> > >
> > > DECLARE @.SQL varchar(255);
> > > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > > EXEC (@.SQL)
> > >
> > > I have a report parameter setup called Server and it has two values
listed
> > > to select. When I try to preview the report, I get compilation errors
> > saying:
> > >
> > > The value expression for the textbox 'job_name' refers to the field
> > > 'job_name'. Report item expressions can only refer to fields within
the
> > > current data set scope or, if inside an aggregate, the specified data
set
> > > scope.
> > >
> > > There is one error for each column that is used in the report.
> > >
> > > It's like Reporting Services does not recognize the data that the
stored
> > > procedure is returning because it is executing a dynamically prepared
SQL
> > > statement.
> > >
> > > Is there anything I can do to get around this?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Use the generic query designer and put in T-SQL statements in the
> > designer.
> > > > Here is some code as an example that I was just messing around with
one
> > day.
> > > >
> > > > declare @.SQL varchar(255)
> > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
where
> > xtype
> > > > = ''U'' order by name'
> > > > exec (@.SQL)
> > > >
> > > > It should give you an idea on how to do what you want to do.
> > > >
> > > > The above had a report parameter of Database.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > message
> > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > I have been trying to determine if I can run a different stored
> > procedure
> > > > > base on a parameter value. I have a stored procedure on each of
my
> > SQL
> > > > > Server machines that lists the SQL Server jobs. I would like to
be
> > able
> > > > use
> > > > > a parameter to list the SQL Server machines, select one of the SQL
> > Server
> > > > > machines and execute the appropriate stored procedure using a
linked
> > > > server
> > > > > that I already have setup.
> > > > >
> > > > > How can this be accomplished?
> > > > >
> > > > > Thanks in advance!
> > > >
> > > >
> > > >
> >
> >
> >|||Bruce,
Thanks! I found it and everything is working great now. I really
appreciate the help!
Scott
"Bruce L-C [MVP]" wrote:
> The refresh fields button is on the dataset tab. You need to be showing the
> fields for your dataset. My understanding of the issue you are having is
> that you do not have fields showing for the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
> news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> > Bruce,
> > > Try clicking on the refresh fields button (to the right of the ...) it
> looks
> > > like the refresh button for IE.
> > I am not getting a <Refresh> button on the Preview tab for the report.
> >
> > > One point, what you have there does not look to me like it will work,
> you
> > > need to have another period. It looks to me like you will get this:
> > >
> > > exec someservermsdb..USP_ListSQLServerJobs
> > >
> > > the way you currently have it.
> > I am adding the extra period that I need to the value that is returned for
> > the @.Server parameter.
> >
> > > Here is another trick you can do when working on something like this.
> > > Replace your exec to this:
> > >
> > > select @.SQL as SQLString
> > >
> > > Excute the query and you can now see what you have. Good way to figure
> out
> > > what is happening.
> > When I add the "select @.SQL as SQLString" and execute it through Query
> > Analyzer, I get the SQL that I am expecting. When I execute the SQL, it
> > works perfectly.
> >
> > Do you have any other possibilities?
> >
> > Thanks in advance!
> > Scott
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Try clicking on the refresh fields button (to the right of the ...) it
> looks
> > > like the refresh button for IE.
> > >
> > > Just to check I took the one I sent you and changed it and clicked on
> the
> > > field refresh and it worked.
> > >
> > > One point, what you have there does not look to me like it will work,
> you
> > > need to have another period. It looks to me like you will get this:
> > >
> > > exec someservermsdb..USP_ListSQLServerJobs
> > >
> > > the way you currently have it.
> > >
> > > Here is another trick you can do when working on something like this.
> > > Replace your exec to this:
> > >
> > > select @.SQL as SQLString
> > >
> > > Excute the query and you can now see what you have. Good way to figure
> out
> > > what is happening.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> message
> > > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > > Bruce,
> > > > Thanks for the quick response! I ran into a problem. I put the
> following
> > > > in designer:
> > > >
> > > > DECLARE @.SQL varchar(255);
> > > > SELECT @.SQL = 'exec ' + @.Server + 'msdb..USP_ListSQLServerJobs';
> > > > EXEC (@.SQL)
> > > >
> > > > I have a report parameter setup called Server and it has two values
> listed
> > > > to select. When I try to preview the report, I get compilation errors
> > > saying:
> > > >
> > > > The value expression for the textbox 'job_name' refers to the field
> > > > 'job_name'. Report item expressions can only refer to fields within
> the
> > > > current data set scope or, if inside an aggregate, the specified data
> set
> > > > scope.
> > > >
> > > > There is one error for each column that is used in the report.
> > > >
> > > > It's like Reporting Services does not recognize the data that the
> stored
> > > > procedure is returning because it is executing a dynamically prepared
> SQL
> > > > statement.
> > > >
> > > > Is there anything I can do to get around this?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Use the generic query designer and put in T-SQL statements in the
> > > designer.
> > > > > Here is some code as an example that I was just messing around with
> one
> > > day.
> > > > >
> > > > > declare @.SQL varchar(255)
> > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
> where
> > > xtype
> > > > > = ''U'' order by name'
> > > > > exec (@.SQL)
> > > > >
> > > > > It should give you an idea on how to do what you want to do.
> > > > >
> > > > > The above had a report parameter of Database.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > > message
> > > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > > I have been trying to determine if I can run a different stored
> > > procedure
> > > > > > base on a parameter value. I have a stored procedure on each of
> my
> > > SQL
> > > > > > Server machines that lists the SQL Server jobs. I would like to
> be
> > > able
> > > > > use
> > > > > > a parameter to list the SQL Server machines, select one of the SQL
> > > Server
> > > > > > machines and execute the appropriate stored procedure using a
> linked
> > > > > server
> > > > > > that I already have setup.
> > > > > >
> > > > > > How can this be accomplished?
> > > > > >
> > > > > > Thanks in advance!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great!
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in message
news:47DEE444-3ABA-46F9-B5DD-0CCBDE617260@.microsoft.com...
> Bruce,
> Thanks! I found it and everything is working great now. I really
> appreciate the help!
> Scott
> "Bruce L-C [MVP]" wrote:
> > The refresh fields button is on the dataset tab. You need to be showing
the
> > fields for your dataset. My understanding of the issue you are having is
> > that you do not have fields showing for the dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
message
> > news:37D47654-341C-439E-B3C9-D7DD16D36CDD@.microsoft.com...
> > > Bruce,
> > > > Try clicking on the refresh fields button (to the right of the ...)
it
> > looks
> > > > like the refresh button for IE.
> > > I am not getting a <Refresh> button on the Preview tab for the report.
> > >
> > > > One point, what you have there does not look to me like it will
work,
> > you
> > > > need to have another period. It looks to me like you will get this:
> > > >
> > > > exec someservermsdb..USP_ListSQLServerJobs
> > > >
> > > > the way you currently have it.
> > > I am adding the extra period that I need to the value that is returned
for
> > > the @.Server parameter.
> > >
> > > > Here is another trick you can do when working on something like
this.
> > > > Replace your exec to this:
> > > >
> > > > select @.SQL as SQLString
> > > >
> > > > Excute the query and you can now see what you have. Good way to
figure
> > out
> > > > what is happening.
> > > When I add the "select @.SQL as SQLString" and execute it through Query
> > > Analyzer, I get the SQL that I am expecting. When I execute the SQL,
it
> > > works perfectly.
> > >
> > > Do you have any other possibilities?
> > >
> > > Thanks in advance!
> > > Scott
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Try clicking on the refresh fields button (to the right of the ...)
it
> > looks
> > > > like the refresh button for IE.
> > > >
> > > > Just to check I took the one I sent you and changed it and clicked
on
> > the
> > > > field refresh and it worked.
> > > >
> > > > One point, what you have there does not look to me like it will
work,
> > you
> > > > need to have another period. It looks to me like you will get this:
> > > >
> > > > exec someservermsdb..USP_ListSQLServerJobs
> > > >
> > > > the way you currently have it.
> > > >
> > > > Here is another trick you can do when working on something like
this.
> > > > Replace your exec to this:
> > > >
> > > > select @.SQL as SQLString
> > > >
> > > > Excute the query and you can now see what you have. Good way to
figure
> > out
> > > > what is happening.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote in
> > message
> > > > news:8AE02A33-B33A-4D67-9344-82245378AD1F@.microsoft.com...
> > > > > Bruce,
> > > > > Thanks for the quick response! I ran into a problem. I put the
> > following
> > > > > in designer:
> > > > >
> > > > > DECLARE @.SQL varchar(255);
> > > > > SELECT @.SQL = 'exec ' + @.Server +
'msdb..USP_ListSQLServerJobs';
> > > > > EXEC (@.SQL)
> > > > >
> > > > > I have a report parameter setup called Server and it has two
values
> > listed
> > > > > to select. When I try to preview the report, I get compilation
errors
> > > > saying:
> > > > >
> > > > > The value expression for the textbox 'job_name' refers to the
field
> > > > > 'job_name'. Report item expressions can only refer to fields
within
> > the
> > > > > current data set scope or, if inside an aggregate, the specified
data
> > set
> > > > > scope.
> > > > >
> > > > > There is one error for each column that is used in the report.
> > > > >
> > > > > It's like Reporting Services does not recognize the data that the
> > stored
> > > > > procedure is returning because it is executing a dynamically
prepared
> > SQL
> > > > > statement.
> > > > >
> > > > > Is there anything I can do to get around this?
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > Use the generic query designer and put in T-SQL statements in
the
> > > > designer.
> > > > > > Here is some code as an example that I was just messing around
with
> > one
> > > > day.
> > > > > >
> > > > > > declare @.SQL varchar(255)
> > > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
> > where
> > > > xtype
> > > > > > = ''U'' order by name'
> > > > > > exec (@.SQL)
> > > > > >
> > > > > > It should give you an idea on how to do what you want to do.
> > > > > >
> > > > > > The above had a report parameter of Database.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > >
> > > > > > "Scott Lindsey" <ScottLindsey@.discussions.microsoft.com> wrote
in
> > > > message
> > > > > > news:F06CA48B-9827-4304-A0E4-F43C4AE49F49@.microsoft.com...
> > > > > > > I have been trying to determine if I can run a different
stored
> > > > procedure
> > > > > > > base on a parameter value. I have a stored procedure on each
of
> > my
> > > > SQL
> > > > > > > Server machines that lists the SQL Server jobs. I would like
to
> > be
> > > > able
> > > > > > use
> > > > > > > a parameter to list the SQL Server machines, select one of the
SQL
> > > > Server
> > > > > > > machines and execute the appropriate stored procedure using a
> > linked
> > > > > > server
> > > > > > > that I already have setup.
> > > > > > >
> > > > > > > How can this be accomplished?
> > > > > > >
> > > > > > > Thanks in advance!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Execute Big Dynamic SQL in Stored Procedure to Create View

I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure creates
a string of custom SQL statement and returns this string back to the
main stored procedure.

This SQL statements work fine on there own. The SQL returned from the
sub stored procedure are returned fine. The datatype of the variable
that this sql is stored in Varchar(I have tried using nvarchar also
same problem).

If I have more that 6 SQL statements concated then the main SQL gets
cut off. It doesnt matter in what sequence I create the main SQL.

Here is the Stored procedure

/**********************************************/
/*Main Stored Procedure */

/**********************************************/
CREATE PROC sp_generate_invoice1 @.prev_date NVarchar(1000) ,
@.prev_month NVarchar(32)
AS

DECLARE invoice_driver_cur CURSOR FOR
Select driversid From Invoice_drivers

Open invoice_driver_cur

Declare
@.C VARCHAR(8000),
@.L_args Varchar(8000),
@.@.sqlstmt Varchar(8000),
@.L_driverid Int,
@.L_rowcount Int

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 32768

Set @.L_rowcount = 0

-- Drop the previous View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'custom_invoice')
DROP VIEW custom_invoice

Fetch Next From invoice_driver_cur
Into @.L_driverid

-- Create the new View
Set @.L_args = N'Create View custom_invoice As'

--Select @.L_driverid

WHILE( @.@.FETCH_STATUS = 0)
BEGIN
Set @.L_rowcount = @.L_rowcount + 1
select @.L_driverid
If @.L_driverid = 2
Begin
Exec sp_invoice_driver2 @.prev_date, @.prev_month, @.@.sqlstmt Output

If @.L_rowcount > 1
Begin
Set @.C = @.L_args + ' Union ' + @.@.sqlstmt
End
Else
Begin
Set @.C = @.L_args + @.@.sqlstmt
End
End

If @.L_driverid = 3
Begin
Exec sp_invoice_driver3 @.prev_date, @.prev_month, @.@.sqlstmt Output