Dynamic Scripting for USE [DB]

Greetings,

I am having a difficult time figuring out a way to get this to work. The scripting executes without error, but does nothing. What I want it to do is change the connection to the database retrieved by the cursor.

DECLARE
@.PAR1 VARCHAR (256);
DECLARE DB_PAR CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST';

OPEN DB_PAR
FETCH DB_PAR INTO @.PAR1
BEGIN
EXECUTE ('USE ' + @.PAR1 +';')
PRINT @.PAR1 /*Show what the par value is */
END;
CLOSE DB_PAR;
DEALLOCATE DB_PAR;

Anyone have any ideas?

Something else I thought of. Oracle provides the ability to change the database connection by issuing "CONN USER/PASSWORD@.INSTANCE". I suppose SQL Server might provide something like this in SQLCMD, but I'm not using that right now.

Hi,

dioes the query SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST'; bring something back ? I guess not, right ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Yes, it does return a value. The database that I am looking for is "jeff_hrn_test" and taht is what returns in the variable when I print it.

If you run SELECT * FROM SYS.SYSDATABASES on any database within a SQL Server instance, it returns the full listing of all Databases on that Server. I just am looking for a specific name. It must contain HRN and TEST in the name of the Database.

So, it has a value, but the dynamic SQL doesn't actually do anything from what I can see.

|||

I have been able to determine an answer, potentially.

The problem is that I am executing this within dynamic sql. So, as soon as the execute completes, the context switches back.

Therefore, if I want to set the context, then run my next block of SQL Code, I have to make it into one gigantic EXECUTE!

I'm hoping to find a better way to do this. FRUSTRATING!!

By the way, Here's a sample code block:

