How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

how can i regenerate Database from my Backup file ? ?

hello All !!

i have taken mybackup(back file ) of database and i stored in folder , now i want to know

1)if i format my Hard Disk . and reinstall Sql server 2000 ?

2) if i delete that Database ?

how can i REGENERATE my database ?? again

thnk u all

In case you just have the backup somewhere safe / available after you have done / faced with 1) and/or 2), you can just restore the database from the .bak file.

SQl Server 2000 Backup And Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

How can I read type of all fields in joined query?

How can I read type of all fields in joined query?

Is there a stored procedure method to use for this purpose?

Thanks

What language needs to know this? c#/VB? T-Sql?

If c#/vb, and you are loading things into a DataTable, the meta-data is available in the DataTable object. I suspect it's availabe in DataReaders, etc.

|||

Hi David,

I need T-SQL stored procedure that gave me type of all fields in a joined query.

Thanks for your help

|||

create procedure get_stuff

as

begin

select col_1, col_2, col_3 from some_table
union
select col_a, col_b, col_c from some_other_table

end

That's the basic syntax. Check the manual for how to add parameters if needed.

How can I place a value from my dataset into the page header?

My report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.

Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.

Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?

many thanks

The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.

In future releases we are looking at adding field references to the other page sections.

|||

Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".

I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.

Have I misunderstood your response? thanks

|||

you need to give reference like these for the header textbox

=ReportItems!txtReportHeader.Value

where txtReportHeader is the text box in the body, which contains the value of the dataset field

|||

Thanks rakam, it worked like a charm....

I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.

How can I Passing Table type variable as Output Param

Hi,
How can I pass table type OUTPUT param to a stored procedure ?
regards,> How can I pass table type OUTPUT param to a stored procedure ?
> regards,
Table data type is not allowed for a SP parameter. If you need to use the
rowset returned in T-SQL code, you have two possibilties:
- change the procedure to a table-valued UDF
- save the output in a temp table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

how can i pass values from .mdb to crystal report?

how can i pass values from a text box to crystal report.the value is not stored in the database.please help me...Pass it as a parameter.

Create a parameter field in the report.

Then pass the value for the parameter from ur interface to report in the folg. way

Cr1.ParameterFields.GetItemByName("EnterOption").AddCurrentValue(ParamOption)

Here EnterOption is the name of the parameter created in Crystal Reports and ParamOption contains some value which u want to pass (any value)

Hope this may help|||Thanks ,
But its not working.I got an error message like this "Argument not optional". Pls explain the reason and do help me with sample code
Thanks
Hari|||Hope you are using VB code for creating interface
--------

'In the General Declarations , declare

Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report

'Assinging the report name
Set Report = Appl.OpenReport("report1.rpt")
Report.DiscardSavedData
'Passing the value of cmboption.Text value to the first parameter
'instead of passing by the name of the parameter, we can also use this convention
Report.ParameterFields(1).AddCurrentValue cmbOption.Text

'Assigning the report source to CRViewer Control and viewing the report
CRViewer1.ReportSource = Report
CRViewer1.ViewReport

--------

If your code produces error, send the code fragment|||There are 3 ways to deal with this/your problem or setting/passing arguments to a report :

Sample 1
---
g_ole_crx_report.DiscardSavedData()
g_ole_crx_report.ParameterFields.item[1].AddCurrentValue("100200300")

Sample 2
---
g_ole_crx_report.DiscardSavedData()
g_ole_crx_report.ParameterFields[1].AddCurrentValue("100200300")

Sample 3
---
g_ole_crx_report.DiscardSavedData()
g_ole_crx_report.ParameterFields.GetItemByName("@.AccountNumber").AddCurrentValue("100200300")

Sample 4
---
g_ole_crx_report.DiscardSavedData()
g_ole_crx_report.ParameterFields.item[3].AddCurrentValue(date("01/01/1999"))

