passing multiple values to a paramter

Hi,

I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

Nevermind, I figured it out, had to pass

Parameters! <ParameterName> .Label

as the parameter where I was linking the report. This passes the whole array with all the chosen values in it.sql

Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting som
e
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connectio
n
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:

>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certai
n
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting so
me
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connecti
on
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:

> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
>
>

Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting some
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connection
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:
>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certain
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting some
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connection
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:
> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
> >Is there a means to pass information to SQL server (on a single connection)
> >that is accessible from T-SQL or triggers. My scenario is that when certain
> >tables are updated/inserted/deleted, I am making entries in a "log" table,
> >via triggers, that contains the table name and the action performed on it.
> >What I also need to insert into the logging table is an integer denoting some
> >additional information about the update/insert/delete which needs to come
> >from the client. Also, this information needs to be scoped to the connection
> >so that it is not accesible from other connections.
>

Passing Data From VB to SSRS using LocalReport

With Crystal reports, it was easy to pass a single piece of data from VB to a report in a formula field. How do you implement this functionality in SSRS 2005 (I am using VS 2005)?

Seems like this should be commonplace, but I can't seem to find any info on it.

I am using a LocalReport, so a URL is not an option.

Thanks.

For anyone else having this problem:

I have figured it out - you have to create a parameter using the Report - Report Properties menu. Then, you link the parameter to a TextBox on the report. Finally, you can pass data from a client using the LocalReport.SetParameters method.

Never did find any documentation on it. Very obscure.

Passing Data From VB to SSRS using LocalReport

With Crystal reports, it was easy to pass a single piece of data from VB to a report in a formula field. How do you implement this functionality in SSRS 2005 (I am using VS 2005)?

Seems like this should be commonplace, but I can't seem to find any info on it.

I am using a LocalReport, so a URL is not an option.

Thanks.

For anyone else having this problem:

I have figured it out - you have to create a parameter using the Report - Report Properties menu. Then, you link the parameter to a TextBox on the report. Finally, you can pass data from a client using the LocalReport.SetParameters method.

Never did find any documentation on it. Very obscure.

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing a variable to multiple rows in an INSERT

