How to validate the parameter values?

Hi,

Is there any way to validate the input paratemers for the report? For example: I want to restrict the value to be less than 100 in one parameter. How to achieve this?

Thnx in advance.

There is no way to validate and inform the user if the parameter value is not in a given range. Though you can restrict the values that user can select for the parameter by providing pre-defined available values to the parameter, either from query or non-queried.

Shyam

|||

Pre-defining the values will be helpful in case of numeric or string values. What if i want to compare date values. For example:

given date is less than or equal to today's date... I think, this is also not possible!!!

The RS Engine validates the date based on the format only. The validations are not possible.

Some alternative should be thought-of by microsoft... as validations are very necessary.

how to use non-basic ascii chars in stored proc paramter

hi all,

I have a stored procedure with a input parameter ( xml block).

sp: spGetAddr @.xmlText

the style of parameter @.xmlText looks like

<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>

If I use non-basic ASCII character, just like 'á' in the parameter, the execution fails and I get 'The error description is 'An invalid character was found in text content.''

But this kind of special characters should be allowed in the name column of the database table.

If I use the htlm code '&#xE1" to replace 'á' in the parameter, then the execution of the stored procedure is fine. but how can I change all of the possible sepcial characters?

If somebody knows how to handle this issue, please help me. I'm pre-appreciated for all the idea and suggestion!

Thanks,

Jone

When you pass the parameter to the stored procedure, make sure to use an nvarchar string. For example:

N'<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>'

Regards,

Galex Yen

how to use non-basic AscII character in Stored procedure parameter

hi all,

I have a stored procedure with a input parameter ( xml block).

sp: spGetAddr @.xmlText

the style of parameter @.xmlText looks like

<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>

If I use non-basic ASCII character, just like 'á' in the parameter, the execution fails and I get 'The error description is 'An invalid character was found in text content.''

But this kind of special characters should be allowed in the name column of the database table.

If I use the htlm code '&#xE1" to replace 'á' in the parameter, then the execution of the stored procedure is fine. but how can I change all of the possible sepcial characters?

If somebody knows how to handle this issue, please help me. I'm pre-appreciated for all the idea and suggestion!

Thanks,

Jone

What happens when you preface the string with the unicode identifier [ N ]?

<Param RestricBy="UserName IN (N'Mayá', 'Bob')"/>

|||

Thanks for your reply.

after preface 'N', I still got the same error.

Any idea?

Thanks,

Jone

|||

Perhaps this will prove useful for you:

http://blogs.msdn.com/mrys/archive/2005/09/06/461682.aspx

how to use input column inside script component

Hi,

I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.

How do i use this inside the script?

cherriesh

dear friend,

Look at this example:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
...

Dim strLogin As String

strLogin = Row.LoginName

...

End Sub

LoginName is the input column!

Helped?

regards!

|||thanks a lot!!!

How to use content of file for xml input?

Hi Everyone,
actually i'm starting to work with OpenXML on SQL Server 2k. At the
examples i found everywhere a variable was defined and filled
hardcode. Something like this:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc = 'This is the content of my file'
EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
But i don't have any hardcoded text. I have a lot of xml files, which
liked to be prepared. They size vary from 1 MB to 100 MB and i don't
know how to pass them into the stored procedure.
So can anyone tell me, how a external xml file can be prepared to use
it with sp_xml_preparedocument' What functions are needed to read the
content of a given file and access it for a stored procedure'
Best regards,
OliverYou write a stored procedure with an ntext typed parameter and you read the
XML on the client and pass it to the stored procedure as a parameter. You
will have to change encoding to UTF-16 on the midtier before you pass it in
though.
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167740889.192836.302290@.48g2000cwx.googlegroups.com...
> Hi Everyone,
> actually i'm starting to work with OpenXML on SQL Server 2k. At the
> examples i found everywhere a variable was defined and filled
> hardcode. Something like this:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc = 'This is the content of my file'
> EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
> But i don't have any hardcoded text. I have a lot of xml files, which
> liked to be prepared. They size vary from 1 MB to 100 MB and i don't
> know how to pass them into the stored procedure.
> So can anyone tell me, how a external xml file can be prepared to use
> it with sp_xml_preparedocument' What functions are needed to read the
> content of a given file and access it for a stored procedure'
> Best regards,
> Oliver
>|||Hi Michael,
ok, but doesn't there any method exists to do it directly on the SQL
server' I don't need a real client. I just have to read a XML file
into a table. Couldn't it be done directly on the server?
Best regards,
Oliver
Michael Rys [MSFT] schrieb:
> You write a stored procedure with an ntext typed parameter and you read th
e
> XML on the client and pass it to the stored procedure as a parameter. You
> will have to change encoding to UTF-16 on the midtier before you pass it i
n
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegroups.com...|||You could write your own extended stored procedure or use the sp_OA stored
procs. Neither I recommend. SQL Server 2005 can do it easily with
OpenRowset(BULK).
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167808359.527451.19810@.a3g2000cwd.googlegroups.com...
> Hi Michael,
> ok, but doesn't there any method exists to do it directly on the SQL
> server' I don't need a real client. I just have to read a XML file
> into a table. Couldn't it be done directly on the server?
> Best regards,
> Oliver
> Michael Rys [MSFT] schrieb:
>
>|||Hi Michael,
thanks for the answer but i found a solution.
I used the bulk load mechanism. If you know what you need you know what
you have to search for. ;-) So after i read something about bulk load
and a little search i found the following website:
http://msdn2.microsoft.com/en-us/library/aa225754.aspx
With these mechasim i'm able to read the file into our SQL Server.
So thanks for your interest in my problem and best regards,
Oliver
Michael Rys [MSFT] schrieb:
> You could write your own extended stored procedure or use the sp_OA stored
> procs. Neither I recommend. SQL Server 2005 can do it easily with
> OpenRowset(BULK).
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167808359.527451.19810@.a3g2000cwd.googlegroups.com...