Look at this sample. It needs conversion. You need to use convert function like date (I dont' know about VB) to convert date to date format.

Maybe this is what causing your problem.

Thanks

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 pass dataset to stored procedure

hi
i have customized dataset. i want to insert into database. how can i insert
on a single round trip. how can i pass dataset to stored procedure'examnotes (sdfsdf@.discussions.microsoft.com) writes:
> i have customized dataset. i want to insert into database. how can i
> insert on a single round trip. how can i pass dataset to stored
> procedure'
Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes but i have to first save the xml file. mine is web application so how ca
n
i serialize file.
is there any other alternative?
"Erland Sommarskog" wrote:

> examnotes (sdfsdf@.discussions.microsoft.com) writes:
> Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Hi,
What I think Erland was saying to create XML object ( e.g MSXML.DomDocument)
in your application and not file. This XML can be created from dataset, and
while calling SP pass the XML string as parameter ( Varchar lenght is the
constraint here).
Inside SP we can access the Node values from XML string using OpenXML.
Regards,
Vishal Khajuria
"sdfsdf" wrote:
> yes but i have to first save the xml file. mine is web application so how
can
> i serialize file.
> is there any other alternative?
>
> "Erland Sommarskog" wrote:
>|||Vishal Khajuria (Vishal Khajuria@.discussions.microsoft.com) writes:
> What I think Erland was saying to create XML object ( e.g
> MSXML.DomDocument) in your application and not file.
Yes. (Except that I don't really know how you build the XML string.
I only know that it's doable.)

> This XML can be created from dataset, and while calling SP pass the XML
> string as parameter ( Varchar lenght is the constraint here).
Pass the XML string as ntext, and length should not be a problem. (Unless
you exceed the 2GB limit for large objects.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How can I Pass an array into a Stored Procedure from Visual Basic?

Hi
How can I Pass an array into a MS SQL Server Stored Procedure from Visual
Basic?
Thanks
IanConvert it into a string
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>|||Define what you mean by an array and what you want to do with it in the
stored procedure for better answers.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>|||There are no array type in t-SQL. You will have to use other workarounds
like changing your VB array to a delimited string & pass as a single
parameter to the procedure, or build an XML string etc. As far as dealing
with such parameters & parsing them within a stored procedure, you can get
some ideas from: http://www.sommarskog.se/arrays-in-sql.html
--
Anith|||CREATE Procedure sp_test_array
@.IDList Varchar(8000)
AS
-- Notes: @.IDList must be a comma delimited list of id's
SET NOCOUNT ON
DECLARE @.Pos1 Int, @.Pos2 Int, @.id Int
-- Input value - bracket with commas
SET @.IDList = ',' + @.IDList + ','
SET @.Pos1 = 1
WHILE @.Pos1 < Len(@.IDList)
BEGIN
SET @.Pos2 = Charindex(',' , @.IDList , @.Pos1 + 1)
SET @.id = Convert(Int, Substring(@.IDList, @.Pos1 + 1, @.Pos2 - @.Pos1 - 1))
PRINT @.id
SET @.Pos1 = @.Pos2
END

How can I Pass an array into a Stored Procedure from Visual Basic?

Hi
How can I Pass an array into a MS SQL Server Stored Procedure from Visual
Basic?
Thanks
Ian
Convert it into a string
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||Define what you mean by an array and what you want to do with it in the
stored procedure for better answers.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||There are no array type in t-SQL. You will have to use other workarounds
like changing your VB array to a delimited string & pass as a single
parameter to the procedure, or build an XML string etc. As far as dealing
with such parameters & parsing them within a stored procedure, you can get
some ideas from: http://www.sommarskog.se/arrays-in-sql.html
Anith
|||CREATE Procedure sp_test_array
@.IDList Varchar(8000)
AS
-- Notes: @.IDList must be a comma delimited list of id's
SET NOCOUNT ON
DECLARE @.Pos1 Int, @.Pos2 Int, @.id Int
-- Input value - bracket with commas
SET @.IDList = ',' + @.IDList + ','
SET @.Pos1 = 1
WHILE @.Pos1 < Len(@.IDList)
BEGIN
SET @.Pos2 = Charindex(',' , @.IDList , @.Pos1 + 1)
SET @.id = Convert(Int, Substring(@.IDList, @.Pos1 + 1, @.Pos2 - @.Pos1 - 1))
PRINT @.id
SET @.Pos1 = @.Pos2
END

How can I pass a large XML document from a column of data type ntext to a stored procedure

I'm trying to "prepare" a large xml document that we've
stored (as a string) in our database as an ntext
document. I can't seem to find a way to pass the ntext
datatype to sp_xml_preparedocument. Ntext are not allowed
as local variables, and I'm not sure how chopping up the
ntext, with readtext can help me.

sp_xml_preparedocument @.idoc output, (ntext)

In SQL 2005, you can cast the ntext to nvarchar(max) or the XML datatype and go from there using either OPENXML or XQuery to manipulate the XML.

For SQL 2000, if you know the offset of the data in the buffer, you can use the READTEXT functions to access them. Otherwise you could write code outside of the database that selected the values and converted them using native or managed data access API's. But in general in SQL 2000 you cannot have a variable that is bigger than 4000 unicode characters. SQL 2005 removes this restriction.

How can I open an OS text file inside one Stored Procedure

HI,
I have one Operating System text file in some directory in UNIX environment . How can I open this file inside one Stored Procedure.
Thanks in advance.
DillipHello,

to open flat files in the OS use the package SYS.UTL_FILE. You can use the functions/procedures fopen, get_line and fclose to access the datas in the ff.

You get direct access to the file you must enter the path of the file in your initial parameter. Otherwise Oracle can not read or write the file.

The parameter is UTL_FILE_DIR.

Here is a short example:

declare
fptr utl_file.file_type;
buff varchar2(2048);
line_no number(10):=0;
loc_no integer;
begin
fptr:=utl_file.fopen('C:\Oracle\admin\PENT\udump', 'ORA00324.TRC','R');
utl_file.get_line(fptr,buff);
utl_file.fclose(fptr);
exception
when no_data_found then
utl_file.fclose(fptr);
dbms_output.put_line('Number of lines parsed ='||line_no);
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
raise_application_error(-20100,'file error');
when utl_file.invalid_mode then
dbms_output.put_line('invalid_mode');
raise_application_error(-20100,'file error');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid_filehandle');
raise_application_error(-20100,'file error');
when utl_file.invalid_operation then
dbms_output.put_line('invalid_operation');
raise_application_error(-20100,'file error');
when utl_file.read_error then
dbms_output.put_line('read_error');
raise_application_error(-20100,'file error');
when utl_file.write_error then
dbms_output.put_line('write_error');
raise_application_error(-20100,'file error');
when utl_file.internal_error then
dbms_output.put_line('internal_error');
raise_application_error(-20100,'file error');
when others then
dbms_output.put_line('un-handled');
raise_application_error(-20100,'file error');
end;

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

How can I make a SQL agent job to ignore warnings?

Hi! guys,

I have a SQL agent job fails because it gets 10 warnings when it runs
a stored procedure. These warnings are trivial and can be ignored. Can
I make it ignore these warnings and proceed? I think there is some
setting I can do to change the default behavour of an agent job
regarding warnings but I just don't know how to do it.

Any idea?

Thanks,

GaryGary (rooty_hill2002@.yahoo.com.au) writes:
> I have a SQL agent job fails because it gets 10 warnings when it runs
> a stored procedure. These warnings are trivial and can be ignored. Can
> I make it ignore these warnings and proceed? I think there is some
> setting I can do to change the default behavour of an agent job
> regarding warnings but I just don't know how to do it.

Exactly what warnings do you get?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95212D2EFFCFYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I have a SQL agent job fails because it gets 10 warnings when it runs
> > a stored procedure. These warnings are trivial and can be ignored. Can
> > I make it ignore these warnings and proceed? I think there is some
> > setting I can do to change the default behavour of an agent job
> > regarding warnings but I just don't know how to do it.
> Exactly what warnings do you get?

Erland,

Thanks for your reply.

I think it is warning 8153. Something like "NULL value has been
eliminated by aggregate function or by a SET statement" (not exact
words though). The job belongs to one of our vendors. Basically there
is a bug in one of the stored procedures it calls. One of SET
statements in the problematic stored procedure doesn't use isnull()
function to filter the result of a subquery properly.

Although I can modify the stored procedure to use isnull() or handle
the warnings within the code, I am not supposed to change their code
(of course I log a support call with them). The thing is I can't wait
for the response, in the meantime, I don't want to set ANSI_WARNINGS
OFF either.

That is the reason I raised my original question. It is also
interesting to me that whether we can do something on the
server/database level to refine the behavour of a agent job in
relation to error handling, or error tolerance rather.

Any idea?

Cheers,

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> I think it is warning 8153. Something like "NULL value has been
> eliminated by aggregate function or by a SET statement" (not exact
> words though). The job belongs to one of our vendors. Basically there
> is a bug in one of the stored procedures it calls. One of SET
> statements in the problematic stored procedure doesn't use isnull()
> function to filter the result of a subquery properly.
> Although I can modify the stored procedure to use isnull() or handle
> the warnings within the code, I am not supposed to change their code
> (of course I log a support call with them). The thing is I can't wait
> for the response, in the meantime, I don't want to set ANSI_WARNINGS
> OFF either.
> That is the reason I raised my original question. It is also
> interesting to me that whether we can do something on the
> server/database level to refine the behavour of a agent job in
> relation to error handling, or error tolerance rather.

I have to admit that I'm versed in Agent's intricacies, since I use it
only occasionally. But I ran a two-step job, where the first step included
a number of "SELECT avg(col) FROM tbl" where col is a column with many
NULL values, and the second step was just a PRINT statement. I ran this
job, and it Agent says that it was successful. Sure, there is some output
in job history, but that is not failure.

One possibility is that there is a real error in your step as well.

If you look up the job in Agent, double-click it and go to steps,
and then Edit, there is the Advanced tab. Here you can control what is
going to happen on Success and Failure. You can also direct output
to a file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9522921677BCCYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I think it is warning 8153. Something like "NULL value has been
> > eliminated by aggregate function or by a SET statement" (not exact
> > words though). The job belongs to one of our vendors. Basically there
> > is a bug in one of the stored procedures it calls. One of SET
> > statements in the problematic stored procedure doesn't use isnull()
> > function to filter the result of a subquery properly.
> > Although I can modify the stored procedure to use isnull() or handle
> > the warnings within the code, I am not supposed to change their code
> > (of course I log a support call with them). The thing is I can't wait
> > for the response, in the meantime, I don't want to set ANSI_WARNINGS
> > OFF either.
> > That is the reason I raised my original question. It is also
> > interesting to me that whether we can do something on the
> > server/database level to refine the behavour of a agent job in
> > relation to error handling, or error tolerance rather.
> I have to admit that I'm versed in Agent's intricacies, since I use it
> only occasionally. But I ran a two-step job, where the first step included
> a number of "SELECT avg(col) FROM tbl" where col is a column with many
> NULL values, and the second step was just a PRINT statement. I ran this
> job, and it Agent says that it was successful. Sure, there is some output
> in job history, but that is not failure.
> One possibility is that there is a real error in your step as well.
> If you look up the job in Agent, double-click it and go to steps,
> and then Edit, there is the Advanced tab. Here you can control what is
> going to happen on Success and Failure. You can also direct output
> to a file.