DECLARE @.DB VARCHAR (50);
SET @.DB=(SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST')
EXECUTE('USE [' + @.DB + '];
SELECT * FROM TABLE')

Dynamic Query expression

I am receiving the "expression expected" error when trying to include the
following in an expression for a sql statement. I don't receive it without
the IIF statement & do if I include it:
"AND (table.field = @.Team OR @.Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = 3 ")
" ORDER BY Year, Weeknum, OpenDate"
Thank you for any help.
--CorySorry if this is too obvious, but have you remembered the 1st '='?
Your expression should read
="AND (table.field = @.Team OR @.Team = '(All Teams') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
= 3 ")
" ORDER BY Year, Weeknum, OpenDate"|||Hi. I only sent part of the query... Here's more of the query - I just
removed field names etc. Everything works until I add in the iif statement.
="SELECT " &
"table.field, table.field etc" &
"FROM table " &
"GROUP BY table.field, table.field etc" &
"HAVING table.field IN ('text value') " &
"AND table.date >= @.StartDate " &
"AND table.date <= @.EndDate " &
"AND (table.field = @.Team OR @.Team = '(All Teams)') " &
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = " &
Parameters!Category.Value & "")
" ORDER BY Year, Weeknum, OpenDate"
"TomP" wrote:
> Sorry if this is too obvious, but have you remembered the 1st '='?
> Your expression should read
> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> = 3 ")
> " ORDER BY Year, Weeknum, OpenDate"
>|||What is biting you here is typical of dynamic sql. I assume category is a
string. It needs to be enclosed in single quotes. When you have a query
paramter RS is handling all of this for you. When you assemble the string
yourself you need to enclude any necessary single quotes.
AND table.field = '" & Parameters!Category.Value & "'")
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> Hi. I only sent part of the query... Here's more of the query - I just
> removed field names etc. Everything works until I add in the iif
> statement.
> ="SELECT " &
> "table.field, table.field etc" &
> "FROM table " &
> "GROUP BY table.field, table.field etc" &
> "HAVING table.field IN ('text value') " &
> "AND table.date >= @.StartDate " &
> "AND table.date <= @.EndDate " &
> "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = "
> &
> Parameters!Category.Value & "")
> " ORDER BY Year, Weeknum, OpenDate"
> "TomP" wrote:
>> Sorry if this is too obvious, but have you remembered the 1st '='?
>> Your expression should read
>> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
>> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> = 3 ")
>> " ORDER BY Year, Weeknum, OpenDate"
>>|||Do you have to use the value of the parameter in the = statement?
What I have is a situtation where the user is allowed to choose from:
All Categories
Exclude Category 3
Only Category 3
The actual value of the field being used in for query is the character 3
(varchar).
So what I ended out trying was the following:
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '" & 3 & "'")
and I also tried:
IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.category_id
= '3'")
but neither worked. I can't use the actual value of the parameter because
I'm going to need 2 if statements, one that is equal to 3 and one that is not
equal to 3. So 3 would need to be the value twice... which of course won't
work.
I get the feeling I'm making this more complicated than necessary. If you
have suggestions I'll take any.
Thanks again.
--Cory
"Bruce L-C [MVP]" wrote:
> What is biting you here is typical of dynamic sql. I assume category is a
> string. It needs to be enclosed in single quotes. When you have a query
> paramter RS is handling all of this for you. When you assemble the string
> yourself you need to enclude any necessary single quotes.
> AND table.field = '" & Parameters!Category.Value & "'")
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> > Hi. I only sent part of the query... Here's more of the query - I just
> > removed field names etc. Everything works until I add in the iif
> > statement.
> >
> > ="SELECT " &
> > "table.field, table.field etc" &
> > "FROM table " &
> > "GROUP BY table.field, table.field etc" &
> > "HAVING table.field IN ('text value') " &
> > "AND table.date >= @.StartDate " &
> > "AND table.date <= @.EndDate " &
> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field = "
> > &
> > Parameters!Category.Value & "")
> > " ORDER BY Year, Weeknum, OpenDate"
> >
> > "TomP" wrote:
> >
> >> Sorry if this is too obvious, but have you remembered the 1st '='?
> >>
> >> Your expression should read
> >>
> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> = 3 ")
> >> " ORDER BY Year, Weeknum, OpenDate"
> >>
> >>
>
>|||Those are the labels, not the value correct? Remember that the values do not
have to equal label. You can accomplish this without dynamic sql.
I have three label, value pairs: All Categories, 0 Exclude Category
3, -1 Only Category 3
myfield = @.CategoryParam or @.CategoryParam = 0 or (@.CategoryParam = -1 and
myfield != 3)
For the above to work 0 and -1 have to not be valid categories.
So, take a look at the three choices. If you select All Categories then
@.CategoryParam will = 0 and all your categories will returned. If Only
Category 3 is selected then @.CategoryParam will = 3. Then finally the last
one handles returning everything except where the category equal 3.
Now you can totally get away from dynamic SQL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cory" <Cory@.discussions.microsoft.com> wrote in message
news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> Do you have to use the value of the parameter in the = statement?
> What I have is a situtation where the user is allowed to choose from:
> All Categories
> Exclude Category 3
> Only Category 3
> The actual value of the field being used in for query is the character 3
> (varchar).
> So what I ended out trying was the following:
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> table.category_id
> = '" & 3 & "'")
> and I also tried:
> IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> table.category_id
> = '3'")
> but neither worked. I can't use the actual value of the parameter because
> I'm going to need 2 if statements, one that is equal to 3 and one that is
> not
> equal to 3. So 3 would need to be the value twice... which of course
> won't
> work.
> I get the feeling I'm making this more complicated than necessary. If you
> have suggestions I'll take any.
> Thanks again.
> --Cory
> "Bruce L-C [MVP]" wrote:
>> What is biting you here is typical of dynamic sql. I assume category is a
>> string. It needs to be enclosed in single quotes. When you have a query
>> paramter RS is handling all of this for you. When you assemble the string
>> yourself you need to enclude any necessary single quotes.
>> AND table.field = '" & Parameters!Category.Value & "'")
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Cory" <Cory@.discussions.microsoft.com> wrote in message
>> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
>> > Hi. I only sent part of the query... Here's more of the query - I just
>> > removed field names etc. Everything works until I add in the iif
>> > statement.
>> >
>> > ="SELECT " &
>> > "table.field, table.field etc" &
>> > "FROM table " &
>> > "GROUP BY table.field, table.field etc" &
>> > "HAVING table.field IN ('text value') " &
>> > "AND table.date >= @.StartDate " &
>> > "AND table.date <= @.EndDate " &
>> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
>> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> > = "
>> > &
>> > Parameters!Category.Value & "")
>> > " ORDER BY Year, Weeknum, OpenDate"
>> >
>> > "TomP" wrote:
>> >
>> >> Sorry if this is too obvious, but have you remembered the 1st '='?
>> >>
>> >> Your expression should read
>> >>
>> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
>> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
>> >> = 3 ")
>> >> " ORDER BY Year, Weeknum, OpenDate"
>> >>
>> >>
>>|||Thank you Bruce. That was exactly what I was looking for. I had a suspicion
I was making it more difficult than necessary.
Thanks again.
--Cory
"Bruce L-C [MVP]" wrote:
> Those are the labels, not the value correct? Remember that the values do not
> have to equal label. You can accomplish this without dynamic sql.
> I have three label, value pairs: All Categories, 0 Exclude Category
> 3, -1 Only Category 3
> myfield = @.CategoryParam or @.CategoryParam = 0 or (@.CategoryParam = -1 and
> myfield != 3)
> For the above to work 0 and -1 have to not be valid categories.
> So, take a look at the three choices. If you select All Categories then
> @.CategoryParam will = 0 and all your categories will returned. If Only
> Category 3 is selected then @.CategoryParam will = 3. Then finally the last
> one handles returning everything except where the category equal 3.
> Now you can totally get away from dynamic SQL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> news:53CF6DCA-0BDA-4FB4-B9BD-B646D9246CCF@.microsoft.com...
> > Do you have to use the value of the parameter in the = statement?
> >
> > What I have is a situtation where the user is allowed to choose from:
> >
> > All Categories
> > Exclude Category 3
> > Only Category 3
> >
> > The actual value of the field being used in for query is the character 3
> > (varchar).
> >
> > So what I ended out trying was the following:
> >
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> > table.category_id
> > = '" & 3 & "'")
> >
> > and I also tried:
> >
> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND
> > table.category_id
> > = '3'")
> >
> > but neither worked. I can't use the actual value of the parameter because
> > I'm going to need 2 if statements, one that is equal to 3 and one that is
> > not
> > equal to 3. So 3 would need to be the value twice... which of course
> > won't
> > work.
> >
> > I get the feeling I'm making this more complicated than necessary. If you
> > have suggestions I'll take any.
> >
> > Thanks again.
> >
> > --Cory
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What is biting you here is typical of dynamic sql. I assume category is a
> >> string. It needs to be enclosed in single quotes. When you have a query
> >> paramter RS is handling all of this for you. When you assemble the string
> >> yourself you need to enclude any necessary single quotes.
> >>
> >> AND table.field = '" & Parameters!Category.Value & "'")
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Cory" <Cory@.discussions.microsoft.com> wrote in message
> >> news:E6AEFEFE-4EBE-4280-AA24-5CCDDC4E0E43@.microsoft.com...
> >> > Hi. I only sent part of the query... Here's more of the query - I just
> >> > removed field names etc. Everything works until I add in the iif
> >> > statement.
> >> >
> >> > ="SELECT " &
> >> > "table.field, table.field etc" &
> >> > "FROM table " &
> >> > "GROUP BY table.field, table.field etc" &
> >> > "HAVING table.field IN ('text value') " &
> >> > "AND table.date >= @.StartDate " &
> >> > "AND table.date <= @.EndDate " &
> >> > "AND (table.field = @.Team OR @.Team = '(All Teams)') " &
> >> > IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> > = "
> >> > &
> >> > Parameters!Category.Value & "")
> >> > " ORDER BY Year, Weeknum, OpenDate"
> >> >
> >> > "TomP" wrote:
> >> >
> >> >> Sorry if this is too obvious, but have you remembered the 1st '='?
> >> >>
> >> >> Your expression should read
> >> >>
> >> >> ="AND (table.field = @.Team OR @.Team = '(All Teams') " &
> >> >> IIF(Parameters!Category.Value = 'Only Category 3',""," AND table.field
> >> >> = 3 ")
> >> >> " ORDER BY Year, Weeknum, OpenDate"
> >> >>
> >> >>
> >>
> >>
> >>
>
>

dynamic query "expression expected" error

I'm using the following SQL and receiving: "The expression for the
query 'NOL' contains an error: [BC30201] Expression expected."
="SELECT C.CardholderLastName, C.CardholderFirstName,
C.CardNumberLast4Digits, C.CUDiscoveryDate,
C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE"
I've looked over all the message related to Dynamic Queries and from
what I can tell I'm doing this correctly, but for some reason it's not
working. I can remove the IIF and after and it works fine, I'm sure
its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
string value.
ThanksDoes this work?
SELECT C.CARDHOLDERLASTNAME,
C.CARDHOLDERFIRSTNAME,
C.CARDNUMBERLAST4DIGITS,
C.CUDISCOVERYDATE,
C.ESTIMATEDLOSSAMOUNT_QUANTITY LOSSAMOUNT,
C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'FraudTypeGrouping1'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.FRAUDTYPE)) AS FRAUDTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'TransactionType'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) AS TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'BankCardProgram'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.CARDPROGRAM)) AS CARDPROGRAM,
'ALL' AS ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C,
APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE
" &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', ", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE
formatting thanks to
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
I don't think it will, I think the comma in the IIF before the AND is the
problem.
Steve MunLeeuw
<kelkoenig@.gmail.com> wrote in message
news:1139434326.076692.110810@.g43g2000cwa.googlegroups.com...
> I'm using the following SQL and receiving: "The expression for the
> query 'NOL' contains an error: [BC30201] Expression expected."
> ="SELECT C.CardholderLastName, C.CardholderFirstName,
> C.CardNumberLast4Digits, C.CUDiscoveryDate,
> C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
> FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
> WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
> IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
> C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
> ORDER BY C.CUSTOMERREFERENCE"
> I've looked over all the message related to Dynamic Queries and from
> what I can tell I'm doing this correctly, but for some reason it's not
> working. I can remove the IIF and after and it works fine, I'm sure
> its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
> string value.
> Thanks
>|||Nope, no luck, still same error w/in Reporting Services. I had to add
the = and "s at the beginning and end of the statement, unless you left
those out intentionally?
THanks|||Try enclosing the word ALL in double quotes instead of single in your
original. SRS doesn't like single quotes at all!
kelkoenig@.gmail.com wrote:
> Nope, no luck, still same error w/in Reporting Services. I had to add
> the = and "s at the beginning and end of the statement, unless you left
> those out intentionally?
> THanks|||Thanks a ton Toolman, that worked like a charm.
kelsql

