Passing non-URL Friendly Parameters?

I have a parameter in my SQL report which is a system account number. This
account number often contains non-URL friendly characters (i.e. '<', '>',
';', ' ' , etc...). How can I create some type of substitition function to
work around this issue?
I reall need to pass the parameter through the URL. Thank you in advance.
- MaxWhat instead of using the GET method, you use the POST method to send your
parameters
Med Bouchenafa
"Max Tyack" <MaxTyack@.discussions.microsoft.com> a écrit dans le message de
news: D3AC72E4-77AE-4410-91E3-DC2DAB367842@.microsoft.com...
>I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max|||Do you need the link on a static page or from an other report?
Reporting Services should recognize escaped uri parameters. So instead of
'http://Hello World' you can use 'http://Hello%20World'. The same applies to
<, > and so on. The % values are the hex-ascii values, so you might need to
look up an ascii table and do a string replace ( like strUri.Replace(" ",
"%20") )
In .NET 2.0 you should look up for the Uri class and use EscapeUriString or
EscapeDataString.
I hope it's understandable, else you can try it at
http://www.greymana.net/examples/escape.html . For Non-URI friendly
parameters the Dest (param) might be more intersting. It's all in client-side
javascript.
"Max Tyack" wrote:
> I have a parameter in my SQL report which is a system account number. This
> account number often contains non-URL friendly characters (i.e. '<', '>',
> ';', ' ' , etc...). How can I create some type of substitition function to
> work around this issue?
> I reall need to pass the parameter through the URL. Thank you in advance.
> - Max

Passing a list of numbers to a stored procudure, having a size more than 8000 characters

Hi..

I m working on MS SQL Server 2000.

I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@.Items varchar(100) List of numbers

)

AS Begin

Declare @.SQL varchar(8000)

Set @.SQL =

'

Select Query......

Where products IN (' + @.items + ') '

'

Exec (@.SQL)

This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP?

In 2000, you can split the list over mulitple parms and then concat them together when you call the sporc like so:

create proc whatever

( @.Items1 varchar(8000),

@.Itmes2 varchar(8000),

...

)

Declare @.SqlStart Varchar(8000),

@.SqlEnd varchar(8000)

set @.SqlStart = '

Select bla

where products in ('

set SqlEnd = ')'

exec (@.sqlStart + @.Items1 + @.Items2 + ... + @.SqlEnd)

That should work although it tedious to split the items on the calling end. Where do the itmes come from, can;t you use a join or something to get at the records you need?

Regards GJ

|||

Change the datatype from Varchar to Text & execute the query directly dont use any variable.

Code Snippet

Create Procedure [Proc]

(@.Items text)

AS

Begin

Exec ('Select Query......

Where products IN (' + @.items + ')')

End

|||

Thanks GJ...

Works fine after splitting the varchar...

I was thinking about using joins as well but I guess its not possible the way my query is.

Anyways... Thanks for ur help both of Us

Pass unicode to stored procedures

Hi,
I have an **urgent** need to pass chinese characters into an SQL Server
Express table using a stored procedure. The MSDN documentation informs me
that everything will work 'automatically' if I use NVARCHAR parameters but
all I am getting is question marks. I've found old threads where other
people have the same problem but the only answer available is 'it should
just work'. Hopefully someone here (PLEASE) has more idea why the following
would not work (the result for me is that every unicode character is
converted into a question mark instead of having the correct chinese
charater).
PS The same code works fine for Jet (Access).
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
SET @.iID = 1
SET @.sName = '''
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;
--
Result: 1 '
--
NOTE: Prefixing the variable name with an "N" does fix the problem:
SET @.sName = ''' // this inserts '
SET @.sName = N''' // this inserts '
However, this solution is not workable because eventually the parameter will
be set by an existing C# application that passes a string. BTW The
'automatic' detection of string variables should make this unecessary right?
This is an urgent problem for me. A fast response would be greatly
appreciated.
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.auIs it an omen that this newsgroup filtered unicode from my original post?
The following lines that contain question marks should actually have Chinese
characters:
SET @.sName = '''
SET @.sName = ''' // this inserts question marks
SET @.sName = N''' // this inserts '
Other instances of question marks in the original post are "as intended":
Result: 1 '
Chinese characters: '
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.au|||I've reworked the code in the original post slightly to better illustrate my
point (and because I'm desperate for help!). If you copy code below the
dotted line into studio manager and replace the 'QQQ' with Chinese
characters then the following results are obtained:
1 ? // This is failure
2 QQQ // This is success
---
DROP TABLE MyTable;
GO
DROP PROCEDURE MyTable_Insert;
GO
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS
INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
/* THIS INSERTS QUESTION MARKS */
SET @.iID = 1
SET @.sName = 'QQQ'
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
/* THIS INSERTS UNICODE */
SET @.iID = 2
SET @.sName = N'QQQ
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;|||> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
As Jesse mentioned, strings in C# are Unicode. You'll get the desired
behavior as long as you parameterize your SQL statements with the correct
datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hamilton Colyer-Long" <hamilton@.NOjanusysSPAM.com.au> wrote in message
news:uD3nDInAIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have an **urgent** need to pass chinese characters into an SQL Server
> Express table using a stored procedure. The MSDN documentation informs me
> that everything will work 'automatically' if I use NVARCHAR parameters but
> all I am getting is question marks. I've found old threads where other
> people have the same problem but the only answer available is 'it should
> just work'. Hopefully someone here (PLEASE) has more idea why the
> following would not work (the result for me is that every unicode
> character is converted into a question mark instead of having the correct
> chinese charater).
> PS The same code works fine for Jet (Access).
> CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
> GO
> CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
> AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
> GO
> DECLARE @.RC INT
> DECLARE @.iID INT
> DECLARE @.sName NVARCHAR(50)
> SET @.iID = 1
> SET @.sName = '''
> EXECUTE @.RC = MyTable_Insert @.iID, @.sName
> GO
> SELECT * FROM MyTable;
> --
> Result: 1 '
> --
> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
> This is an urgent problem for me. A fast response would be greatly
> appreciated.
> Regards,
> Hamilton Colyer-Long
> Janusys
> www.janusys.com.au
>