Erland, thanks again for your reply.

I understand that I can arrange an agent job to do certain things like
"going to next step", or "reporting failure", etc.. Maybe I have not
phrased my question preciously(forgive me for my broken English), let
me try again. What I want is to let the agent NOT REGARD warnings as
part of the criteria for job failure, which means warnings are
IGNORED. I don't have problem with how an agent should do once a
failure occurs and I am sure there is no other error in the stored
procedure since I debugged it throughly.

By the way, if you try the following block, you will likely get the
warning 8153 about usage of NULL value:

declare @.iTemp int
set @.iTemp=select max(colname) from tablename;

In which "tablename" is the table name for the test while "colname" is
the column name in the table with data type integer. If we have
max(colname)= NULL for some reason (although sounds stupid), the
warning occurs. Depends on the business rule involved, in my case
here, an isnull() function should be used to avoid this warning.

If you set up a job with similiar statement in it then your job
probably will fail after 10 warnings occur (ANSI_WARNINGS is ON). But
if you don't want the job to fail just because of this, then my
original question becomes relevant.

It is quite shameful that I still don't know whether we can do this
even after using MSSQL for about 7 years.

Cheers,

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> I understand that I can arrange an agent job to do certain things like
> "going to next step", or "reporting failure", etc.. Maybe I have not
> phrased my question preciously(forgive me for my broken English), let
> me try again. What I want is to let the agent NOT REGARD warnings as
> part of the criteria for job failure, which means warnings are
> IGNORED. I don't have problem with how an agent should do once a
> failure occurs and I am sure there is no other error in the stored
> procedure since I debugged it throughly.

