How can I pass parameters into an RSS file for RS.EXE

I have a stored proc running:
Declare @.cmd nvarchar(255)
Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
"C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
exec master..xp_cmdshell @.cmd
But, the report the RSS file will render has 3 parameters the user specifies.
I know I can easily pass parameters to the command line from my stored proc,
but can I pass parameters from the command line into the RSS file like I can
do with a batch file?the syntax is
-v parameter1Name="parameterValue"
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>I have a stored proc running:
> Declare @.cmd nvarchar(255)
> Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
> "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> exec master..xp_cmdshell @.cmd
> But, the report the RSS file will render has 3 parameters the user
> specifies.
> I know I can easily pass parameters to the command line from my stored
> proc,
> but can I pass parameters from the command line into the RSS file like I
> can
> do with a batch file?|||You should not declare PID, RID and RptName in the script.
What happens if you run same command from command window?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> So then how do I use these parameters in the RSS file?
> I already have an array of parameters like this:
> ' Report Parameters
> Dim parameters(2) As ParameterValue
> parameters(0) = New ParameterValue()
> parameters(0).Name = "PID"
> parameters(0).Value = PID
> parameters(1) = New ParameterValue()
> parameters(1).Name = "RID"
> parameters(1).Value = RID
> parameters(2) = New ParameterValue()
> parameters(2).Name = "Name"
> parameters(2).Value = RptName
>
> PID, RID, and RptName are being passed to the RSS file as Global Variables
> from the command line using -v but when I run this, I get error BC30451
> (variable not declared).
> If I add:
> Dim PID as integer
> Dim RID as integer
> Dim RptName as string
> I get "Unhandled Exception. An error has occured during report
> processing".
>
> "Lev Semenets [MSFT]" wrote:
>> the syntax is
>> -v parameter1Name="parameterValue"
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >I have a stored proc running:
>> >
>> > Declare @.cmd nvarchar(255)
>> > Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
>> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >
>> > exec master..xp_cmdshell @.cmd
>> >
>> > But, the report the RSS file will render has 3 parameters the user
>> > specifies.
>> >
>> > I know I can easily pass parameters to the command line from my stored
>> > proc,
>> > but can I pass parameters from the command line into the RSS file like
>> > I
>> > can
>> > do with a batch file?
>>|||Here is what I run from the command line:
rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s http://ibm1pbs001/reportserver
-v PID=3 RID=1 RptName="NJIT Flex Plan Report"
Here is the content of FlexPlan.rss:
Public Sub Main()
Dim format as string = "PDF"
Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "PID"
parameters(0).Value = PID
parameters(1) = New ParameterValue()
parameters(1).Name = "RID"
parameters(1).Value = RID
parameters(2) = New ParameterValue()
parameters(2).Name = "Name"
parameters(2).Value = RptName
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
And the error I get when I execute the above command line code is:
The specified script failed to compile with the following errors:
C:\Documents and Settings\adam.baruh>
"c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe /main:MainModule
/utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web.Services.dll"
/R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
/out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
for Microsoft (R) .NET Framework version 1.1.4322.573
Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
Name 'RID' is not declared.
parameters(1).Value = RID
~~~
C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
Name 'RptName' is not declared.
parameters(2).Value = RptName
"Lev Semenets [MSFT]" wrote:
> You should not declare PID, RID and RptName in the script.
> What happens if you run same command from command window?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> > So then how do I use these parameters in the RSS file?
> >
> > I already have an array of parameters like this:
> >
> > ' Report Parameters
> > Dim parameters(2) As ParameterValue
> > parameters(0) = New ParameterValue()
> > parameters(0).Name = "PID"
> > parameters(0).Value = PID
> >
> > parameters(1) = New ParameterValue()
> > parameters(1).Name = "RID"
> > parameters(1).Value = RID
> >
> > parameters(2) = New ParameterValue()
> > parameters(2).Name = "Name"
> > parameters(2).Value = RptName
> >
> >
> > PID, RID, and RptName are being passed to the RSS file as Global Variables
> > from the command line using -v but when I run this, I get error BC30451
> > (variable not declared).
> >
> > If I add:
> > Dim PID as integer
> > Dim RID as integer
> > Dim RptName as string
> >
> > I get "Unhandled Exception. An error has occured during report
> > processing".
> >
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> the syntax is
> >> -v parameter1Name="parameterValue"
> >>
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
> >> >I have a stored proc running:
> >> >
> >> > Declare @.cmd nvarchar(255)
> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn>rs -i
> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> >> >
> >> > exec master..xp_cmdshell @.cmd
> >> >
> >> > But, the report the RSS file will render has 3 parameters the user
> >> > specifies.
> >> >
> >> > I know I can easily pass parameters to the command line from my stored
> >> > proc,
> >> > but can I pass parameters from the command line into the RSS file like
> >> > I
> >> > can
> >> > do with a batch file?
> >>
> >>
> >>
>
>|||Please use separate -v option for every parameter:
-v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
> Here is what I run from the command line:
> rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
> http://ibm1pbs001/reportserver
> -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
>
> Here is the content of FlexPlan.rss:
> Public Sub Main()
> Dim format as string = "PDF"
> Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
> Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
> ' Prepare Render arguments
> Dim historyID as string = Nothing
> Dim deviceInfo as string = Nothing
> Dim showHide as string = Nothing
> Dim results() as Byte
> Dim encoding as string
> Dim mimeType as string
> Dim warnings() AS Warning = Nothing
> Dim reportHistoryParameters() As ParameterValue = Nothing
> Dim streamIDs() as string = Nothing
> ' Report Parameters
> Dim parameters(2) As ParameterValue
> parameters(0) = New ParameterValue()
> parameters(0).Name = "PID"
> parameters(0).Value = PID
> parameters(1) = New ParameterValue()
> parameters(1).Name = "RID"
> parameters(1).Value = RID
> parameters(2) = New ParameterValue()
> parameters(2).Name = "Name"
> parameters(2).Value = RptName
> results = rs.Render(reportPath, format, _
> Nothing, Nothing, parameters, _
> Nothing, Nothing, encoding, mimeType, _
> reportHistoryParameters, warnings, streamIDs)
> ' Open a file stream and write out the report
> Dim stream As FileStream = File.OpenWrite(fileName)
> stream.Write(results, 0, results.Length)
> stream.Close()
> End Sub
> And the error I get when I execute the above command line code is:
> The specified script failed to compile with the following errors:
> C:\Documents and Settings\adam.baruh>
> "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
> /main:MainModule
> /utf8output /R:"System.dll" /R:"System.Xml.dll"
> /R:"System.Web.Services.dll"
> /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
> /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
> "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
> "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
> Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
> for Microsoft (R) .NET Framework version 1.1.4322.573
> Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
> C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
> Name 'RID' is not declared.
> parameters(1).Value = RID
> ~~~
> C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
> Name 'RptName' is not declared.
> parameters(2).Value = RptName
> "Lev Semenets [MSFT]" wrote:
>> You should not declare PID, RID and RptName in the script.
>> What happens if you run same command from command window?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
>> > So then how do I use these parameters in the RSS file?
>> >
>> > I already have an array of parameters like this:
>> >
>> > ' Report Parameters
>> > Dim parameters(2) As ParameterValue
>> > parameters(0) = New ParameterValue()
>> > parameters(0).Name = "PID"
>> > parameters(0).Value = PID
>> >
>> > parameters(1) = New ParameterValue()
>> > parameters(1).Name = "RID"
>> > parameters(1).Value = RID
>> >
>> > parameters(2) = New ParameterValue()
>> > parameters(2).Name = "Name"
>> > parameters(2).Value = RptName
>> >
>> >
>> > PID, RID, and RptName are being passed to the RSS file as Global
>> > Variables
>> > from the command line using -v but when I run this, I get error BC30451
>> > (variable not declared).
>> >
>> > If I add:
>> > Dim PID as integer
>> > Dim RID as integer
>> > Dim RptName as string
>> >
>> > I get "Unhandled Exception. An error has occured during report
>> > processing".
>> >
>> >
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> the syntax is
>> >> -v parameter1Name="parameterValue"
>> >>
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >> >I have a stored proc running:
>> >> >
>> >> > Declare @.cmd nvarchar(255)
>> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
>> >> > Server\80\Tools\Binn>rs -i
>> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >> >
>> >> > exec master..xp_cmdshell @.cmd
>> >> >
>> >> > But, the report the RSS file will render has 3 parameters the user
>> >> > specifies.
>> >> >
>> >> > I know I can easily pass parameters to the command line from my
>> >> > stored
>> >> > proc,
>> >> > but can I pass parameters from the command line into the RSS file
>> >> > like
>> >> > I
>> >> > can
>> >> > do with a batch file?
>> >>
>> >>
>> >>
>>|||Gotta love those easy answers. I wish the documentation was a bit more clear
on this utility though. Would love even more if there were samples without
having to install the samples on my Report Server. Thanks for the help
though!
"Lev Semenets [MSFT]" wrote:
> Please use separate -v option for every parameter:
> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
> > Here is what I run from the command line:
> >
> > rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
> > http://ibm1pbs001/reportserver
> > -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
> >
> >
> > Here is the content of FlexPlan.rss:
> >
> > Public Sub Main()
> >
> > Dim format as string = "PDF"
> > Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
> > Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan Report"
> >
> > ' Prepare Render arguments
> > Dim historyID as string = Nothing
> > Dim deviceInfo as string = Nothing
> > Dim showHide as string = Nothing
> > Dim results() as Byte
> > Dim encoding as string
> > Dim mimeType as string
> > Dim warnings() AS Warning = Nothing
> > Dim reportHistoryParameters() As ParameterValue = Nothing
> > Dim streamIDs() as string = Nothing
> >
> > ' Report Parameters
> > Dim parameters(2) As ParameterValue
> > parameters(0) = New ParameterValue()
> > parameters(0).Name = "PID"
> > parameters(0).Value = PID
> >
> > parameters(1) = New ParameterValue()
> > parameters(1).Name = "RID"
> > parameters(1).Value = RID
> >
> > parameters(2) = New ParameterValue()
> > parameters(2).Name = "Name"
> > parameters(2).Value = RptName
> >
> > results = rs.Render(reportPath, format, _
> > Nothing, Nothing, parameters, _
> > Nothing, Nothing, encoding, mimeType, _
> > reportHistoryParameters, warnings, streamIDs)
> >
> > ' Open a file stream and write out the report
> > Dim stream As FileStream = File.OpenWrite(fileName)
> > stream.Write(results, 0, results.Length)
> > stream.Close()
> > End Sub
> >
> > And the error I get when I execute the above command line code is:
> >
> > The specified script failed to compile with the following errors:
> > C:\Documents and Settings\adam.baruh>
> > "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
> > /main:MainModule
> > /utf8output /R:"System.dll" /R:"System.Xml.dll"
> > /R:"System.Web.Services.dll"
> > /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
> > /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
> >
> > Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
> > for Microsoft (R) .NET Framework version 1.1.4322.573
> > Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
> >
> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error BC30451:
> > Name 'RID' is not declared.
> >
> > parameters(1).Value = RID
> > ~~~
> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error BC30451:
> > Name 'RptName' is not declared.
> >
> > parameters(2).Value = RptName
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> You should not declare PID, RID and RptName in the script.
> >> What happens if you run same command from command window?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
> >> > So then how do I use these parameters in the RSS file?
> >> >
> >> > I already have an array of parameters like this:
> >> >
> >> > ' Report Parameters
> >> > Dim parameters(2) As ParameterValue
> >> > parameters(0) = New ParameterValue()
> >> > parameters(0).Name = "PID"
> >> > parameters(0).Value = PID
> >> >
> >> > parameters(1) = New ParameterValue()
> >> > parameters(1).Name = "RID"
> >> > parameters(1).Value = RID
> >> >
> >> > parameters(2) = New ParameterValue()
> >> > parameters(2).Name = "Name"
> >> > parameters(2).Value = RptName
> >> >
> >> >
> >> > PID, RID, and RptName are being passed to the RSS file as Global
> >> > Variables
> >> > from the command line using -v but when I run this, I get error BC30451
> >> > (variable not declared).
> >> >
> >> > If I add:
> >> > Dim PID as integer
> >> > Dim RID as integer
> >> > Dim RptName as string
> >> >
> >> > I get "Unhandled Exception. An error has occured during report
> >> > processing".
> >> >
> >> >
> >> >
> >> > "Lev Semenets [MSFT]" wrote:
> >> >
> >> >> the syntax is
> >> >> -v parameter1Name="parameterValue"
> >> >>
> >> >>
> >> >> --
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> >> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
> >> >> >I have a stored proc running:
> >> >> >
> >> >> > Declare @.cmd nvarchar(255)
> >> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
> >> >> > Server\80\Tools\Binn>rs -i
> >> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
> >> >> >
> >> >> > exec master..xp_cmdshell @.cmd
> >> >> >
> >> >> > But, the report the RSS file will render has 3 parameters the user
> >> >> > specifies.
> >> >> >
> >> >> > I know I can easily pass parameters to the command line from my
> >> >> > stored
> >> >> > proc,
> >> >> > but can I pass parameters from the command line into the RSS file
> >> >> > like
> >> >> > I
> >> >> > can
> >> >> > do with a batch file?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||That is documentation bug.
You may find some scripts here: http://blogs.msdn.com/bryanke/
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:936D34E0-3E68-4C38-871E-BA738D2147D9@.microsoft.com...
> Gotta love those easy answers. I wish the documentation was a bit more
> clear
> on this utility though. Would love even more if there were samples
> without
> having to install the samples on my Report Server. Thanks for the help
> though!
> "Lev Semenets [MSFT]" wrote:
>> Please use separate -v option for every parameter:
>> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> news:199317DB-4D61-4247-B719-3D982DA2169A@.microsoft.com...
>> > Here is what I run from the command line:
>> >
>> > rs -i "C:\pbs\gourmetdining\FlexPlan.rss" -s
>> > http://ibm1pbs001/reportserver
>> > -v PID=3 RID=1 RptName="NJIT Flex Plan Report"
>> >
>> >
>> > Here is the content of FlexPlan.rss:
>> >
>> > Public Sub Main()
>> >
>> > Dim format as string = "PDF"
>> > Dim fileName as String = "C:\PBS\GourmetDining\FlexPlanReport.pdf"
>> > Dim reportPath as String = "/Customers/Gourmet_Dining/Flex Plan
>> > Report"
>> >
>> > ' Prepare Render arguments
>> > Dim historyID as string = Nothing
>> > Dim deviceInfo as string = Nothing
>> > Dim showHide as string = Nothing
>> > Dim results() as Byte
>> > Dim encoding as string
>> > Dim mimeType as string
>> > Dim warnings() AS Warning = Nothing
>> > Dim reportHistoryParameters() As ParameterValue = Nothing
>> > Dim streamIDs() as string = Nothing
>> >
>> > ' Report Parameters
>> > Dim parameters(2) As ParameterValue
>> > parameters(0) = New ParameterValue()
>> > parameters(0).Name = "PID"
>> > parameters(0).Value = PID
>> >
>> > parameters(1) = New ParameterValue()
>> > parameters(1).Name = "RID"
>> > parameters(1).Value = RID
>> >
>> > parameters(2) = New ParameterValue()
>> > parameters(2).Name = "Name"
>> > parameters(2).Value = RptName
>> >
>> > results = rs.Render(reportPath, format, _
>> > Nothing, Nothing, parameters, _
>> > Nothing, Nothing, encoding, mimeType, _
>> > reportHistoryParameters, warnings, streamIDs)
>> >
>> > ' Open a file stream and write out the report
>> > Dim stream As FileStream = File.OpenWrite(fileName)
>> > stream.Write(results, 0, results.Length)
>> > stream.Close()
>> > End Sub
>> >
>> > And the error I get when I execute the above command line code is:
>> >
>> > The specified script failed to compile with the following errors:
>> > C:\Documents and Settings\adam.baruh>
>> > "c:\winnt\microsoft.net\framework\v1.1.4322\vbc.exe" /t:exe
>> > /main:MainModule
>> > /utf8output /R:"System.dll" /R:"System.Xml.dll"
>> > /R:"System.Web.Services.dll"
>> > /R:"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\RS.exe"
>> > /out:"C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.exe" /debug-
>> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.0.vb"
>> > "C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb"
>> >
>> > Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
>> > for Microsoft (R) .NET Framework version 1.1.4322.573
>> > Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
>> >
>> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(40) : error
>> > BC30451:
>> > Name 'RID' is not declared.
>> >
>> > parameters(1).Value = RID
>> > ~~~
>> > C:\DOCUME~1\ADAM~1.BAR\LOCALS~1\Temp\1\kzf4n1yz.1.vb(44) : error
>> > BC30451:
>> > Name 'RptName' is not declared.
>> >
>> > parameters(2).Value = RptName
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> You should not declare PID, RID and RptName in the script.
>> >> What happens if you run same command from command window?
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> news:98ECCF1E-42B9-4C63-895B-900210FD82CB@.microsoft.com...
>> >> > So then how do I use these parameters in the RSS file?
>> >> >
>> >> > I already have an array of parameters like this:
>> >> >
>> >> > ' Report Parameters
>> >> > Dim parameters(2) As ParameterValue
>> >> > parameters(0) = New ParameterValue()
>> >> > parameters(0).Name = "PID"
>> >> > parameters(0).Value = PID
>> >> >
>> >> > parameters(1) = New ParameterValue()
>> >> > parameters(1).Name = "RID"
>> >> > parameters(1).Value = RID
>> >> >
>> >> > parameters(2) = New ParameterValue()
>> >> > parameters(2).Name = "Name"
>> >> > parameters(2).Value = RptName
>> >> >
>> >> >
>> >> > PID, RID, and RptName are being passed to the RSS file as Global
>> >> > Variables
>> >> > from the command line using -v but when I run this, I get error
>> >> > BC30451
>> >> > (variable not declared).
>> >> >
>> >> > If I add:
>> >> > Dim PID as integer
>> >> > Dim RID as integer
>> >> > Dim RptName as string
>> >> >
>> >> > I get "Unhandled Exception. An error has occured during report
>> >> > processing".
>> >> >
>> >> >
>> >> >
>> >> > "Lev Semenets [MSFT]" wrote:
>> >> >
>> >> >> the syntax is
>> >> >> -v parameter1Name="parameterValue"
>> >> >>
>> >> >>
>> >> >> --
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
>> >> >> news:807422F1-C259-4D8D-9035-8492E6EA9BDD@.microsoft.com...
>> >> >> >I have a stored proc running:
>> >> >> >
>> >> >> > Declare @.cmd nvarchar(255)
>> >> >> > Set @.cmd = 'C:\Program Files\Microsoft SQL
>> >> >> > Server\80\Tools\Binn>rs -i
>> >> >> > "C:\CmdLineReportRendering.rss" -s http://localhost/reportserver
>> >> >> >
>> >> >> > exec master..xp_cmdshell @.cmd
>> >> >> >
>> >> >> > But, the report the RSS file will render has 3 parameters the
>> >> >> > user
>> >> >> > specifies.
>> >> >> >
>> >> >> > I know I can easily pass parameters to the command line from my
>> >> >> > stored
>> >> >> > proc,
>> >> >> > but can I pass parameters from the command line into the RSS file
>> >> >> > like
>> >> >> > I
>> >> >> > can
>> >> >> > do with a batch file?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||In the documentation it says:
For example, -v a="b" c="d" results in a variable named a with a value of
"b" and a variable c with a value of "d".
Not -v a="b" -v c="d" >> which by the way does not work!
Somebody please change the documentation!!!
"Lev Semenets [MSFT]" wrote:
> Please use separate -v option for every parameter:
> -v PID=1 -v RID=1 -v RptName="NJIT Flex Plan Report"
> --