How to use content of file for xml input?

Hi Everyone,
actually i'm starting to work with OpenXML on SQL Server 2k. At the
examples i found everywhere a variable was defined and filled
hardcode. Something like this:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc = 'This is the content of my file'
EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
But i don't have any hardcoded text. I have a lot of xml files, which
liked to be prepared. They size vary from 1 MB to 100 MB and i don't
know how to pass them into the stored procedure.
So can anyone tell me, how a external xml file can be prepared to use
it with sp_xml_preparedocument? What functions are needed to read the
content of a given file and access it for a stored procedure?
Best regards,
Oliver
You write a stored procedure with an ntext typed parameter and you read the
XML on the client and pass it to the stored procedure as a parameter. You
will have to change encoding to UTF-16 on the midtier before you pass it in
though.
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
> Hi Everyone,
> actually i'm starting to work with OpenXML on SQL Server 2k. At the
> examples i found everywhere a variable was defined and filled
> hardcode. Something like this:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc = 'This is the content of my file'
> EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
> But i don't have any hardcoded text. I have a lot of xml files, which
> liked to be prepared. They size vary from 1 MB to 100 MB and i don't
> know how to pass them into the stored procedure.
> So can anyone tell me, how a external xml file can be prepared to use
> it with sp_xml_preparedocument? What functions are needed to read the
> content of a given file and access it for a stored procedure?
> Best regards,
> Oliver
>
|||Hi Michael,
ok, but doesn't there any method exists to do it directly on the SQL
server? I don't need a real client. I just have to read a XML file
into a table. Couldn't it be done directly on the server?
Best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You write a stored procedure with an ntext typed parameter and you read the
> XML on the client and pass it to the stored procedure as a parameter. You
> will have to change encoding to UTF-16 on the midtier before you pass it in
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
|||You could write your own extended stored procedure or use the sp_OA stored
procs. Neither I recommend. SQL Server 2005 can do it easily with
OpenRowset(BULK).
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...
> Hi Michael,
> ok, but doesn't there any method exists to do it directly on the SQL
> server? I don't need a real client. I just have to read a XML file
> into a table. Couldn't it be done directly on the server?
> Best regards,
> Oliver
> Michael Rys [MSFT] schrieb:
>
|||Hi Michael,
thanks for the answer but i found a solution.
I used the bulk load mechanism. If you know what you need you know what
you have to search for. ;-) So after i read something about bulk load
and a little search i found the following website:
http://msdn2.microsoft.com/en-us/library/aa225754.aspx
With these mechasim i'm able to read the file into our SQL Server.
So thanks for your interest in my problem and best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You could write your own extended stored procedure or use the sp_OA stored
> procs. Neither I recommend. SQL Server 2005 can do it easily with
> OpenRowset(BULK).
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...

How to use a Variable from the For Loop Container as a input parameter to a SP

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

MA2005 wrote:

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

The ForEach loop allows you to store the currently iterated values from the collection that you are iterating over in variables. You can then use those variables in any way that you would normally use variables in a parameterised Execute SQL Task.

I haven't gone into much detail here because I don't want to talk about stuff you already understand. Tell me what isn't clear about what I have said and I'll be glad to fill you in.

-Jamie