I think I understand very well what you were after. The problem I have
I is that I am not able to recreate the situation you are describing. This
first step the first of my job:

select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders

And there are plenty of rows with NULL in that column.

> It is quite shameful that I still don't know whether we can do this
> even after using MSSQL for about 7 years.

Obviously, you can. Or at least I can. However, since I don't use
Agent that much I don't want to rule out that there is some setting
that controls this. But at the same time, I don't want to rule out
the possibility that you have a real error in your job, but which
is occluded by the warnings. Have you redirected the output of the
job to a file, and reviewed the output?

If you set up a job with the code above, does succeed or fail?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952382AF03399Yazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I understand that I can arrange an agent job to do certain things like
> > "going to next step", or "reporting failure", etc.. Maybe I have not
> > phrased my question preciously(forgive me for my broken English), let
> > me try again. What I want is to let the agent NOT REGARD warnings as
> > part of the criteria for job failure, which means warnings are
> > IGNORED. I don't have problem with how an agent should do once a
> > failure occurs and I am sure there is no other error in the stored
> > procedure since I debugged it throughly.
> I think I understand very well what you were after. The problem I have
> I is that I am not able to recreate the situation you are describing. This
> first step the first of my job:
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> And there are plenty of rows with NULL in that column.
> > It is quite shameful that I still don't know whether we can do this
> > even after using MSSQL for about 7 years.
> Obviously, you can. Or at least I can. However, since I don't use
> Agent that much I don't want to rule out that there is some setting
> that controls this. But at the same time, I don't want to rule out
> the possibility that you have a real error in your job, but which
> is occluded by the warnings. Have you redirected the output of the
> job to a file, and reviewed the output?
> If you set up a job with the code above, does succeed or fail?