dynamic Query - ODBC connectivity

I am trying to create Dynamic Query.Connecting to Sybase database source through ODBC, but I try to pass Paremeter by using @.. It says error. Also If i pass &Parameters!Parametername.Value is also not working.Its not saying error for string values but for numeric it says reror. Also in the Query builder i am not able to give expression by giving = "select some Query".

I fgiven an solution or example would be great. Thanks in advance.

I'm not sure but ... does ODBC support named parameters?
Try using ? as parameter, not a questionmark as prefix but like this:

select *
from bla
where something = ?

|||

ODBC is not supporting naming parameter.

I tried with ? working to pass parameter to my query

It was named as Paramater1 when i preview the report and automatically taking the value as string format.Can we change the description and data type?

But I am trying to use Report parameter. i.e. I am getting the values in combo box if in preview the values i select in the query.

But i am unable to use the Report parametr in the query. could anyone help out passing report parameter to sysbase ODBC query

Thanks Hypo for you valuable respons.

dynamic query

Hello,

Could anyone please tell me the error in my query...I use sql reporting 2005.

select * from table1 T

where (T.startdate >= Parameters!Startdate.value and

T.startdate <= Parameters!Enddate.value)

The report runs good but does not return any rows.

Thanks,

asiaindian

Hi,

I got it.....

