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 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 ','.
>

Passing a subquery as a parameter to a user defined function

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 tabl
e.
> 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 ','.|||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 tabl
e.
> 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:
>

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.

passing a boolean parameter to a package

hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE.

i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work.

what can i do?

thnxs

kats wrote:

hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE.

i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work.

what can i do?

thnxs

Boolean values are either 0 or 1. Not True or False. True or False is a presentation layer feature. Pass in a 0 for false or a 1 for true and things should work just fine.

Passed SQL Parms Not Working

I have the following Query:
SELECT T12_R
FROM SUMMARY
WHERE (DECILE = '@.SQLDecile')
I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in the
report parameters. The dataset parameters use '@.SQLDecile' and points to the
report parameters variable 'SQLDecile'. When I run the query in the data tab
it prompts me for the parm and I enter it and it works.
I know the report parm of SQLDecile is getting the correct value because I
am displaying it on a form field also.
When I preview the chart it returns no data!!
Am I right to assume I use the same variable for the report parm and the
data parm and point the report parm to the data parm in the dataset parm
screen?
Thanks for any help!
GeorgeI'm surprized it works in the data tab. Try to remove apostropthes from
around @.SQLDecile and see if it works.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"George" <George@.discussions.microsoft.com> wrote in message
news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
>I have the following Query:
> SELECT T12_R
> FROM SUMMARY
> WHERE (DECILE = '@.SQLDecile')
> I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in
> the
> report parameters. The dataset parameters use '@.SQLDecile' and points to
> the
> report parameters variable 'SQLDecile'. When I run the query in the data
> tab
> it prompts me for the parm and I enter it and it works.
> I know the report parm of SQLDecile is getting the correct value because I
> am displaying it on a form field also.
> When I preview the chart it returns no data!!
> Am I right to assume I use the same variable for the report parm and the
> data parm and point the report parm to the data parm in the dataset parm
> screen?
> Thanks for any help!
> George
>|||Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
would it not want them there? I thought RS would just replace my parm with
the data so the sql statement would be there. Or does RS add the apostropthes
on its own?
"Lev Semenets [MSFT]" wrote:
> I'm surprized it works in the data tab. Try to remove apostropthes from
> around @.SQLDecile and see if it works.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
> >I have the following Query:
> >
> > SELECT T12_R
> > FROM SUMMARY
> > WHERE (DECILE = '@.SQLDecile')
> >
> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile' in
> > the
> > report parameters. The dataset parameters use '@.SQLDecile' and points to
> > the
> > report parameters variable 'SQLDecile'. When I run the query in the data
> > tab
> > it prompts me for the parm and I enter it and it works.
> >
> > I know the report parm of SQLDecile is getting the correct value because I
> > am displaying it on a form field also.
> >
> > When I preview the chart it returns no data!!
> >
> > Am I right to assume I use the same variable for the report parm and the
> > data parm and point the report parm to the data parm in the dataset parm
> > screen?
> >
> > Thanks for any help!
> >
> > George
> >
>
>|||It is all done by data provider. It substitutes parameter values in its own
way, without requiring apostrophes.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"George" <George@.discussions.microsoft.com> wrote in message
news:49E1C902-A3B0-4B53-B2F8-44A9CEE829CB@.microsoft.com...
> Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
> would it not want them there? I thought RS would just replace my parm with
> the data so the sql statement would be there. Or does RS add the
> apostropthes
> on its own?
> "Lev Semenets [MSFT]" wrote:
>> I'm surprized it works in the data tab. Try to remove apostropthes from
>> around @.SQLDecile and see if it works.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
>> >I have the following Query:
>> >
>> > SELECT T12_R
>> > FROM SUMMARY
>> > WHERE (DECILE = '@.SQLDecile')
>> >
>> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile'
>> > in
>> > the
>> > report parameters. The dataset parameters use '@.SQLDecile' and points
>> > to
>> > the
>> > report parameters variable 'SQLDecile'. When I run the query in the
>> > data
>> > tab
>> > it prompts me for the parm and I enter it and it works.
>> >
>> > I know the report parm of SQLDecile is getting the correct value
>> > because I
>> > am displaying it on a form field also.
>> >
>> > When I preview the chart it returns no data!!
>> >
>> > Am I right to assume I use the same variable for the report parm and
>> > the
>> > data parm and point the report parm to the data parm in the dataset
>> > parm
>> > screen?
>> >
>> > Thanks for any help!
>> >
>> > George
>> >
>>|||It worked!! Thanks so much for your help!
"Lev Semenets [MSFT]" wrote:
> It is all done by data provider. It substitutes parameter values in its own
> way, without requiring apostrophes.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:49E1C902-A3B0-4B53-B2F8-44A9CEE829CB@.microsoft.com...
> > Thanks. I'll try it. But SQL uses the apostropthes in a normal query. Why
> > would it not want them there? I thought RS would just replace my parm with
> > the data so the sql statement would be there. Or does RS add the
> > apostropthes
> > on its own?
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> I'm surprized it works in the data tab. Try to remove apostropthes from
> >> around @.SQLDecile and see if it works.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:FF5222C0-C9D1-4B90-A537-7E957C0DB3FA@.microsoft.com...
> >> >I have the following Query:
> >> >
> >> > SELECT T12_R
> >> > FROM SUMMARY
> >> > WHERE (DECILE = '@.SQLDecile')
> >> >
> >> > I have defined '@.SQLDecile' in the dataset parameters and 'SQLDecile'
> >> > in
> >> > the
> >> > report parameters. The dataset parameters use '@.SQLDecile' and points
> >> > to
> >> > the
> >> > report parameters variable 'SQLDecile'. When I run the query in the
> >> > data
> >> > tab
> >> > it prompts me for the parm and I enter it and it works.
> >> >
> >> > I know the report parm of SQLDecile is getting the correct value
> >> > because I
> >> > am displaying it on a form field also.
> >> >
> >> > When I preview the chart it returns no data!!
> >> >
> >> > Am I right to assume I use the same variable for the report parm and
> >> > the
> >> > data parm and point the report parm to the data parm in the dataset
> >> > parm
> >> > screen?
> >> >
> >> > Thanks for any help!
> >> >
> >> > George
> >> >
> >>
> >>
> >>
>
>

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>

pass a parameter to a SqlDataSource

I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?

SubmitForm.ascx page:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string..... %>"
SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

SubmitForm.ascx.vb page:

Private ProgramID as string = "25"

Public ReadOnly Property ProgramID() As String
Get
Return _ProgramID
End Get
End Property

Thanks

Jason

Hello my friend,

If this is what you want to do, take the DefaultValue attribute out of the aspx page and use the following in your code behind: -

sqlDataSource2.SelectParameters["programID"].DefaultValue = ProgramID;

Kind regards

Scotty

|||

worked. Thank you.

Jason