Erland, thank you for your time.

By running your statement, I didn't get that warning message because
the result of this MAX function will never be NULL although we have a
lots of NULL in the table.

I wrote the following block to simulate the issue I am getting in the
job.

-- 1. create a table to test this warning message.
create table testwarnings
(
TxntIdintegernot null,
maxDepositAmountfloatnull
)

-- 2. populate the table with data.
insert into testwarnings values(1, null)
insert into testwarnings values(2, 10000.00)

-- 3. run the testing block which will give the message.
declare @.fTemp float
set @.fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors. Yes, I can't rule out
any possibility of any other warnings or errors. However before it
aborts it only get 10 times of warning as above. Only thing I am
interested in here is how to make the job to ignore warnings. If it
fails, let it fail on real errors, not warnings.

Cheers,

Gary|||On 11 Jul 2004 17:04:45 -0700, Gary wrote:

[snip]
> -- 3. run the testing block which will give the message.
> declare @.fTemp float
> set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> Txntid=1)
> I set up a job with only step 3 in it. The job fails as predicated,
> with warning message "Warning: Null value is eliminated by an
> aggregate or other SET operation."
> The job log clearly shows me that after 10 warning messages, it
> aborted without any other warnings or errors. Yes, I can't rule out
> any possibility of any other warnings or errors. However before it
> aborts it only get 10 times of warning as above. Only thing I am
> interested in here is how to make the job to ignore warnings. If it
> fails, let it fail on real errors, not warnings.
> Cheers,
> Gary
This may be an unattractive solution, but could you use the equivalent of

set @.fTemp = (
Select max(maxDepositAmount)
from testwarnings
where Txntid=1 and maxDepositAmount is not null)

Or even