sql

Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."

>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

dynamic pivot table

hi

i am trying to create a dynamic sql statement so i can pivot information...

my problem is that when i run this query i get the error information below. i am running sql server 2000... can this run in 2005?

this runs perfectly when only one record is returned in the sub query...

declare @.query varchar(300)
select @.query = 'Select '+ char(10) + (
select Code
+'case when Code = '''+ Code +''' then count(Code) else 0 end as '+Code+''+char(10)
as [text()]

from WipAvailable WA
)+ char(10) +
' from WipMaster group by Code'

select @.query
exec (@.query)

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

u have given the answer..."this runs perfectly when only one record is returned in the sub query...", in this case the outer query just expects one result to be returned by the subquery... and it'll give the same error in 2005 as well... u need to look at the logic...

i dont know ur exact aim..if pivot is the aim , there is a pivot operator u can use in sql server 2005, to make row data into columns...

|||

if you are using the sql server 2005 then use Pivot operator,

example,

SELECT
[1] as [Code-1],
[2] as [Code-2],
[3] as [Code-3]
FROM
(SELECT Code from WipAvailable) Master
PIVOT
(Count(Code) for Code in ([1],[2],[3]))
AS pvt

to achive the above query dynamically use the following statement,

Declare @.Columns as varchar(1000);
Declare @.Values as varchar(1000);
Declare @.Query as varchar(3000);
select
@.Columns = Isnull(@.Columns,'') + '[' + Code + '] as [Code-' + Code + '],' ,
@.Values = Isnull(@.Values,'') + '[' + Code + '],'
From WipMaster

select @.Columns = Substring(@.Columns,1,Len(@.Columns)-1),
@.Values = Substring(@.Values,1,Len(@.Values)-1)

select @.Query = 'Select ' + @.Columns + ' From (Select Code From WipAvailable) Master'
+ ' Pivot (Count(Code) for Code in (' + @.values + ')) as Pvt'

Exec (@.Query)

|||

To work on SQL Server 2000,

Use the following query

Select
(Select count(Code) from WipAvailable where Code=1) as [1]
,(Select count(Code) from WipAvailable where Code=2) as [2]
,(Select count(Code) from WipAvailable where Code=3) as [3]

To achive the above query dynamically use the following statement

Declare @.query varchar(8000)
select @.query = Isnull(@.query,'') + '(Select count(Code) '
+ char(10) +
' from WipAvailable where Code=' + Code + ') as [' + Code + '],'
from Wipmaster Master
Order By Code
select @.query = 'Select ' + Substring(@.query,1,Len(@.query)-1)
Exec (@.query)

Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I'll edit the page to make clearer, but I've only used
the MYUDF(HOST_NAME()) solution for scalars.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll need to check this out in SQL 2005 beta to see if it's fixed or if
anyone can give a reason why ...
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split('1, 2', ','))
compile (and runs), but
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split(HOST_NAME(), ','))
does not, whereas
SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
compiles again.
It's nothing to do with replication per se, but how the complier is parsing
the statements.
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
> Paul,
> I'll edit the page to make clearer, but I've only used
> the MYUDF(HOST_NAME()) solution for scalars.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||why don't you put the hostname within your function as opposed to supplying
it as an argument.
"Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
> anyone can give a reason why ...
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split('1, 2', ','))
> compile (and runs), but
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split(HOST_NAME(), ','))
> does not, whereas
> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
> compiles again.
> It's nothing to do with replication per se, but how the complier is
> parsing the statements.
> Thanks
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
>
|||Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.
I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.
Thanks
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX8lbLaxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
> "Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec' Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC' THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount offIf you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec'
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC'
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get th
e
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü?meyer" wrote:

> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
>
>|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:

> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec' Meaning
,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, tha
t
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC' THank
s,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =
coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.err
or
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
>

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec? Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC? THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount off
If you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec?
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC?
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>
|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get the
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü?meyer" wrote:

> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
>
>
|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:

> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec? Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC? THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>
|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =
coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:

Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec' Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC' THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount offIf you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec'
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC'
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get the
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü�meyer" wrote:
> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> > When doing a basic EXEC statement, is there any straight-forward way of
> > dealing with error handling about the statement inside the exec'
> > Meaning,
> > if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> > not that the code WITHIN the EXEC ran ok. I threw this example below,
> > that
> > does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> > a
> > #table works, but this seems a bit much to code for. Any other ideas to
> > shorten the code but be able to do error handling within the EXEC'
> > THanks,
> > Bruce
> >
> > set nocount on
> > drop table #err
> > create table #err (error_no int)
> > declare @.cmd varchar(255), @.errno int
> > select @.errno = 0
> > select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> > char(39) + 'mroXXXir' + char(39)
> > + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> > (error_no) select @.errno_2'
> > select @.cmd
> > exec (@.cmd)
> > select @.errno = @.@.error
> > select @.errno as 'Error Code from the EXEC'
> > select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> > set nocount off
> >
> >
>
>|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec' Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC' THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
> > When doing a basic EXEC statement, is there any straight-forward way of
> > dealing with error handling about the statement inside the exec' Meaning,
> > if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> > not that the code WITHIN the EXEC ran ok. I threw this example below, that
> > does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
> > #table works, but this seems a bit much to code for. Any other ideas to
> > shorten the code but be able to do error handling within the EXEC' THanks,
> > Bruce
> >
> > set nocount on
> > drop table #err
> > create table #err (error_no int)
> > declare @.cmd varchar(255), @.errno int
> > select @.errno = 0
> > select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> > char(39) + 'mroXXXir' + char(39)
> > + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> > (error_no) select @.errno_2'
> > select @.cmd
> > exec (@.cmd)
> > select @.errno = @.@.error
> > select @.errno as 'Error Code from the EXEC'
> > select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> > set nocount off
> >
> >

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>

Dynamic conditional report parameters error. SQL Server 2005 SSRS

Given the following dataset:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"
I am trying to dynamically generate a report based on pattype,
facility, and fromdate. The problem I am running into is that it keeps
choking on the fromdate parameter and I don't know why. I keep getting
'Cannot set the command text for data set (above data set)'.
Is it a problem with syntax? Am I not typecasting correctly? The data
type for 'fromdate' in the database is DateTime.
I'm wondering if this might not be possible to do, given the first two
lines in the WHERE statement use @. parameters and the IIF statement
doesn't...
Any advice would be greatly appreciated. Thank you...Correction on the SELECT statement:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value = "","", "AND fromdate = " & <--
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"|||Two things, first, you are assembling a string. When trying to debug this
what I suggest is to have a report with the report parameters and a single
textbox that you assign this expression to so you can see the resulting
string. This helps you figure out what is going on.
One of the things you are doing wrong is with your @.pattype and @.facility.
You have that embedded in your string, RS will not be replacing these for
you. What you need to do is this:
"WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") & ")) " &
Here is a summary from Robert a MS employee on a variety of expressions for
multi-value parameters.
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue boolean flag - tells if a parameter is
defined as multi value
=Parameters!MVP1.Count returns the number of values in the array
=Parameters!MVP1.Value(0) returns the first selected value
=Join(Parameters!MVP1.Value) creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ") creates a comma separated list of
values
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178558124.887441.311130@.y5g2000hsa.googlegroups.com...
> Given the following dataset:
> ="Select * " &
> "From pat " &
> "WHERE (pattype IN (@.pattype)) " &
> "AND (facility IN (@.facility)) " &
> IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
> CDate(Parameters!fromdate.Value)) &
> "Order By pattype,facility"
> I am trying to dynamically generate a report based on pattype,
> facility, and fromdate. The problem I am running into is that it keeps
> choking on the fromdate parameter and I don't know why. I keep getting
> 'Cannot set the command text for data set (above data set)'.
> Is it a problem with syntax? Am I not typecasting correctly? The data
> type for 'fromdate' in the database is DateTime.
> I'm wondering if this might not be possible to do, given the first two
> lines in the WHERE statement use @. parameters and the IIF statement
> doesn't...
> Any advice would be greatly appreciated. Thank you...
>|||First of all, thank you. I appreciate your reply.
Now that you mention it, that makes obvious sense. I overlooked that
while I was going through the Books Online tutorials and I feel they
just 'jumped' too quickly without describing it the way you did. So
thanks for that.
The problem I'm running into now is that I am at this point:
="Select *" &
" From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
")) " &
" AND (facility IN (" & Join(Parameters!facility.Value, ",
") & "))" &
Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
(fromdate IN (" & Parameters!fromdate.Value & ")) Order By
pattype,facility,fromdate")
I'm still getting command text errors though. Can you offer any other
thoughts? Perhaps elaborate on how you use a textbox for a resulting
string (or is this useful after I get past my current problem?)? Or
can you point me to other tutorials that get more involved with what I
am trying to do? Books online don't really go deep enough.
Thanks again for your help. I assure you I am rigorously working on
this. Any help is once again greatly appreciated.
Jay|||The best way to do this is to assign this expression to a textbox so you can
see it (copy the report and delete everything except the parameters and the
textbox).
What I bet is happening is that your data types are strings. That means what
you really want is not a comma separated string. You want single quotes
around it. You could write some code behind reports that you bind the
parameter to that takes the parameter and returns a string all properly
formatted.
I have a suggestion, use the user sortable columns instead of sorting it
yourself this way. Then you would not have to use an expression for your
dataset definition in the first place. Using expressions for this is
generally a pain. For one thing, it will not give you a list of fields. You
have to use a regular sql string to get your field list and then change it
to an expression after than.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178567450.999831.232960@.q75g2000hsh.googlegroups.com...
> First of all, thank you. I appreciate your reply.
> Now that you mention it, that makes obvious sense. I overlooked that
> while I was going through the Books Online tutorials and I feel they
> just 'jumped' too quickly without describing it the way you did. So
> thanks for that.
> The problem I'm running into now is that I am at this point:
> ="Select *" &
> " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
> ")) " &
> " AND (facility IN (" & Join(Parameters!facility.Value, ",
> ") & "))" &
> Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
> (fromdate IN (" & Parameters!fromdate.Value & ")) Order By
> pattype,facility,fromdate")
> I'm still getting command text errors though. Can you offer any other
> thoughts? Perhaps elaborate on how you use a textbox for a resulting
> string (or is this useful after I get past my current problem?)? Or
> can you point me to other tutorials that get more involved with what I
> am trying to do? Books online don't really go deep enough.
> Thanks again for your help. I assure you I am rigorously working on
> this. Any help is once again greatly appreciated.
> Jay
>
>|||OK I think we're getting somewhere now...I hooked up the expression to
a textbox like you advised.
This works:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
This does not work (#error is returned in the textbox):
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
")) "
It appears to be choking on the join statement. I doublechecked the
syntax and I'm pretty sure I'm using it correctly...Do you see
anything wrong by any chance?
I'm going to shop around for an advanced manual I think. Books online
and the Osbourne SSRS manual doesn't go over this area very well at
all...
Thanks again for your help.|||Does the first syntax work or only works if you check a single value?
Also, just in case. I have in the past had problems with carriage returns.
Try putting it all on one line.
I use the Join(Parameters ...) syntax when I am showing the selected
parameters at the top of the report.
You got me curious. I did the following. I created a new report. I put a
single textbox on the report. I copied and pasted your expression which uses
the Join() below. Next I added a parameter called pattype, multi-select,
string and put a few values in.
It ran and it did as I suspected it would do. You end up with this:
Select facility, account, fromdate, thrudate, mednum, last_name, pattype
From pat WHERE (pattype IN (T1,T2,T3))
Note that this would work if the value type was integer but with a value
type of string this is invalid SQL. It needs single quotes around each
parameter.
I tried the one you say works below and it does not work of me. I get a
#Error.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178632816.621829.101750@.e51g2000hsg.googlegroups.com...
> OK I think we're getting somewhere now...I hooked up the expression to
> a textbox like you advised.
> This works:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
> This does not work (#error is returned in the textbox):
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
> ")) "
> It appears to be choking on the join statement. I doublechecked the
> syntax and I'm pretty sure I'm using it correctly...Do you see
> anything wrong by any chance?
> I'm going to shop around for an advanced manual I think. Books online
> and the Osbourne SSRS manual doesn't go over this area very well at
> all...
> Thanks again for your help.
>|||Trying again. I replied but looks like it didn't go through...
I think that's it (the single quotes around the string values). Now I
get a convert to int data type error when the column in the database
is of type char/string.
Is there an example of a formed statement that uses the Join method
with multi selects for strings? I tried [ Join("'" & value & "'",
",") ] but that didn't work. Perhaps I need to create a loop or load
an array?
Bruce, thank you...|||From user Jeje: sure in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:'toto', 'tata', 'tutu'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178802164.886275.281550@.y5g2000hsa.googlegroups.com...
> Trying again. I replied but looks like it didn't go through...
> I think that's it (the single quotes around the string values). Now I
> get a convert to int data type error when the column in the database
> is of type char/string.
> Is there an example of a formed statement that uses the Join method
> with multi selects for strings? I tried [ Join("'" & value & "'",
> ",") ] but that didn't work. Perhaps I need to create a loop or load
> an array?
> Bruce, thank you...
>|||Holy painful lesson...lol.
I got it:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" &
" From pat" &
" WHERE (pattype IN (" & "'" & Join(Parameters!
pattype.Value,"','") & "'" & ")) " &
" AND (facility IN (" & "'" & Join(Parameters!
facility.Value,"','") & "'" & ")) "
Man that made me feel so stupid. My mental block was the fact that I
needed to think from a perspective of VBScript writing SQL/T-SQL...
Well the good news is that now I can throw a few 'Iif' statements on
each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
dropdowns, I can create ONE dynamic statement/report that the user can
do with as they please and report on whatever they want.
Bruce. Thank you for being there and seeing me through this. I
appreciate that.|||Glad you got it to work. Dynamic queries (and getting the single quotes
right) is a pain. If you ever use openquery it gets worse because you need
to double the amount of single quotes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1179330253.302418.191850@.n59g2000hsh.googlegroups.com...
> Holy painful lesson...lol.
> I got it:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" &
> " From pat" &
> " WHERE (pattype IN (" & "'" & Join(Parameters!
> pattype.Value,"','") & "'" & ")) " &
> " AND (facility IN (" & "'" & Join(Parameters!
> facility.Value,"','") & "'" & ")) "
> Man that made me feel so stupid. My mental block was the fact that I
> needed to think from a perspective of VBScript writing SQL/T-SQL...
> Well the good news is that now I can throw a few 'Iif' statements on
> each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
> dropdowns, I can create ONE dynamic statement/report that the user can
> do with as they please and report on whatever they want.
> Bruce. Thank you for being there and seeing me through this. I
> appreciate that.
>|||On May 16, 11:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Glad you got it to work. Dynamic queries (and getting the single quotes
> right) is a pain. If you ever use openquery it gets worse because you need
> to double the amount of single quotes.
Hey one last quick question if you don't mind and I'll stop bugging
you.
I found it once in SQL Server 2005 Books Online/Tutorials, but I put
it down and can't find it anymore. There was a section on how to call
a webservice from SSRS and get the XML back. Have you, by any chance,
come across this?
I even remember what it looked like too. You had to put the webservice
URL in the data source connection string and the XML namespace/wsdl
for the data set (I think) and some other stuff like that.
Thanks Bruce.|||Found it...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-
a98f-234454d5d267.htm

Dy

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting the
varchar value 'SELECT ' to a column of data type int.):
[Code]
DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject varchar(10
)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sAgentid ='0'
SELECT @.sProject = 'A'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
IF @.sAgentId <> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
END
IF @.sProject<> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
END
EXEC (@.SQL)
If I put the @.RC inside the quotes I get error: Must declare the variable
'@.RC'.
I tested by printing the statement and it prints properly just errors when
executing.
How can I make this work?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200507/1EXEC() has its own scope, so has no idea what @.RC is (nor does it "see"
sAgentID or @.sProject).
Try getting rid of the dynamic SQL altogether:
SELECT @.RC = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND COALESCE(sRawLogout, 'x') = 'x'
AND sAgentID = CASE @.sAgentID WHEN 'O' THEN sAgentID ELSE @.sAgentID
END
AND sProject = CASE @.sProject WHEN 'O' THEN sProject ELSE @.sProject
END
Or see http://www.aspfaq.com/2492
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:51A8B62C943CC@.webservertalk.com...
> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting the
> varchar value 'SELECT ' to a column of data type int.):
> [Code]
> DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject
> varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sAgentid ='0'
> SELECT @.sProject = 'A'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> IF @.sAgentId <> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
> END
> IF @.sProject<> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
> END
> EXEC (@.SQL)
> If I put the @.RC inside the quotes I get error: Must declare the variable
> '@.RC'.
> I tested by printing the statement and it prints properly just errors when
> executing.
> How can I make this work?
> Thanks,
> Ninel
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200507/1|||I have a similar problem.
USE MyDataBase
GO
if exists
(select * from sysobjects where id =
object_id(N'[dbo].[sp_MTV_Diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MTV_Diskalert]
GO
create procedure sp_MTV_diskalert (
@.LowerLimit INT
, @.LinkedServerName SYSNAME
, @.NumberOfEmailsToSend INT
, @.EmailAddress1 NVARCHAR(30)
, @.EmailAddress2 NVARCHAR(30))
AS
--
BEGIN
--
SET XACT_ABORT ON
SET NOCOUNT ON
--
DECLARE @.RCPT VARCHAR(500)
, @.LIMIT INT
, @.SQLSTRING NVARCHAR(4000)
, @.STRING2 NVARCHAR(4000)
, @.EMAIL NVARCHAR(4000)
, @.ParmDefinition NVARCHAR(4000)
, @.ParmIn NVARCHAR(4000)
, @.ParmRet NVARCHAR(4000)
, @.MyCounter int
, @.TempEmailAddress NVARCHAR(30)
--
set @.MyCounter = 0
--
--The following stmt works
--
exec (select @.EmailAddress1 as name)
--
while ( @.MyCounter <> @.NumberOfEmailsToSend )
Begin
SET @.MyCounter = @.MyCounter + 1
SET @.TempEmailAddress = '@.EmailAddress'+ltrim(str(@.MyCounter))
SET @.SQLString = N'Select ' +@.TempEmailAddress+ ' as name'
-- print @.SQLstring
EXECUTE sp_executesql
@.SQLString
,@.params = N'@.email nvarchar(30) output'
,@.email=@.email output
END
END
go
EXEC Sealedair.dbo.sp_SAC_diskalert
250,DUNSQL02,1,'Michael.varriale@.sealedair.com','2','3','4','5','6','7','8',
'9','10'
Must declare the variable '@.EmailAddress1'.