So in my case, how do I differentiate between the string intended for @.Field
1
from the ones intended for @.Field2?
sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
or do I do something like this:
sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
encased in a double qoute to identify it as one string to be passed over.
THANK YOU! :)
"Mark Williams" wrote:
> You'll need double single-quotes (that's really clear!) to get what you wa
nt
> For example
> DECLARE @.foo varchar(50)
> SET @.foo = '''ABC'', ''DEF'', ''GHI''' --those are all single quotes!
> SELECT @.foo
> returns
> 'ABC', 'DEF', 'GHI'
>
> "ckeaton@.inergex.com" wrote:
>I'm not sure why it's not working when I try to pass the value as it stands
in your most recent example.
It's not finding the individual values which leads me to believe that
something about how the construct is being presented to the database is wron
g.
It should end out looking like this...
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN ('ABC', 'DEF', 'GHI');
But it appears not to be doing that.
Thank you for your suggestions.
Frustrating, because this seems like such a simple thing, but it's tripping
me up! :)
"Mark Williams" wrote:
> You would want something like the second example, but with all single quot
es
> again:
> sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
> "ckeaton@.inergex.com" wrote:
>|||> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is
> wrong.
Maybe you could show us your code? (I, for one, only see three messages so
far in this thread. It appears to have been orphaned in some way.)|||It looks like the statement is evaluating the variable as a single string.
For example, try the following in the Northwind sample database
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1, @.var2)', N'@.var1 varchar(50), @.var2 varchar(50)
',
@.var1='Konbu',@.var2='Tofu'
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1)', N'@.var1 varchar(50)',
@.var1='''Konbu'',''Tofu'''
The first one works, but the second doesn't.
If you wanted to pass your list of coupons as just comma-seperated, you
could do something like this
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @.coupons TABLE (coupon char(3))
DECLARE @.coupon char(3)
WHILE CHARINDEX(',',@.Field2)<>0
BEGIN
INSERT INTO @.coupons (coupon) VALUES (LEFT(@.Field2, CHARINDEX(',',@.Field2)
- 1))
SET @.Field2=SUBSTRING(@.Field2, CHARINDEX(',', @.Field2) + 1,LEN(@.Field2))
END
INSERT INTO @.coupons (coupon) VALUES (@.Field2)
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (SELECT coupon FROM @.coupons)
--alternatively AND EXISTS (SELECT coupon FROM @.coupons WHERE
coupon=P.coupon)
The procedure would be called like
sp_Monkey '24601','ABC,DEF,GHI'
The procedure splits the comma-seperated Field2 and inserts each value into
the table variable @.coupons, which is reference later.
"ckeaton@.inergex.com" wrote:
> I'm not sure why it's not working when I try to pass the value as it stand
s
> in your most recent example.
> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is wr
ong.
> It should end out looking like this...
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN ('ABC', 'DEF', 'GHI');
> But it appears not to be doing that.
> Thank you for your suggestions.
> Frustrating, because this seems like such a simple thing, but it's trippin
g
> me up! :)
>|||You would want something like the second example, but with all single quotes
again:
sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
"ckeaton@.inergex.com" wrote:
> So in my case, how do I differentiate between the string intended for @.Fie
ld1
> from the ones intended for @.Field2?
> sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
> or do I do something like this:
> sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
> encased in a double qoute to identify it as one string to be passed over.
> THANK YOU! :)
> "Mark Williams" wrote:
>
Showing posts with label string. Show all posts
Showing posts with label string. Show all posts
Wednesday, March 28, 2012
passing multi value parameter to stored procedure
I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.
Change the command type to Stored Procedure and type
SPMultiValue
Now click OK and hit the refresh button. Now go back to Edit the dataset and go to parameters tab and map it with the correct parameter.
For multi value parameter within stored procedure you need to decode the multi values to be able to use in the query.
|||Thanks for u'r reply. I have GroupDataBy parameter. Based on that value I need to call different stored procedure.
I have to use commond type As text. Is there any way to pass Multi value parameter for the following statement
"Exec spMultiValue '" & Parameters!Month.Value & "'"
|||Change Parameters!Month.Value to Parameters!Month.IsMultiValuePassing Multi Value Delimited string to a parameter
Hi Everyone,
I am using, or want to use a parameter as a filter in a sql statement, the
user will pick from a list that will then send a delimited list of numbers
e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
statement with a " field IN (@.parameter) " type thing.
Im sure this should work and its probably just the phrasing or something
that ive got wrong, can anyone help?
Steve DMulti-select parameters are a feature of RS 2005. This will not work in RS
2000. You can do dynamic sql to do this (note that you do open up to
injection attacks). Switch to generic query designer (to the right of the
...). Put in something like this.
="select somefield from sometable where anotherfield in (" &
Parameters.ParamName.Value & ")"
Note that RS will not detect your field list, so first have a regular query
to populate the field list.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:OFZt89YWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I am using, or want to use a parameter as a filter in a sql statement, the
> user will pick from a list that will then send a delimited list of numbers
> e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
> statement with a " field IN (@.parameter) " type thing.
> Im sure this should work and its probably just the phrasing or something
> that ive got wrong, can anyone help?
> Steve D
>
I am using, or want to use a parameter as a filter in a sql statement, the
user will pick from a list that will then send a delimited list of numbers
e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
statement with a " field IN (@.parameter) " type thing.
Im sure this should work and its probably just the phrasing or something
that ive got wrong, can anyone help?
Steve DMulti-select parameters are a feature of RS 2005. This will not work in RS
2000. You can do dynamic sql to do this (note that you do open up to
injection attacks). Switch to generic query designer (to the right of the
...). Put in something like this.
="select somefield from sometable where anotherfield in (" &
Parameters.ParamName.Value & ")"
Note that RS will not detect your field list, so first have a regular query
to populate the field list.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve Dearman" <steve.dearman@.grant.co.uk> wrote in message
news:OFZt89YWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I am using, or want to use a parameter as a filter in a sql statement, the
> user will pick from a list that will then send a delimited list of numbers
> e.g. "a1,a2,a3,a4" as the parameter that will be referenced in the sql
> statement with a " field IN (@.parameter) " type thing.
> Im sure this should work and its probably just the phrasing or something
> that ive got wrong, can anyone help?
> Steve D
>
Monday, March 26, 2012
Passing Index Value
I am attempting to string together insert statements in a stored procedure...
In tblCatalogue there is all the information on catalogues.
In tblArticles there is all the information on articles.
There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.
I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?
All help and comments are appreciated.I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?
It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.
-PatP|||Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.
The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.
What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.
I hope this makes sense, and thanks for your quick reply.|||If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @.@.IDENTITY.
In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.
That should get you on your way!
-PatP
In tblCatalogue there is all the information on catalogues.
In tblArticles there is all the information on articles.
There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.
I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?
All help and comments are appreciated.I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?
It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.
-PatP|||Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.
The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.
What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.
I hope this makes sense, and thanks for your quick reply.|||If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @.@.IDENTITY.
In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.
That should get you on your way!
-PatP
Passing GUID into dtexec, invalid cast to a string variable. Solution?
I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.
Thanks for any help! Aaron B.
What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.
/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"
|||Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.
passing empty string to stored procedure -SQL Express 2005
I am taking data from a form and passing it to a stored procedure to insert
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:
> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:
> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.
Friday, March 23, 2012
Passing DB Name/Owner Name in a prepareCall statement
When calling a stored procedure in sql server passing just the Stored
Procedure name, my stored procedure name works correctly. Below is
the String I use when performing Connection.prepareCall(String)
{CALL sp_name (?,?,?)}
However when I want to specify the db name and the user name (name of
the user who owns the sp), I get an error that the driver could not
find the stored procedure. Any clues as to how this could be fixed?
In other words, how can I set the default database name and owner name
to query against?
{CALL dbName.username.sp_name (?,?,?)}
Ryan wrote:
> When calling a stored procedure in sql server passing just the Stored
> Procedure name, my stored procedure name works correctly. Below is
> the String I use when performing Connection.prepareCall(String)
> {CALL sp_name (?,?,?)}
> However when I want to specify the db name and the user name (name of
> the user who owns the sp), I get an error that the driver could not
> find the stored procedure.
It's the DBMS, not the driver, that can or cannot find a stored procedure.
Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
help.
Any clues as to how this could be fixed?
> In other words, how can I set the default database name and owner name
> to query against?
> {CALL dbName.username.sp_name (?,?,?)}
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Ryan wrote:
> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...
>
> I get the following exception:
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]The procedure name
> 'dbName.username1.dbName.userName2.' contains more than the maximum
> number of prefixes. The maximum is 3.
> The call I pass to Connection.prepareCall is:
> {CALL dbName.username2.sp_name (?,?,?)}
> where dbname = the name of the database that I logged into. Username1
> is the username that I logged in with. Username2 is the username of
> the owner of the stored procedure and sp_name is the name of the
> stored procedure.
very odd. Does this code work for you?
It does for me... (logging in as a non-sa user...)
Statement s = c.createStatement();
s.execute("use tempdb");
s.execute("exec master.dbo.sp_who");
PreparedStatement p = c.prepareStatement("{ call master.dbo.sp_who() }");
p.execute();
CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who() }");
cl.execute();
|||Thanks Joe,
I found the culprit in an extended class. It was prepending a default
databaseName.ownerName. causing this "weird" behavior. A wasted
thread,
{CALL dbname.ownername.sp_name (?,?,?)}
will work.
Joe Weinstein wrote:[vbcol=seagreen]
> Ryan wrote:
news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
Stored[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
not[vbcol=seagreen]
procedure.[vbcol=seagreen]
that will[vbcol=seagreen]
name[vbcol=seagreen]
Username1[vbcol=seagreen]
of
> very odd. Does this code work for you?
> It does for me... (logging in as a non-sa user...)
> Statement s = c.createStatement();
> s.execute("use tempdb");
> s.execute("exec master.dbo.sp_who");
> PreparedStatement p = c.prepareStatement("{ call
master.dbo.sp_who() }");
> p.execute();
> CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who()
}");
> cl.execute();
Procedure name, my stored procedure name works correctly. Below is
the String I use when performing Connection.prepareCall(String)
{CALL sp_name (?,?,?)}
However when I want to specify the db name and the user name (name of
the user who owns the sp), I get an error that the driver could not
find the stored procedure. Any clues as to how this could be fixed?
In other words, how can I set the default database name and owner name
to query against?
{CALL dbName.username.sp_name (?,?,?)}
Ryan wrote:
> When calling a stored procedure in sql server passing just the Stored
> Procedure name, my stored procedure name works correctly. Below is
> the String I use when performing Connection.prepareCall(String)
> {CALL sp_name (?,?,?)}
> However when I want to specify the db name and the user name (name of
> the user who owns the sp), I get an error that the driver could not
> find the stored procedure.
It's the DBMS, not the driver, that can or cannot find a stored procedure.
Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
help.
Any clues as to how this could be fixed?
> In other words, how can I set the default database name and owner name
> to query against?
> {CALL dbName.username.sp_name (?,?,?)}
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
> Ryan wrote:
>
> It's the DBMS, not the driver, that can or cannot find a stored procedure.
> Your JDBC SQL seems fine. Show the actual exception you get. Maybe that will
> help.
> Any clues as to how this could be fixed?
I get the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]The procedure name
'dbName.username1.dbName.userName2.' contains more than the maximum
number of prefixes. The maximum is 3.
The call I pass to Connection.prepareCall is:
{CALL dbName.username2.sp_name (?,?,?)}
where dbname = the name of the database that I logged into. Username1
is the username that I logged in with. Username2 is the username of
the owner of the stored procedure and sp_name is the name of the
stored procedure.
|||Ryan wrote:
> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<412CF482.9070904@.bea.com>...
>
> I get the following exception:
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]The procedure name
> 'dbName.username1.dbName.userName2.' contains more than the maximum
> number of prefixes. The maximum is 3.
> The call I pass to Connection.prepareCall is:
> {CALL dbName.username2.sp_name (?,?,?)}
> where dbname = the name of the database that I logged into. Username1
> is the username that I logged in with. Username2 is the username of
> the owner of the stored procedure and sp_name is the name of the
> stored procedure.
very odd. Does this code work for you?
It does for me... (logging in as a non-sa user...)
Statement s = c.createStatement();
s.execute("use tempdb");
s.execute("exec master.dbo.sp_who");
PreparedStatement p = c.prepareStatement("{ call master.dbo.sp_who() }");
p.execute();
CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who() }");
cl.execute();
|||Thanks Joe,
I found the culprit in an extended class. It was prepending a default
databaseName.ownerName. causing this "weird" behavior. A wasted
thread,
{CALL dbname.ownername.sp_name (?,?,?)}
will work.
Joe Weinstein wrote:[vbcol=seagreen]
> Ryan wrote:
news:<412CF482.9070904@.bea.com>...[vbcol=seagreen]
Stored[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
not[vbcol=seagreen]
procedure.[vbcol=seagreen]
that will[vbcol=seagreen]
name[vbcol=seagreen]
Username1[vbcol=seagreen]
of
> very odd. Does this code work for you?
> It does for me... (logging in as a non-sa user...)
> Statement s = c.createStatement();
> s.execute("use tempdb");
> s.execute("exec master.dbo.sp_who");
> PreparedStatement p = c.prepareStatement("{ call
master.dbo.sp_who() }");
> p.execute();
> CallableStatement cl = c.prepareCall("{ call master.dbo.sp_who()
}");
> cl.execute();
Passing DateTime Parameter to Subreport (Error)
Hi guys,
I have managed to pass a string parameter form the main report to the subreport with no problems, but when i try and use the same method to pass a datetime type, i am issued with the following error messase:
"[rsErrorExecutingSubreport] An error occurred while executing the subreport ‘SubReportName’: The value provided for the report parameter 'MasterDate' is not valid for its type."
I cant see why it work for strings, but not for datetime. Any suggestions will be very much appreciated
Passing Date as String
I have a report that returns date in the format 'YYYY-MM-DD'. This report
has no parameters, but I am drilling to another report that accepts date as a
parameter. So, I select the textbox I want to drill from and select "Jump to
Report". I select the report to be drilled to and define the parameters that
are to be passed.
Parameter Parameter Value
date =Fields!date.Value
Next, I go to the report that is to be drilled to and add date as a
parameter. Parameter name "date", parameter data type "string". Now, when
the drill report passes the date to the drilled report, the date parameter is
filled in as:
MM/DD/YYYY 00:00:00
Why is RS converting my strings and what is the best way to either make it
stop or work around this problem.
Thanks for any help!Sprinkle liberally with:
=Format(Convert.ToDateTime(Fields!date.Value), "yyyy-MM-dd")
Cheers,
"asnewbie+rs=argh" wrote:
> I have a report that returns date in the format 'YYYY-MM-DD'. This report
> has no parameters, but I am drilling to another report that accepts date as a
> parameter. So, I select the textbox I want to drill from and select "Jump to
> Report". I select the report to be drilled to and define the parameters that
> are to be passed.
> Parameter Parameter Value
> date =Fields!date.Value
> Next, I go to the report that is to be drilled to and add date as a
> parameter. Parameter name "date", parameter data type "string". Now, when
> the drill report passes the date to the drilled report, the date parameter is
> filled in as:
> MM/DD/YYYY 00:00:00
> Why is RS converting my strings and what is the best way to either make it
> stop or work around this problem.
> Thanks for any help!
>
has no parameters, but I am drilling to another report that accepts date as a
parameter. So, I select the textbox I want to drill from and select "Jump to
Report". I select the report to be drilled to and define the parameters that
are to be passed.
Parameter Parameter Value
date =Fields!date.Value
Next, I go to the report that is to be drilled to and add date as a
parameter. Parameter name "date", parameter data type "string". Now, when
the drill report passes the date to the drilled report, the date parameter is
filled in as:
MM/DD/YYYY 00:00:00
Why is RS converting my strings and what is the best way to either make it
stop or work around this problem.
Thanks for any help!Sprinkle liberally with:
=Format(Convert.ToDateTime(Fields!date.Value), "yyyy-MM-dd")
Cheers,
"asnewbie+rs=argh" wrote:
> I have a report that returns date in the format 'YYYY-MM-DD'. This report
> has no parameters, but I am drilling to another report that accepts date as a
> parameter. So, I select the textbox I want to drill from and select "Jump to
> Report". I select the report to be drilled to and define the parameters that
> are to be passed.
> Parameter Parameter Value
> date =Fields!date.Value
> Next, I go to the report that is to be drilled to and add date as a
> parameter. Parameter name "date", parameter data type "string". Now, when
> the drill report passes the date to the drilled report, the date parameter is
> filled in as:
> MM/DD/YYYY 00:00:00
> Why is RS converting my strings and what is the best way to either make it
> stop or work around this problem.
> Thanks for any help!
>
Wednesday, March 21, 2012
Passing around message contents as Streams (conversion to/from String & XmlDoc's).
More of a general "Streams" question than broker specific, but this is all being done in the context of Broker passing the messages around. The use of Streams & Encoding seems to be my problem and I'm not as familiar with Streams as I am with other areas of the Framework... Any advice would be appreciated.
At this point, I've created my own objects/stored procedures based loosely on the ServiceBrokerInterface class provided in the SQL Server samples. Some of this was done for simplification and as a learning exercise, but also to ensure that all of the SQL operations are being done via Stored Procedures and not inline SQL. This was done to adhere to our existing security policy used on this project.
In this "interface" I've built, I have a [BrokerMessage.cs] class which is meant to have a few additional pieces of functionality beyond what the MS provided version had supplied.
1st... A constructor for accepting either String or XmlDocument as the "content"
2nd... Methods to return either a XmlDocument or a simple String.
Since all of the Broker functionality is defined as using VARBINARY(MAX) in my stored procedures, I don't believe I have any problems at that level. It's simply a binary blob to Broker.
In my constructor for accepting String or XmlDocuments, I attempted to use the following...
public BrokerMessage(string type, XmlDocument contents)
{
m_type = type;
m_contents = new MemoryStream(EncodingProvider.GetBytes(contents.InnerXml));
}
My understanding was that MemoryStream is derived from Stream so I can implicitly cast it. The "EncodingProvider" is a static member set as follows:
public static Encoding EncodingProvider = Encoding.Unicode;
This way I ensure that internal & external code can all be set to use the same encoding and easily changed if necessary. I was hoping to avoid using Unicode since the rest of the application does not require it, but from my understanding all Xml documents in SQL Server are Unicode based, so this should be a better encoding choice for any processing that may potentially occur within SQL Server itself.
In my methods to return the various forms of the "Stream", I have the following code... The ToBytes() method is what is used to pass intot he stored procedure parameter that is defined as VarBinary and expecting a byte array. One area of concern is that the Read method accepts an INT for the length, but the actual Length property is a LONG. I'm sure there's a better way to handle this and I would welcome any advise there.
/// <summary>
/// Used to convert from a Stream back to a simple Byte array.
/// </summary>
/// <returns></returns>
public virtual byte[] ToBytes()
{
byte[] results = new byte[this.Contents.Length];
this.Contents.Read(results, 0, (int)this.Contents.Length);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple String.
/// </summary>
/// <returns></returns>
public new string ToString()
{
byte[] buffer = this.ToBytes();
String results = EncodingProvider.GetString(buffer);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple XmlDocument.
/// </summary>
/// <returns></returns>
public virtual XmlDocument ToXmlDocument()
{
XmlDocument results = new XmlDocument();
results.InnerText = this.ToString();
return results;
}
Further confusion & frustration. I'm frustrated that the stream Read methods all use INT for parameters. Which makes no sense since a Stream length can be Long. Even when reading in a "chunk" at a time, the "offset" in the buffer might potentially need to be a Long... even if the "read size" is just an Int. argh...
So I revised my code as below. Not quite a finished piece of code, but more of a draft to get me going. I thought using one of these "Readers" as the HellowWorld_CLR sample used might hold some answers. Not the case. Both StreamReader and BinaryReader are giving me no results.
This appears to be because after "using" the Stream once to populate a database table (this is on a received message populated from a SqlBytes/Reader), subsequent access fails. When I look at the underlying datatype, it is of the [SqlTypes.StreamOnSqlBytes] type. One thing I noticed in the ServiceBrokerInterface / Message.cs class is that when populating from the DataReader, they call the m_XXXXX variables for everything BUT the actual message content. For that case, they call the Body Property Set which is defined as Stream just like the underlying m_Body member. Seemed odd, so I followed suit in my class but that did not resolve the problem.
So here's the catch. When debugging this in SQL/CLR I did come across something odd. The Position is always 64 and the length 64. When I first consume the stream, Position was at zero. So I explicity put calls in to move the "position". I do this via BOTH the Position and Seek members. The result is that Position stays at 64. I can see through QuickWatch that the "CanSeek" value is "true". So this makes no sense. Now, to further confuse the issue. When I tried to set the Position property via QuickWatch, it would not change.... BUT.... when I hovered over it until IntelliSense displayed the value and then editited it there... the property "took" and was at zero. Attempting to perform a "Read" on the stream at that point returned my data as it should. This was not always a repeatable exercise, but 1 out of 3 or so... very inconsistent. When I took that byte array and did a "GetString" from my Encoding... I did see my original Xml message as it should be. So this confirms that I'm properly sending the message and that it's on the "reception" side of things where I'm having trouble.
Any Stream experts out there care to chime in? Streams shouldn't be this difficult to utilize. What am I missing here?
protected virtual byte[] ToBytes()
{
long readBytes = 0;
int readSize = 1024;
int lastRead = 0;
int position = 0;
this.Contents.Position = 0;
this.Contents.Seek(0, SeekOrigin.Begin);
BinaryReader reader = new BinaryReader(this.Contents, EncodingProvider);
byte[] results = new byte[this.Contents.Length];
while (readBytes < this.Contents.Length)
{
if ((this.Contents.Length - readBytes) < readSize)
{
readSize = (int)(this.Contents.Length - readBytes);
}
lastRead = reader.Read(results, position, readSize);
readBytes += readSize;
position = (int)readBytes + 1;
}
return results;
}
|||After looking at how SqlBytes was being used in the population, I attempted the following and this works. I can't explain why the previous attempts did not work or why this ones does.... but I am getting back the proper array of bytes.
protected virtual byte[] ToBytes()
{
SqlBytes bytes = new SqlBytes(this.Contents);
byte[] results = bytes.Buffer;
return results;
}
|||By default, SqlCommand.ExecuteReader will read one row at a time. So if you were doing a RECEIVE, it will bring the entire row including the message body into an in-memory buffer. In that case, the SqlBytes object created by DataReader.GetSqlBytes() contains a buffer that has the entire message_body inside it. If you want true streaming, you will have to specify the CommandBehavior to be SequentialAccess in the call to ExecuteReader. While reading the columns you must read in the order they are declared in the query. GetSqlBytes() will return a true stream and the Buffer property will not give you the entire message at once.
About your question regarding why the Stream.Read api takes in an int-sized offset argument? The offset argument is not an offset into the stream itself but one into the buffer you supply to the Read method. Since the buffer cannot be larger than 2^32 bytes (otherwise it wouldn't be addressable in 32-bit environments) the offset argument is simply an int.
Finally, you are already making an assumption that the message size will fit in memory (since you are using XmlDocument and string). So why bother with streams at all? You could simply use DataReader.GetBytes() which returns a byte array that can be decoded and parsed into an XmlDocument object.
Rushi
|||Digging through the ADO.NET's ExecuteReader implementation using the Reflector, it seems that the streaming behavior using SqlBytes is only available for SQLCLR "context connections" and not for TDS. So effectively for Service broker external activations, CommandBehavior.SequentialAccess does not provide much of a benefit as far as streaming functionality is concerned. Can anybody confirm this?
Thanks,
Ramkishore
sqlPassing around message contents as Streams (conversion to/from String & XmlDoc's).
More of a general "Streams" question than broker specific, but this is all being done in the context of Broker passing the messages around. The use of Streams & Encoding seems to be my problem and I'm not as familiar with Streams as I am with other areas of the Framework... Any advice would be appreciated.
At this point, I've created my own objects/stored procedures based loosely on the ServiceBrokerInterface class provided in the SQL Server samples. Some of this was done for simplification and as a learning exercise, but also to ensure that all of the SQL operations are being done via Stored Procedures and not inline SQL. This was done to adhere to our existing security policy used on this project.
In this "interface" I've built, I have a [BrokerMessage.cs] class which is meant to have a few additional pieces of functionality beyond what the MS provided version had supplied.
1st... A constructor for accepting either String or XmlDocument as the "content"
2nd... Methods to return either a XmlDocument or a simple String.
Since all of the Broker functionality is defined as using VARBINARY(MAX) in my stored procedures, I don't believe I have any problems at that level. It's simply a binary blob to Broker.
In my constructor for accepting String or XmlDocuments, I attempted to use the following...
public BrokerMessage(string type, XmlDocument contents)
{
m_type = type;
m_contents = new MemoryStream(EncodingProvider.GetBytes(contents.InnerXml));
}
My understanding was that MemoryStream is derived from Stream so I can implicitly cast it. The "EncodingProvider" is a static member set as follows:
public static Encoding EncodingProvider = Encoding.Unicode;
This way I ensure that internal & external code can all be set to use the same encoding and easily changed if necessary. I was hoping to avoid using Unicode since the rest of the application does not require it, but from my understanding all Xml documents in SQL Server are Unicode based, so this should be a better encoding choice for any processing that may potentially occur within SQL Server itself.
In my methods to return the various forms of the "Stream", I have the following code... The ToBytes() method is what is used to pass intot he stored procedure parameter that is defined as VarBinary and expecting a byte array. One area of concern is that the Read method accepts an INT for the length, but the actual Length property is a LONG. I'm sure there's a better way to handle this and I would welcome any advise there.
/// <summary>
/// Used to convert from a Stream back to a simple Byte array.
/// </summary>
/// <returns></returns>
public virtual byte[] ToBytes()
{
byte[] results = new byte[this.Contents.Length];
this.Contents.Read(results, 0, (int)this.Contents.Length);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple String.
/// </summary>
/// <returns></returns>
public new string ToString()
{
byte[] buffer = this.ToBytes();
String results = EncodingProvider.GetString(buffer);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple XmlDocument.
/// </summary>
/// <returns></returns>
public virtual XmlDocument ToXmlDocument()
{
XmlDocument results = new XmlDocument();
results.InnerText = this.ToString();
return results;
}
Further confusion & frustration. I'm frustrated that the stream Read methods all use INT for parameters. Which makes no sense since a Stream length can be Long. Even when reading in a "chunk" at a time, the "offset" in the buffer might potentially need to be a Long... even if the "read size" is just an Int. argh...
So I revised my code as below. Not quite a finished piece of code, but more of a draft to get me going. I thought using one of these "Readers" as the HellowWorld_CLR sample used might hold some answers. Not the case. Both StreamReader and BinaryReader are giving me no results.
This appears to be because after "using" the Stream once to populate a database table (this is on a received message populated from a SqlBytes/Reader), subsequent access fails. When I look at the underlying datatype, it is of the [SqlTypes.StreamOnSqlBytes] type. One thing I noticed in the ServiceBrokerInterface / Message.cs class is that when populating from the DataReader, they call the m_XXXXX variables for everything BUT the actual message content. For that case, they call the Body Property Set which is defined as Stream just like the underlying m_Body member. Seemed odd, so I followed suit in my class but that did not resolve the problem.
So here's the catch. When debugging this in SQL/CLR I did come across something odd. The Position is always 64 and the length 64. When I first consume the stream, Position was at zero. So I explicity put calls in to move the "position". I do this via BOTH the Position and Seek members. The result is that Position stays at 64. I can see through QuickWatch that the "CanSeek" value is "true". So this makes no sense. Now, to further confuse the issue. When I tried to set the Position property via QuickWatch, it would not change.... BUT.... when I hovered over it until IntelliSense displayed the value and then editited it there... the property "took" and was at zero. Attempting to perform a "Read" on the stream at that point returned my data as it should. This was not always a repeatable exercise, but 1 out of 3 or so... very inconsistent. When I took that byte array and did a "GetString" from my Encoding... I did see my original Xml message as it should be. So this confirms that I'm properly sending the message and that it's on the "reception" side of things where I'm having trouble.
Any Stream experts out there care to chime in? Streams shouldn't be this difficult to utilize. What am I missing here?
protected virtual byte[] ToBytes()
{
long readBytes = 0;
int readSize = 1024;
int lastRead = 0;
int position = 0;
this.Contents.Position = 0;
this.Contents.Seek(0, SeekOrigin.Begin);
BinaryReader reader = new BinaryReader(this.Contents, EncodingProvider);
byte[] results = new byte[this.Contents.Length];
while (readBytes < this.Contents.Length)
{
if ((this.Contents.Length - readBytes) < readSize)
{
readSize = (int)(this.Contents.Length - readBytes);
}
lastRead = reader.Read(results, position, readSize);
readBytes += readSize;
position = (int)readBytes + 1;
}
return results;
}
|||After looking at how SqlBytes was being used in the population, I attempted the following and this works. I can't explain why the previous attempts did not work or why this ones does.... but I am getting back the proper array of bytes.
protected virtual byte[] ToBytes()
{
SqlBytes bytes = new SqlBytes(this.Contents);
byte[] results = bytes.Buffer;
return results;
}
|||By default, SqlCommand.ExecuteReader will read one row at a time. So if you were doing a RECEIVE, it will bring the entire row including the message body into an in-memory buffer. In that case, the SqlBytes object created by DataReader.GetSqlBytes() contains a buffer that has the entire message_body inside it. If you want true streaming, you will have to specify the CommandBehavior to be SequentialAccess in the call to ExecuteReader. While reading the columns you must read in the order they are declared in the query. GetSqlBytes() will return a true stream and the Buffer property will not give you the entire message at once.
About your question regarding why the Stream.Read api takes in an int-sized offset argument? The offset argument is not an offset into the stream itself but one into the buffer you supply to the Read method. Since the buffer cannot be larger than 2^32 bytes (otherwise it wouldn't be addressable in 32-bit environments) the offset argument is simply an int.
Finally, you are already making an assumption that the message size will fit in memory (since you are using XmlDocument and string). So why bother with streams at all? You could simply use DataReader.GetBytes() which returns a byte array that can be decoded and parsed into an XmlDocument object.
Rushi
|||Digging through the ADO.NET's ExecuteReader implementation using the Reflector, it seems that the streaming behavior using SqlBytes is only available for SQLCLR "context connections" and not for TDS. So effectively for Service broker external activations, CommandBehavior.SequentialAccess does not provide much of a benefit as far as streaming functionality is concerned. Can anybody confirm this?
Thanks,
Ramkishore
Passing an IN (a, b, c) list to a sproc as a string -- best method?
I want to do something like this in a stored proc:
--
Create Procedure dbo.GetPatients
@.PatientIdList varchar(200) -- comma separated list of PatientIDs
As
Select *
From Patients
Where PatientId In (@.PatientIdList)
--
I know the above won't work, but of course what I want is if
@.PatientIdList = '1,2,3' then I want Patient records with PatientIds
1, 2, and 3 returned.
It looks like the only way to do this is to build the SQL statement as
a string within the stored procedure ... which pretty much defeats the
usefulness of using precompiled sprocs as I understand it (better off
building a dynamic query against a View in that case).
Thoughts?
Joel Thornton ~ <groups@.joelpt.eml.cc>Joel,
Erland has a decent writing on this topic.
http://www.sommarskog.se/arrays-in-sql.html
--
-oj
http://www.rac4sql.net
"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401072012.5c38ba06@.posting.google.c om...
> I want to do something like this in a stored proc:
> --
> Create Procedure dbo.GetPatients
> @.PatientIdList varchar(200) -- comma separated list of PatientIDs
> As
> Select *
> From Patients
> Where PatientId In (@.PatientIdList)
> --
> I know the above won't work, but of course what I want is if
> @.PatientIdList = '1,2,3' then I want Patient records with PatientIds
> 1, 2, and 3 returned.
> It looks like the only way to do this is to build the SQL statement as
> a string within the stored procedure ... which pretty much defeats the
> usefulness of using precompiled sprocs as I understand it (better off
> building a dynamic query against a View in that case).
>
> Thoughts?
> Joel Thornton ~ <groups@.joelpt.eml.ccsql
Passing an Entire XQuery String as Variable
Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
Bob
Hello Bob,
No, not really because the XQuery parser is one-pass, so something like this:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is sad to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> sad to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co. uk...[vbcol=seagreen]
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
Bob
Hello Bob,
No, not really because the XQuery parser is one-pass, so something like this:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is sad to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> sad to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co. uk...[vbcol=seagreen]
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Passing an Entire XQuery String as Variable
Hi,
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
BobHello Bob,
No, not really because the XQuery parser is one-pass, so something like this
:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall
,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is
to seem go
this way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
> to seem go this way instead of fully investing in both.
> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co.uk...
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Is there any way to formulate an entire XQuery (XPath) string as a
variable and then pass it to a XQuery e.g.
Use something like this:
@.Path nvarchar(4000)
set @.Path='//category[@.name="RTE"]//sku'
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes(@.Path) AS NewTable(AllSKUs)
To achieve something like this:
SELECT AllSKUs.value('./@.name','nvarchar(MAX)') AS SKU
FROM Hierarchy CROSS APPLY
HierarchyXML.nodes('//category[@.name="RTE"]//sku') AS NewTable(AllSKUs)
Cheers,
BobHello Bob,
No, not really because the XQuery parser is one-pass, so something like this
:
declare @.x xml
set @.x = '<v>3</v>'
declare @.q varchar(255)
set @.q = '(/v)[1]'
select @.x.value('sql:variable("@.q")','int')
go
assumes that that query in @.q is meant to be a literal value so it doens't
get eval'ed as a query.
Cheers,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent,
That's a shame really - the power of XPath is not fully realised there.
I think I'll try writing the whole stored procedure dynamically.
Bob
Kent Tegels wrote:
> Hello Bob,
> No, not really because the XQuery parser is one-pass, so something like
> this:
> declare @.x xml
> set @.x = '<v>3</v>'
> declare @.q varchar(255)
> set @.q = '(/v)[1]'
> select @.x.value('sql:variable("@.q")','int')
> go
> assumes that that query in @.q is meant to be a literal value so it
> doens't get eval'ed as a query.
> Cheers,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob,
> That's a shame really - the power of XPath is not fully realised
> there. I think I'll try writing the whole stored procedure
> dynamically.
I don't know if I'd agree with that. I would say that the team has done the
best they could with limited resources, variable targets and a slower than
expected standards process. Could they have done more? Sure -- if they had
more people and time. They didn't. So I look at as glass half full. Afterall
,
we're much futher along the XQuery path than the client (.NET folks are)
period.
And as I look it, I think we're only going to get limited improvements in
this support over time as Microsoft seems much more interested in putting
their resources into LINQ instead of XQuery. I'm okay with that because it
solves the problem (at least eventually), but part of me is
to seem gothis way instead of fully investing in both.
Time will tell...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is a shame - but what we've got now is still a huge leap ahead of
what we had before. And it's, in general, a joy to work with.
Thanks again,
Bob
Kent Tegels wrote:
> Hi Bob,
>
>
> I don't know if I'd agree with that. I would say that the team has done
> the best they could with limited resources, variable targets and a
> slower than expected standards process. Could they have done more? Sure
> -- if they had more people and time. They didn't. So I look at as glass
> half full. Afterall, we're much futher along the XQuery path than the
> client (.NET folks are) period.
> And as I look it, I think we're only going to get limited improvements
> in this support over time as Microsoft seems much more interested in
> putting their resources into LINQ instead of XQuery. I'm okay with that
> because it solves the problem (at least eventually), but part of me is
>
to seem go this way instead of fully investing in both.> Time will tell...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Bob
Thanks for the comment ;)
We are aware of the request to not having to write dynamic SQL to provide
the XPath expression as a string variable to the XQuery methods.
I would like to encourage you to either file (or if already filed vote) such
a request at
http://lab.msdn.microsoft.com/productfeedback/
On the server we are still investing in XQuery, but as Kent mentions, many
competing requests for the same resources will have to be influenced by you
the customer requesting us to help you improve.
Thanks
Michael
"Bob Turner" <b_turner@.hotmail.co.uk> wrote in message
news:DhCTf.77298$zk4.16164@.fe3.news.blueyonder.co.uk...
> It is a shame - but what we've got now is still a huge leap ahead of what
> we had before. And it's, in general, a joy to work with.
> Thanks again,
> Bob
> Kent Tegels wrote:
Tuesday, March 20, 2012
Passing a VB string to a Crystal parameter field
Hello,
I'm having trouble passing a VB string to a Crystal Report parameter field. Following is an excerpt of the code:
----------------
strDocID = InputBox("Please enter the DocID")
If strDocID <> "" Then
With CrystalReport1
.ReportFileName = "C:\Program Files\CSC Imaging\CSCReports\Reports\DocumentHistory.rpt" .Connect = cn.ConnectionString
.WindowState = crptMaximized
strSelection = "{CSC_TRANS.Document_ID} = " & "'" + strDocID + "'"
.ReplaceSelectionFormula strSelection
.Destination = crptToWindow
.Action = 1
----------------
The user enters the DocID, but when the Crystal Report launches, the user has to enter it again.
Following is the entry in the Record Selection Formula:
{CSC_TRANS.Document_ID} = {?DocumentID_PROMPT}
Also, if they select Cancel in the Crystal parameter window, it still tries to run the report. How can I stop the report from running?
Thank you for your assistance!
Andyeither is wrong the formula,and is crystal report,doesn't know the field. or...
work with an report object,and put
Public reportApp As CRAXDRT.Application
Public Report As CRAXDRT.Report
reportApp=createobject("CRAXDRT.Application")
report=reportApp.loadReport("...rpt")
...for not showing parameter prompting
Report.EnableParameterPrompting=false
report is an object of craxdrt.dll i thing at least i have worked with this one.this is for not showing the parameters inputs again.
also,it should be existing in the report(in design) a parameter(Insert/Parameter) and the value for that should have your docID value
something like this.
Dim cpars As CRAXDRT.ParameterFieldDefinitions
Dim cpar As CRAXDRT.ParameterFieldDefinition
Set cpar = cpars(1)
cpar.AddCurrentValue (docID)
docID here is your VB variable
Maybe this will work,or try to verify carefully the selection formula,AND in report it should be existing a parameter that will take in the code that value!
Good luck|||Thank you very much for the reply, jasie24!
I'm having trouble passing a VB string to a Crystal Report parameter field. Following is an excerpt of the code:
----------------
strDocID = InputBox("Please enter the DocID")
If strDocID <> "" Then
With CrystalReport1
.ReportFileName = "C:\Program Files\CSC Imaging\CSCReports\Reports\DocumentHistory.rpt" .Connect = cn.ConnectionString
.WindowState = crptMaximized
strSelection = "{CSC_TRANS.Document_ID} = " & "'" + strDocID + "'"
.ReplaceSelectionFormula strSelection
.Destination = crptToWindow
.Action = 1
----------------
The user enters the DocID, but when the Crystal Report launches, the user has to enter it again.
Following is the entry in the Record Selection Formula:
{CSC_TRANS.Document_ID} = {?DocumentID_PROMPT}
Also, if they select Cancel in the Crystal parameter window, it still tries to run the report. How can I stop the report from running?
Thank you for your assistance!
Andyeither is wrong the formula,and is crystal report,doesn't know the field. or...
work with an report object,and put
Public reportApp As CRAXDRT.Application
Public Report As CRAXDRT.Report
reportApp=createobject("CRAXDRT.Application")
report=reportApp.loadReport("...rpt")
...for not showing parameter prompting
Report.EnableParameterPrompting=false
report is an object of craxdrt.dll i thing at least i have worked with this one.this is for not showing the parameters inputs again.
also,it should be existing in the report(in design) a parameter(Insert/Parameter) and the value for that should have your docID value
something like this.
Dim cpars As CRAXDRT.ParameterFieldDefinitions
Dim cpar As CRAXDRT.ParameterFieldDefinition
Set cpar = cpars(1)
cpar.AddCurrentValue (docID)
docID here is your VB variable
Maybe this will work,or try to verify carefully the selection formula,AND in report it should be existing a parameter that will take in the code that value!
Good luck|||Thank you very much for the reply, jasie24!
Passing a subquery as a parameter to a user defined function
I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>
Passing a string report parameter to a stored proc
I have created a report that uses a SQL stored procedure. I am passing 3
parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
created 3 report parameters and I am passing them to the stored proc via the
parameters tab for the dataset. In visual studio the closest option to
NVARCHAR I have for the report parameter is string. If I run the sp from the
data area and pass it the params it works fine but when I try to preview the
report and enter the params I get no data (also no error message or anything)
I'm thinking I may have to do some sort of conversion but I'm not sure what
it would be. Any help would be aprrciated.Be sure the store proc parameter is defined in your Report parameters list.
"Jan Shulse" wrote:
> I have created a report that uses a SQL stored procedure. I am passing 3
> parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
> created 3 report parameters and I am passing them to the stored proc via the
> parameters tab for the dataset. In visual studio the closest option to
> NVARCHAR I have for the report parameter is string. If I run the sp from the
> data area and pass it the params it works fine but when I try to preview the
> report and enter the params I get no data (also no error message or anything)
> I'm thinking I may have to do some sort of conversion but I'm not sure what
> it would be. Any help would be aprrciated.
parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
created 3 report parameters and I am passing them to the stored proc via the
parameters tab for the dataset. In visual studio the closest option to
NVARCHAR I have for the report parameter is string. If I run the sp from the
data area and pass it the params it works fine but when I try to preview the
report and enter the params I get no data (also no error message or anything)
I'm thinking I may have to do some sort of conversion but I'm not sure what
it would be. Any help would be aprrciated.Be sure the store proc parameter is defined in your Report parameters list.
"Jan Shulse" wrote:
> I have created a report that uses a SQL stored procedure. I am passing 3
> parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
> created 3 report parameters and I am passing them to the stored proc via the
> parameters tab for the dataset. In visual studio the closest option to
> NVARCHAR I have for the report parameter is string. If I run the sp from the
> data area and pass it the params it works fine but when I try to preview the
> report and enter the params I get no data (also no error message or anything)
> I'm thinking I may have to do some sort of conversion but I'm not sure what
> it would be. Any help would be aprrciated.
Passing a String into the InsertCommand of SqlDataSource at the @color character
Ok, so I'm a JSP guy and thing it should be easy to replace "@.color" with t_color after I initialized it to red by
String t_color = "red";
and then calling the insert
SqlDataSource1.Insert();
here is insert command:
InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"
I've tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"
Ive tried InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "<%$ t_color %>" )"
Is there any easy way to do this?
or
Can I set it like
@.color = t_color?
Thanks in advance for ANY help
JSP turning ASP (Maybe)
Dan
Hi Dan,
Hope your day goes better!
If you are using @.something, this tells your database that a parameter is expected, in which case you would instantiate a new parameter and sent your string value.
your line would have worked (this one): InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, "+ t_color+")"
had you remembered to use single-quotes around your color value (strings are interpreted in sql if they have single quotes around them)
so it would have been like this:
InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@.name,'"+ t_color+"')"
it know its hard to see, but in the end, the insertcommand recieved by your database server is...
INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, 'red') -- which would have worked.
hope this helps!!
|||DARN!
THought that was it.
However, when I try '"+ t_color+"' i getParser Error Message:The server tag is not well formed.
when I try "'+ t_color+'" I getParser Error Message:The server tag is not well formed.
How avout setting the value of @.color to 'red' ?
Is that an option?
Sorry Im so green at this c# stuff
In your GUI, look at the properties of the sqldatasource. click the [...] button for your insert command, and check to see if you have your parameters already created there.
if so, you can set your parameter value like this...
sdsMySQLDataSource.InsertParameters[
"color"].DefaultValue = t_string;If you don't have any parameters, then you should add them using the GUI... you can do it programmatically though like this...
sdsMySQLDataSource.InsertParameters.Add(
newParameter("color",TypeCode.String,"black"));|||Incase you want to see my test code on this issue... Hereis the test code that I usedin testing your issue. it compiled nicely (I just don't have the tables to actually write the data to... cant test that)// add a parameter to the insert query string t_color = "red"; string myInsertSQL;// parameters myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, @.color)"; sdsTestFiltering.InsertParameters.Add(new Parameter("color", TypeCode.String, "black")); sdsTestFiltering.InsertParameters["color"].DefaultValue = t_color;// sql injection myInsertSQL = "INSERT INTO [favcolor] ([name], [color]) VALUES (@.name, '" + t_color + "')"; sdsTestFiltering.InsertCommand = myInsertSQL;
Passing a string for IN clause
Hello,
How do I pass a string parameter to a Stored Procedure with an 'in' clause?
ex.
Select * FROM Persons
WHERE PersonID IN (1,2,3,4,5,20,56,80)
How do I define my Store Procedure so I can pass the values between () as a string (nvarchar) parameter to the SqlCommand?
Thanks,
WYou would have to use dynamic SQL (create a string witht he values to create a SQL command) or pass in a delimited string, and then create a function to split the values and return a table, and use that returned table object in the IN clause.
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. )
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. )
Subscribe to:
Posts (Atom)