set @.fTemp = (
Select max(coalesce(maxDepositAmount,0))
from testwarnings
where Txntid=1 and maxDepositAmount is not null)|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> By running your statement, I didn't get that warning message because
> the result of this MAX function will never be NULL although we have a
> lots of NULL in the table.

If you run the batch which I posted, you do get a whole lot of

Warning: Null value is eliminated by an aggregate or other SET operation.

because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
this presumes that you ANSI_WARNINGS ON.

> -- 3. run the testing block which will give the message.
> declare @.fTemp float
> set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> Txntid=1)
> I set up a job with only step 3 in it. The job fails as predicated,
> with warning message "Warning: Null value is eliminated by an
> aggregate or other SET operation."
> The job log clearly shows me that after 10 warning messages, it
> aborted without any other warnings or errors.

One thing is not clear to me. Do you run this batch 10 times in one
job step, and this step fails. Or does the job fail the tenth time
after nine succesful executions? Or does your job have 10 equal job
steps of which the tenth fall?

Sorry for asking stupid questions, but I created a job as you posted,
and it appears to run successfully each time. I included many copies
of the SET statement in the batch, and I have run the job multiple times.
I have not testet many job steps, yet, though.

Maybe it's time to check versions. What does SELECT @.@.version say at
your box? And, if you are running the job on a different server on
which you are runnin SQL Agent, which is the version on the SQL Agent
box?

Does the log include a "Job failed" message, or how do you see that the
job actually failed?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Also, post the output of

msdb..sp_help_job @.jobname = 'your-job-name-here'

Since the information is wide, please put the output in an attachment. (Text
file, please!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95253CEC93FYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > By running your statement, I didn't get that warning message because
> > the result of this MAX function will never be NULL although we have a
> > lots of NULL in the table.
> If you run the batch which I posted, you do get a whole lot of
> Warning: Null value is eliminated by an aggregate or other SET operation.
> because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
> this presumes that you ANSI_WARNINGS ON.
> > -- 3. run the testing block which will give the message.
> > declare @.fTemp float
> > set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> > Txntid=1)
> > I set up a job with only step 3 in it. The job fails as predicated,
> > with warning message "Warning: Null value is eliminated by an
> > aggregate or other SET operation."
> > The job log clearly shows me that after 10 warning messages, it
> > aborted without any other warnings or errors.
> One thing is not clear to me. Do you run this batch 10 times in one
> job step, and this step fails. Or does the job fail the tenth time
> after nine succesful executions? Or does your job have 10 equal job
> steps of which the tenth fall?
> Sorry for asking stupid questions, but I created a job as you posted,
> and it appears to run successfully each time. I included many copies
> of the SET statement in the batch, and I have run the job multiple times.
> I have not testet many job steps, yet, though.
> Maybe it's time to check versions. What does SELECT @.@.version say at
> your box? And, if you are running the job on a different server on
> which you are runnin SQL Agent, which is the version on the SQL Agent
> box?
> Does the log include a "Job failed" message, or how do you see that the
> job actually failed?

Erland,

Your input is appreciated.

I think I made a mistake. After double checking the vendor's job, I
found it actually calls one stored procedure(let's call it sp_a) which
calls another stored procedure(let's call it sp_b). The warning
message comes from the latter one (sp_b). I have been concentrated on
problematic one sp_b and totally forgot it is actually call by sp_a in
the job.

The job did fail after 10 warning messages (they arouse from sp_b). I
mean the job status is "failed" in the job list of Enterprise Manager.
I can't post the code here due to the understandable reason ( I know
it makes our discussion more difficult), but I can tell that sp_a
loops through a CURSOR and in every row in the CURSOR, it calls sp_b
which means sp_b is repeatedly called.

Also, Erland you are right. I also created a simple job which only
with
"declare @.fTemp float" followed by 11 copies of "set @.fTemp =( select
max(maxDepositAmount) from testwarnings where Txntid=1)". The job
finishes successfully each time!!! Now I am totally lost.

Result of "select @.@.version" is "Microsoft SQL Server 2000 -
8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
2195: Service Pack 4) " and I am doing the testing on the server
itself.