How can i output XQuery's key words such like "<,>" when using XQuery in SQL Se

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

sql

How can i output XQuery's key words such like "<,>" when using XQuery in SQ

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

How can I insert the results from an EXEC command

Declare @.pTable varchar(30)
DECLARE @.TotRec integer
select @.pTable = 'Salaires'
SELECT @.TotRec EXEC ('SELECT Count(*) FROM ' + @.pTable)
GO
Print @.TotRec
How can I insert the results from an EXEC command?[QUOTE][SIZE=1]Originally posted by ericjean
Declare @.pTable varchar(30)
DECLARE @.TotRec integer
select @.pTable = 'Salaires'
SELECT @.TotRec = EXEC ('SELECT Count(*) FROM ' + @.pTable)
GO
Print @.TotRec

Like that is not correct!|||Unless you are just presenting a simplified example, there is no need to use dynamic sql to get rowcounts. You can look them up directly in the system tables (sysindexes where indid in (0, 1)):
-------------
set @.TotRec =
(select sysindexes.Rowcnt
from sysobjects
inner join sysindexes on sysobjects.id = sysindexes.id
where sysobjects.name = @.pTable and sysobjects.xtype = 'U' and sysindexes.indid in (0, 1)

Print @.TotRec
-------------

blindman|||Good idea!

Thank's|||I need to do like this

Declare @.pTable varchar(30)
DECLARE @.tmp integer
select @.pTable = 'Salaires'
SELECT @.tmp = exec('SELECT count(*) FROM ' + @.pTable + ' WHERE ID = 2)|||Declare @.pTable varchar(30)
DECLARE @.tmp integer
select @.pTable = 'Salaires'
SET @.tmp = ('SELECT count(*) FROM ' + @.pTable + ' WHERE ID = 2)

@.tmp now has the count of that table

how can I have this package execute in loop....I want to change every lines that are not c

declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')Without a problem description, including table DDL and sample data, a
meaningful response is not likely.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:eZTmg.15162$XT2.164998@.wagner.videotron.net...
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>

How can I get the @x?

I want to get the @.x's value:
declare
@.sql varchar(8000),
@.p1 varchar(100),
@.p2 varchar(100),
@.x int
select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
set @.sql='
select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
EXEC (@.sql)
print(@.x)
But the error message:
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@.x".
There are some conditions:must use exec;do not use temp table or procodure.
Any help would be appreciated.luyan wrote:
> I want to get the @.x's value:
> declare
> @.sql varchar(8000),
> @.p1 varchar(100),
> @.p2 varchar(100),
> @.x int
> select @.p1 = 'lastwaittype',@.p2 = 'SLEEP_TASK'
> set @.sql='
> select @.x=count(*) from test1 where '+@.p1+'='+''''+@.p2+''''
> EXEC (@.sql)
> print(@.x)
> But the error message:
> Msg 137, Level 15, State 1, Line 2
> Must declare the scalar variable "@.x".
> There are some conditions:must use exec;do not use temp table or
> procodure. Any help would be appreciated.
Use sp_executesql instead. For example:
Declare @.Var1 int
Declare @.sql nvarchar(1000)
Declare @.x int
Set @.Var1 = 10
Set @.sql = N'Select @.x = count(*) From dbo.sysobjects Where id > @.Var1'
Exec sp_executesql @.sql, N'@.x int OUTPUT, @.Var1 int', @.x OUTPUT, @.Var1
Select @.x
David Gugick
Quest Software
www.quest.com