Through a stored procedure I want to insert twelve rows into a table with
each value dependent on a single date value passed to the procedure. I've
tried various combinations of SET and GO, but always lose the variable
definition. What would be the structure of the statements for inserting the
rows?Why don't you post the procedure and let us look at that? Otherwise,
my answer is 42.
Stu|||You can't have GO inside a stored procedure. GO ends the procedure. Can you
be more specific what
you want to achieve? Something like:
CREATE PROC p @.dt datetime
AS
INSERT INTO tbl (c1, c2) VALUES(1, @.dt)
INSERT INTO tbl (c1, c2) VALUES(2, @.dt)
INSERT INTO tbl (c1, c2) VALUES(3, @.dt)
INSERT INTO tbl (c1, c2) VALUES(4, @.dt)
INSERT INTO tbl (c1, c2) VALUES(5, @.dt)
INSERT INTO tbl (c1, c2) VALUES(6, @.dt)
INSERT INTO tbl (c1, c2) VALUES(7, @.dt)
INSERT INTO tbl (c1, c2) VALUES(8, @.dt)
INSERT INTO tbl (c1, c2) VALUES(9, @.dt)
GO
I know, probably not, but as you probably understand it s pretty hard to gue
ss from your desciption.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:F2F3AFE8-9ECD-4E11-950F-5619BC1B9A47@.microsoft.com...
> Through a stored procedure I want to insert twelve rows into a table with
> each value dependent on a single date value passed to the procedure. I've
> tried various combinations of SET and GO, but always lose the variable
> definition. What would be the structure of the statements for inserting th
e
> rows?|||Thanks. This is what I've got:
CREATE TABLE tblMaturityDate
(Duration varchar(15),
MaturityDate smalldatetime)
INSERT INTO tblMaturityDate
VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 1
THEN DATEADD(mm,3,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 7
THEN DATEADD(mm,3,GETDATE()) + 2
ELSE DATEADD(mm,3,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 1
THEN DATEADD(mm,6,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 7
THEN DATEADD(mm,6,GETDATE()) + 2
ELSE DATEADD(mm,6,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 1
THEN DATEADD(mm,9,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 7
THEN DATEADD(mm,9,GETDATE()) + 2
ELSE DATEADD(mm,9,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 1
THEN DATEADD(mm,12,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 7
THEN DATEADD(mm,12,GETDATE()) + 2
ELSE DATEADD(mm,12,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 1
THEN DATEADD(mm,15,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 7
THEN DATEADD(mm,15,GETDATE()) + 2
ELSE DATEADD(mm,15,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 1
THEN DATEADD(mm,18,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 7
THEN DATEADD(mm,18,GETDATE()) + 2
ELSE DATEADD(mm,18,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
INSERT INTO tblMaturityDate
VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 1
THEN DATEADD(yy,2,GETDATE()) + 1
WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 7
THEN DATEADD(yy,2,GETDATE()) + 2
ELSE DATEADD(yy,2,GETDATE())
END)
"Stu" wrote:

> Why don't you post the procedure and let us look at that? Otherwise,
> my answer is 42.
> Stu
>|||That looks very simple. I'll try it and let you know.
I've posted what I have so far.
Thank you.
"Tibor Karaszi" wrote:

> You can't have GO inside a stored procedure. GO ends the procedure. Can yo
u be more specific what
> you want to achieve? Something like:
> CREATE PROC p @.dt datetime
> AS
> INSERT INTO tbl (c1, c2) VALUES(1, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(2, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(3, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(4, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(5, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(6, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(7, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(8, @.dt)
> INSERT INTO tbl (c1, c2) VALUES(9, @.dt)
> GO
> I know, probably not, but as you probably understand it s pretty hard to g
uess from your desciption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:F2F3AFE8-9ECD-4E11-950F-5619BC1B9A47@.microsoft.com...
>|||That code executed without errors on my machine...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TLD" <TLD@.discussions.microsoft.com> wrote in message
news:5E81EF19-7534-4E02-B96A-C59D8DA5EE1C@.microsoft.com...
> Thanks. This is what I've got:
> CREATE TABLE tblMaturityDate
> (Duration varchar(15),
> MaturityDate smalldatetime)
> INSERT INTO tblMaturityDate
> VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 1
> THEN DATEADD(mm,3,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,3,GETDATE())) = 7
> THEN DATEADD(mm,3,GETDATE()) + 2
> ELSE DATEADD(mm,3,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 1
> THEN DATEADD(mm,6,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,6,GETDATE())) = 7
> THEN DATEADD(mm,6,GETDATE()) + 2
> ELSE DATEADD(mm,6,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 1
> THEN DATEADD(mm,9,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,9,GETDATE())) = 7
> THEN DATEADD(mm,9,GETDATE()) + 2
> ELSE DATEADD(mm,9,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 1
> THEN DATEADD(mm,12,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,12,GETDATE())) = 7
> THEN DATEADD(mm,12,GETDATE()) + 2
> ELSE DATEADD(mm,12,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 1
> THEN DATEADD(mm,15,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,15,GETDATE())) = 7
> THEN DATEADD(mm,15,GETDATE()) + 2
> ELSE DATEADD(mm,15,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 1
> THEN DATEADD(mm,18,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(mm,18,GETDATE())) = 7
> THEN DATEADD(mm,18,GETDATE()) + 2
> ELSE DATEADD(mm,18,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,2,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,3,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
> INSERT INTO tblMaturityDate
> VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 1
> THEN DATEADD(yy,2,GETDATE()) + 1
> WHEN DATEPART(dw,DATEADD(yy,5,GETDATE())) = 7
> THEN DATEADD(yy,2,GETDATE()) + 2
> ELSE DATEADD(yy,2,GETDATE())
> END)
>
> "Stu" wrote:
>|||I'm sorry; I'm a bit . Where's the variable that you're trying
to declare, but always lose? Is it to replace the GETDATE() function?
Stu|||Yes, that's right. It finds the date from a field on a form and passes it to
the procedure.
"Stu" wrote:

> I'm sorry; I'm a bit . Where's the variable that you're trying
> to declare, but always lose? Is it to replace the GETDATE() function?
> Stu
>|||It worked in Query Analyzer, but it didn't work when created as a procedure.
I replaced the GETDATE() with a variable, but couldn't pass the variable dow
n
all of the rows.
"Tibor Karaszi" wrote:

> That code executed without errors on my machine...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TLD" <TLD@.discussions.microsoft.com> wrote in message
> news:5E81EF19-7534-4E02-B96A-C59D8DA5EE1C@.microsoft.com...
>|||I've had some success, but not complete. The following, when applied to a
combo box, returns "The stored procedure completed successfully, but did not
return any records." Am I close?
CREATE PROCEDURE spp_MaturityDateComboBox
(@.DealDate smalldatetime)
AS
DELETE FROM tblMaturityDate
INSERT INTO tblMaturityDate
VALUES ('3 mo', CASE WHEN DATEPART(dw,DATEADD(mm,3,@.DealDate)) = 1
THEN DATEADD(mm,3,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,3,@.DealDate)) = 7
THEN DATEADD(mm,3,@.DealDate) + 2
ELSE DATEADD(mm,3,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('6 mo', CASE WHEN DATEPART(dw,DATEADD(mm,6,@.DealDate)) = 1
THEN DATEADD(mm,6,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,6,@.DealDate)) = 7
THEN DATEADD(mm,6,@.DealDate) + 2
ELSE DATEADD(mm,6,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('9 mo', CASE WHEN DATEPART(dw,DATEADD(mm,9,@.DealDate)) = 1
THEN DATEADD(mm,9,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,9,@.DealDate)) = 7
THEN DATEADD(mm,9,@.DealDate) + 2
ELSE DATEADD(mm,9,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('12 mo', CASE WHEN DATEPART(dw,DATEADD(mm,12,@.DealDate)) = 1
THEN DATEADD(mm,12,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,12,@.DealDate)) = 7
THEN DATEADD(mm,12,@.DealDate) + 2
ELSE DATEADD(mm,12,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('15 mo', CASE WHEN DATEPART(dw,DATEADD(mm,15,@.DealDate)) = 1
THEN DATEADD(mm,15,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,15,@.DealDate)) = 7
THEN DATEADD(mm,15,@.DealDate) + 2
ELSE DATEADD(mm,15,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('18 mo', CASE WHEN DATEPART(dw,DATEADD(mm,18,@.DealDate)) = 1
THEN DATEADD(mm,18,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(mm,18,@.DealDate)) = 7
THEN DATEADD(mm,18,@.DealDate) + 2
ELSE DATEADD(mm,18,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('2 yr', CASE WHEN DATEPART(dw,DATEADD(yy,2,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,2,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('3 yr', CASE WHEN DATEPART(dw,DATEADD(yy,3,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,3,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
INSERT INTO tblMaturityDate
VALUES ('5 yr', CASE WHEN DATEPART(dw,DATEADD(yy,5,@.DealDate)) = 1
THEN DATEADD(yy,2,@.DealDate) + 1
WHEN DATEPART(dw,DATEADD(yy,5,@.DealDate)) = 7
THEN DATEADD(yy,2,@.DealDate) + 2
ELSE DATEADD(yy,2,@.DealDate)
END)
SELECT * FROM tblMaturityDate
GO
--End
"TLD" wrote:

> Through a stored procedure I want to insert twelve rows into a table with
> each value dependent on a single date value passed to the procedure. I've
> tried various combinations of SET and GO, but always lose the variable
> definition. What would be the structure of the statements for inserting th
e
> rows?

Passing a string containing a single quote

I am trying to pass a string that contains a single quote. I can do it if I put double quotes around it, however, if the string is larger then 128 characters then I get an error that says:

Run-time error '-2147217900 (80040e14)':
The identifier that starts with '01234567890...' is too long. Maximum length is 128"

Which, I think, via my research, is telling me that "it" thinks that the column name is too long. Regardless of what that does or does not mean, I have worked through using the data types and setting quote identifyer on and off, etc. It is none of thoes. I have traced it down to that single quote. I can run the stored procedure directly from the Access window by double clicking on it and when it prompts me for the parameter I can enter the string containing the single quote and it accepts it just fine, so it is not the table design or the actual stored procedure.

If I pass the string using a single quote it is OK, but the single quote will give the error about being too long when there is a single quote in the string and it will sstart with the "2" so i know it is that single quote.

I have tried to wrap the string in double double quotes and double single quotes, and nothing seems to work.

How can I pass the string that contains a single quote?!!

I am using Access 2000 (.ADP not .MDB) and MS SQL 7.

=========================
Dim strInfo As String
strInfo = "01'20000123456789012345678901234567890123456789012 34567890123456789012345678901234567890123456789012 345678901234567890123456789012345678"

' strInfo = Chr(39) & Chr(34) & strInfo & Chr(34) & Chr(39)
strInfo = Chr(39) & strInfo & Chr(39)

Dim cnADOConnectionObject As ADODB.Connection
Set cnADOConnectionObject = New ADODB.Connection

cnADOConnectionObject.ConnectionString = "Provider=SQLOLEDB;Data Source=MERCURY;Initial Catalog = pm-data;User ID=mitch; Password=mitch;"
cnADOConnectionObject.Open
'=====================
'Chr(39)=' chr(34)= "

Call cnADOConnectionObject.Execute("spLogAddTest" & " @.mystrInfo=" & strInfo)

'To Disconnect:
cnADOConnectionObject.Close
'Set rs = Nothing
Set cnADOConnectionObject = Nothingstart by fireing up Profiler so you can look at the commands you are sending your server. I learned some interesting things about ADO this was.

Single quotes can be handdled in two ways:

select 'Can''t tell if this will work'
select "Can't tell is this will work"

I am sure there are more but these are the most common. Next I would look at youe execute statment, unless I am wrong you are sending the following commnd to your server...

spLogAddTest @.mystrInfo= 01'20000123456789012345678901234567890123456789012
34567890123456789012345678901234567890123456789012
345678901234567890123456789012345678

Could you change your .Execute statment to "spLogAddTest" & " @.mystrInfo=""" & strInfo & """"

or add replace(strinfo, "'","''")|||I was able to get this to work using the following code:

=======================

strInfo = "66'33000000000000000000000000000000001234567890123 45678901234567890123456789012345678901234567890123 45678901234567890123456789012345678901234567890123 456789012345678"

strSingleQ = Chr(39)
strDoubleQ = Chr(39) & Chr(39)
strInfo = Replace(strInfo, strSingleQ, strDoubleQ, , , vbTextCompare)

Call cnADOConnectionObject.Execute("spLogAddTest" & " @.mystrInfo=" & Chr(39) & strInfo & Chr(39))|||Paul Young,
"Can't tell is this will work" is a column name.|||ispaleny,
"Can't tell is this will work" is a column name, or object identifier, when SET QUOTED_IDENTIFIER is ON.

"Can't tell is this will work" is a literal string when SET QUOTED_IDENTIFIER is OFF.|||So it is setting dependent. I wonder mistux get it to work, he has QUOTED_IDENTIFIER OFF by default on both servers.

I never have had seen it before you posted. It violates SQL-92 rules. Pre-MSSQL2K syntax ?|||I think this goes back to Sybase Ver 4.2 when Microsoft ripped off, oops I mean briefly collaborated with Sybase to make SQL Server.

I should have stated that using double quotation marks for literal strings requires a non-standard setting.

Perhaps I should adopt a practice of posting my QA settings when offering code examples.

IMHO, braces "[]" are better delimiters for non-standard object identifiers. But then again I truly feel that Null equals Null.|||Paul Young,

NULL can never equals NULL.
Why other programming languages do not have better NULL support.
VB6 had a variant datatype and it don't like variants, but wkat about nullable integer, boolean or string?

Also more kinds of NULL would help. Sometimes it is "missing value" other times "not existing value" and others.

But let's stop talking about NULLs. I have found a solution for "Ad hoc non-trusted user access to OLE DB provider". Many Webs have pages written about it, but none works. I know how to do it, but I don't know how it works. Can you look at it at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20565290.html

I reply to many questions. But when I ask, nobody replies.

( If you cannot access that site, I will repost it to dbforums.com. )

Passing a stored procedure parameter into an IN clause

Hi All :)

I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @.CustCode). The parameter is passed using a DECommand object in VB6.

I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@.CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@.CustCode1, @.CustCode2,...etc), but do not want to limit the number of customers.

If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

and

'KA1001', 'KA1002'

Both fail :(

Any ideas?

Regards

Xoyou need to parse your list into a table variable and then join to that table variable.|||How about

WHERE
','+@.CustCode+','
LIKE
'%,'+CustomerCode+',%'

perhaps?

It's crap (won't use an index) but does work and is easy (might need some trims in there).
These requirements are really dodgy but sadly very common. You shoudl really try using an array of some kind. I would stay away from dynamic SQL which is the other way.|||You can also do this by passing the list of parameters as nText and using XML to add as many options you want. In the sproc you will need to use sp_xml_PrepareDocument to force that into a local temp table.

It's all kinda tricky, but crafty at the same time.|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

alex8675: Thanks but no thanks!!! :)

Thrasymachus: Thanks!! Have used your solution for now :)|||Bookmark this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

What's to not understand? It's just a where statement. Did you try it?|||Hi Xogon

A bit of a bumpy ride for your second post methinks - don't take it personal, sometimes people forget what it's like to not fully understand the basics.

One easy Option for you - but not the most efficient is to build you SQL Statement in a VarChar and then EXECUTE it.

Heres an example


Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT [something]'
SELECT @.SQL = @.SQL + ' FROM [Table]'
SELECT @.SQL = @.SQL + ' WHERE CustomerCode IN (' + @.CustCode + ')'

EXECUTE (@.SQL)

And watch out for the Single Quotes in your @.CustCode these will have to be doubled up

GW|||ummmmmmmmmmm...did you read the link I posted?|||thanks GWilliy - worked a treat!! :)|||UUummmmmmmmmmmmmmmmmm - LOL

I Obviously did Brett & found the link in the Thread to another thread which had this Code.CREATE Procedure sp_dynamic_test
@.TableName varChar(100),
@.ID1 varchar(100),
@.ID2 varchar(100),
@.ID3 varchar(100),
@.DX varchar(100),
@.family varchar(100)
AS

Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT FIRSTNAME, LASTNAME, @.ID1, @.ID2, @.ID3, @.DX '
SELECT @.SQL = @.SQL + ' FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE Family = COALESCE((NULLIF(@.Family,0)),Family) '

Exec ( @.SQL)

Probably Naughty of me to do the Leg work for xogon, Plagarize & Dumb the code down.

I don't know what Skill level xogon is @. (2nd Post) but I thought a Concise & Clear example is often a good place to start. Complexities & doing his own Googles will organically follow.

DBForums is a Fantastic Site but I think sometimes posting a link to a related thread can frustrate New members, Although I will accept a point about too much hand holding.

hopefuly I've not upset anyone

GW|||Still, the use of the table variable is MUCH preferred over building dynamic SQL like you are suggesting and as I fear xogon has implemented.

We actually have built a function that takes as input the CSV string of (in this case, customer numbers), parses it, and returns the required table. That way I offer some reasonable methodology to the unwashed masses of developers here that need to do what you are doing in some form or fashion.

I didn't check Brett's link, but I think this function was derived from an earlier query I made on this very subject eons ago.CREATE FUNCTION [dbo].[fn_CSVList_FilteredPortfolioTable] (@.CsvList varchar(4000))
RETURNS table
AS

RETURN ( SELECT TOP 100 PERCENT *
FROM dbo.Portfolio (nolock)
WHERE ((CHARINDEX(',' + CAST(PortfolioID AS VARCHAR) + ',', ',' + @.CsvList + ',') > 0)
AND (isInactive <> 1))
ORDER BY PortfolioID)this code is probably even more complex than yours would need to be (you really would only need the CHARINDEX line), as it "verifies" the entries in the input CSV string against a table of valid codes.

Still, the use of a function lets you do the conversion on the fly in selects by join, such as: SELECT DISTINCT CL.PortfolioID, CL.StockOSID, @.CreateDate AS CreateDate, BuyDate, SellDate, CL.StockSymbol, Weight
FROM dbo.CurrentList CL (nolock)
INNER JOIN dbo.fn_CSVList_FilteredPortfolioTable(@.PortfoliosT oProcess) FP ON
CL.PortfolioID = FP.PortfolioID
WHERE ((@.CreateDate > CL.Buydate) AND
(@.CreateDate <= CL.SellDate)) AND
(CL.Active = 1)

errrr...and forgive the use of the fn_ prefix on the function name...it was created before I knew better, and at the suggestion of the senior SQL Server developer here at the time. *blush*

passing a comma delimited string to stored procedure

Hello,

I have an asp page that sends a string,
ex. CO,S2,S3,S4,S5,S6,SA,SB,SD,SF,SG,SO,SQ,SR,ST
to a stored procedure in sql server as a single variable
(example @.str).

I want to then somehow split the variable's contents up as 'CO', 'S2', etc to use in a select statement's WHERE IN clause.

Ive tried the replace function to replace the , (comma) with ',' but didnt get the right syntax possibly...

Anybody have any leads or samples done before for this.

Very much appreciated in advance.What about this idea?

drop proc test2
go
create proc test2 @.line varchar(8000)
as
declare @.sql varchar(8000)
select @.sql='select ''ok'' where ''A'' in('+@.line+')'
select @.sql
exec(@.sql)
go
test2 '''A'',''B'',''C'',''D'''
go|||I assume you are going to use this in a dynamic SQL statement?

You say you got a syntax error. Did you remember to put single quotes before and after the string (as snail illustrates), as well as around the commas?

I find it helpful in debugging dynamic code to construct the code in a variable and then PRINT the variable immediately before executing it. If you do this and you are still having problems, post your SQL so that we can review it.

blindman