I don't know whether just because our job (yours and mine) is doing
direct statement execution , not nested stored procedure calls (as my
vendor's job does), the warning messages are treated differently by
the SQL Agent Service. I will simplify the vendor's job without
changing its characteristic then reproduce the problem so we can dig
deeper.

I will keep you posted.

Thanks again.

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> The job did fail after 10 warning messages (they arouse from sp_b). I
> mean the job status is "failed" in the job list of Enterprise Manager.
> I can't post the code here due to the understandable reason ( I know
> it makes our discussion more difficult), but I can tell that sp_a
> loops through a CURSOR and in every row in the CURSOR, it calls sp_b
> which means sp_b is repeatedly called.

While it is a different scenario, I fail to see how that could affect SQL
Server Agent.

I realize that you cannot post the original code, and anyway I would
have had any use for it, without tables and data.

What I have been asking for is a "repro" - a script, together with
instructions to run it - that demonstrates the problem. The reason I
keep asking for this, is that without this I have to resort to guessing.
If you were to open a case with Microsoft about this, they would ask
you for the same thing. The one difference, is that in that case you
could actually share the code with MS, since it is not a public forum.

> Result of "select @.@.version" is "Microsoft SQL Server 2000 -
> 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
> Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
> 2195: Service Pack 4) " and I am doing the testing on the server
> itself.

That is SQL Server SP3, the same version as I am using.

> I don't know whether just because our job (yours and mine) is doing
> direct statement execution , not nested stored procedure calls (as my
> vendor's job does), the warning messages are treated differently by
> the SQL Agent Service. I will simplify the vendor's job without
> changing its characteristic then reproduce the problem so we can dig
> deeper.

So this is one of the other points of asking people for a repro. If
they have to condense their problematic code to a manageable repro,
they might as a side-effect find the real problem themselves and also
learnt something on the way. I realize that since you are working with
third-party code that you may be interested in this. But if that vendor
has a support desk, there is one more option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
I have not tried this, but it will probably work for you.

There is a registry entry for this:

HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent \
Key: NonAlertableErrors

Try adding the error number to the list and see if it works.

Hope it helps,
Dan Wilson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
Thanks
Benny wrote:

> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Regards
Amish Shah
|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
ThanksBenny wrote:

> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspf...
log-files.html
Regards
Amish Shah|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How Can I Look Up Database Log

In either SQL 2000 or SQL 2005.
we can set both Data and Log file's position or initial size or
autogrowth.
I know data are stored in the Data File.
but how do I know what are stored in the Log file?
so
what are stored in the log file in SQL Server's database?
ThanksBenny wrote:
> In either SQL 2000 or SQL 2005.
> we can set both Data and Log file's position or initial size or
> autogrowth.
> I know data are stored in the Data File.
> but how do I know what are stored in the Log file?
> so
> what are stored in the log file in SQL Server's database?
> Thanks
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Regards
Amish Shah|||There are some ways out of the box that can reveal info:
select * from ::fn_dblog(null,null)
dbcc log(Pub1, type=4)
However to get hold of the actual TSQL statements you'll need a 3rd-party
tool eg Lumigent Log Explorer
(http://www.lumigent.com/products/le_sql.html). BTW it's true that you could
use transactional replication and sp_browsereplcmds as a means of achieving
this aim without buying a 3rd-party tool, but it's unlikely anyone would
want the unnecessary overhead to go down this route, and the info obtained
is more limited than using Log Explorer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how can i know execution stutus of a stored procedure

hi
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error messageIt depends on the tasks procedure is going to perform. One way is to use error handling mechanism provided by SQL server.|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR

This is not quite true. If the sproc has multiple steps (for example a select, an insert, another select, and a delete) you will need to check the value of @.@.ERROR after each DML statement, because @.@.ERROR only contains the return value from the last operation.

I encourage developers to save the value of @.@.ERROR into a variable, and then inspect the value of that variable as needed.

How can I JOIN the results of two Stored Procedures?

How can I JOIN the results of two stored procs?I have a two stored procs: sp_Users_GetByID and sp_UserInfo_GetByIDI want to create another stored proc that basically grabs the results from both of these, joins them, and returns that data. I just don't know how...

Has your question been answered here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84858

|||

Yes, they answered my question. Thanks!