Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-value parameter returns syntax error

I've created a report that includes a multi-value parameter, however when
more than one value is seelcted from the list the report produces an error
that references "Incorrect Syntax near ",".
How do I correct this?Show how you are using the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> I've created a report that includes a multi-value parameter, however when
> more than one value is seelcted from the list the report produces an error
> that references "Incorrect Syntax near ",".
> How do I correct this?
>|||Send the Query where you are using this parametre.For Multivalue you
should use "in" keyword instead of "="
hope this might be your problem
Bruce L-C [MVP] wrote:
> Show how you are using the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "StMaas" <StMaas@.discussions.microsoft.com> wrote in message
> news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> > I've created a report that includes a multi-value parameter, however when
> > more than one value is seelcted from the list the report produces an error
> > that references "Incorrect Syntax near ",".
> > How do I correct this?
> >sql

multi-value parameter problem - please help

I get an error (Incorrect syntax near ','.) when I choose more than one value
for any of the multi-valued report parameters in the below query. All
parameters, except scheduled_date and tracking_number, are defined as
multi-value. It may be a problem with strings appending. Any suggestions?
SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
D.tracking_number, B.alternate_id, D.name,
CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
status, D.sent_date, D.scheduled_date, D.owner_user_id
FROM o_dpl_deployment AS D INNER JOIN
o_bas_brand AS B ON D.environment_id = B.environment_id
INNER JOIN
o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
AND B.environment_id = DT.environment_id
WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
AND ((D.tracking_number LIKE @.tracking_number) OR
(@.tracking_number = ''))
AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
AND ((D.scheduled_date >= @.scheduled_date AND
D.scheduled_date < DATEADD(month,1,@.scheduled_date))
OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
>= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
>= GETDATE() - 1 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
>= GETDATE() - 3 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
>= GETDATE() - 7 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
>= GETDATE() - 14 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
>= GETDATE() - 30 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
>= GETDATE() - 60 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9599-12-31')))
AND ((DT.name IN (@.deployment_type_name)) OR
(@.deployment_type_name = '-1'))
AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
AND ((D.owner_user_id IN (@.owner_user_id)) OR
(@.owner_user_id = '-1'))
AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
AND ((D.status IN (@.status)) OR (@.status = '-1'))
AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
UNION
SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
ORDER BY D.scheduled_date DESCYou may want to search in the programming discussion group too. I had a
similar problem.
"Stephanie" wrote:
> I get an error (Incorrect syntax near ','.) when I choose more than one value
> for any of the multi-valued report parameters in the below query. All
> parameters, except scheduled_date and tracking_number, are defined as
> multi-value. It may be a problem with strings appending. Any suggestions?
> SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
> D.tracking_number, B.alternate_id, D.name,
> CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
> status, D.sent_date, D.scheduled_date, D.owner_user_id
> FROM o_dpl_deployment AS D INNER JOIN
> o_bas_brand AS B ON D.environment_id = B.environment_id
> INNER JOIN
> o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
> AND B.environment_id = DT.environment_id
> WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
> AND ((D.tracking_number LIKE @.tracking_number) OR
> (@.tracking_number = ''))
> AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
> AND ((D.scheduled_date >= @.scheduled_date AND
> D.scheduled_date < DATEADD(month,1,@.scheduled_date))
> OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
> >= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
> OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
> >= GETDATE() - 1 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
> >= GETDATE() - 3 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
> >= GETDATE() - 7 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
> >= GETDATE() - 14 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
> >= GETDATE() - 30 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
> >= GETDATE() - 60 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9599-12-31')))
> AND ((DT.name IN (@.deployment_type_name)) OR
> (@.deployment_type_name = '-1'))
> AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
> AND ((D.owner_user_id IN (@.owner_user_id)) OR
> (@.owner_user_id = '-1'))
> AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
> AND ((D.status IN (@.status)) OR (@.status = '-1'))
> AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
> UNION
> SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
> ORDER BY D.scheduled_date DESC
>

Multiserver Job

hello
when we are trying to execute a multiserver job the following error is coming.can u please suggest the exact solution for this

Blocking error preventing further downloads: The specified @.database_name ('ibbflorida') does not exist. [SQLSTATE 42000] (Error 14262) Unable to create the local version of MSX job '0x6160950EA825544CA777E978E09ECC59'. [SQLSTATE 42000] (Error 50000)

thank you.my guess is that your job is referencing a database called 'IBBFLORIDA' and on one of your servers this database does not exist.|||Thanks for responding MR.Paul.

The database Ibbflorida is existing in one of my server but still it is giving error i could not understand what is the problem|||you didn't explain what your job does or attaach any code around this db reference so I am guessing here...

If ther should only be one copy of IBBFlorida tha I suspect that when your job runs on the linked server, the linked server incorrectly assums the IBBFlorida db is local. What happens if you add a server reference. If IBBFlorida is on ServerA and the job is running on ServerB then change all references to IBBFlorida to ServerA.IBBFlorida.|||hi paul
iam sending u the code for your convenience plz check it here i used db1, db2 in place of original databases names is there any problem with procedure.

thank you once again

CREATE procedure dbo.SP_IU_BALANCES
as
DECLARE
@.LV1 VARCHAR(255),
@.LV2 VARCHAR(255),
@.LV3 varchar(9)

declare TABLECURSOR CURSOR
FOR
select F3,F4,F1 from SERVER1.DB1.dbo.TABLE1
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
WHILE @.@.FETCH_STATUS=0
BEGIN
update SERVER2.DB2.dbo.TABLE2
set TABLE2F1 = @.LV1,
TABLE2F2 = @.LV2
where TABLE2F3=@.LV3
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
END;

CLOSE TABLECURSOR;
DEALLOCATE TABLECURSOR;

insert into SERVER1.DB1.dbo.TABLE1(F1, F2, F3, F4, F5)
(select F1, getdate() +1 , F3, 0, F3
from SERVER1.DB1.dbo.TABLE1 where F2 = getdate())|||when you execute this are you still seeing the error from your original post?|||procedure is working fine when we executed manually. but when the same procedure is called in the job then it is giving the error which i mentioned before|||I am drwaing a blank on this one, I will continue to research the problem.

Anyone else have an idea?

Multiserver Administration Master Server (MSX)

Hi
I am trying to delete some jobs on a SQL Server 2000
database that I have taken over and I am getting the
following error : Error 14274 - Cannot delete a job that
originated from an MSX server.
We only have one server in our organisation and I have
checked and it is not set up as an MSX server.
Please could someone tell me how I can delete these jobs.
Many thanksIt sounds like the server has been renamed at some point. You need to update
sysjobs to reflect the current server name in the originating_server column.
You can use the following procedure to do this
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Philip" <anonymous@.discussions.microsoft.com> wrote in message
news:07c001c3d423$352cfaf0$a301280a@.phx.gbl...
> Hi
> I am trying to delete some jobs on a SQL Server 2000
> database that I have taken over and I am getting the
> following error : Error 14274 - Cannot delete a job that
> originated from an MSX server.
> We only have one server in our organisation and I have
> checked and it is not set up as an MSX server.
> Please could someone tell me how I can delete these jobs.
> Many thanks
>sql

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multi-Select Error, easy question

I am trying to show/hide a table based on a multi-select parameter in VS 2005
beta 2.
In the parameter I have the following:
Data Type: String
Multi-value - checked
Label: General Info
Value: GeneralInfo
In the properties for the report I have the following under Visibility:
=IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
When I execute the report and choose the only parameter value I get the
following. (once I get this to work I will add additional parameters)
Error:
Processing Error
"The Hidden expression for the table 'Table_Header' contains an error:
Overload resolution failed because no Public '=' can be called with these
arguments:
Public Shared Operator =(a As String, b As String) As Boolen':
Argument matching parameter 'a' connot convert from 'Object()' to 'String'.
Thanks!!!
--
Thank You!Once you mark a parameter as "multi-value", the .Value property will return
an object[] with all selected values. If only one value is selected, it will
be an object array of length = 1. Object arrays cannot be directly compared
with Strings.
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
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>I am trying to show/hide a table based on a multi-select parameter in VS
>2005
> beta 2.
> In the parameter I have the following:
> Data Type: String
> Multi-value - checked
> Label: General Info
> Value: GeneralInfo
> In the properties for the report I have the following under Visibility:
> =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> When I execute the report and choose the only parameter value I get the
> following. (once I get this to work I will add additional parameters)
>
> Error:
> Processing Error
> "The Hidden expression for the table 'Table_Header' contains an error:
> Overload resolution failed because no Public '=' can be called with these
> arguments:
> Public Shared Operator =(a As String, b As String) As Boolen':
> Argument matching parameter 'a' connot convert from 'Object()' to
> 'String'.
> Thanks!!!
> --
> Thank You!|||Robert, thanks my man, thanks for taking the time to respond. I'll try this
out first thing tomorrow.
Thanks!
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> 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
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Robert, thanks for your reply. Very valuable information for this report and
for my future reports.
You wrote, "Object arrays cannot be directly compared with strings." Do you
know how I could do this 'indirectly'?
Let's say the user selects 'GeneralInfo' and 'Contact Info' in the
multi-select. Do you know of a way to evaluate their selection so I can take
action on it? (such as visability)
Thanks again for your help.
Shane
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> 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
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Hi Robert, don't worry about replying again. I think I figured it out. I
need to split it after I join it, right?
--
Thank You!
"Robert Bruckner [MSFT]" wrote:
> Once you mark a parameter as "multi-value", the .Value property will return
> an object[] with all selected values. If only one value is selected, it will
> be an object array of length = 1. Object arrays cannot be directly compared
> with Strings.
> 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
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
> >I am trying to show/hide a table based on a multi-select parameter in VS
> >2005
> > beta 2.
> >
> > In the parameter I have the following:
> > Data Type: String
> > Multi-value - checked
> > Label: General Info
> > Value: GeneralInfo
> >
> > In the properties for the report I have the following under Visibility:
> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
> >
> > When I execute the report and choose the only parameter value I get the
> > following. (once I get this to work I will add additional parameters)
> >
> >
> > Error:
> > Processing Error
> > "The Hidden expression for the table 'Table_Header' contains an error:
> > Overload resolution failed because no Public '=' can be called with these
> > arguments:
> > Public Shared Operator =(a As String, b As String) As Boolen':
> > Argument matching parameter 'a' connot convert from 'Object()' to
> > 'String'.
> >
> > Thanks!!!
> >
> > --
> > Thank You!
>
>|||Yes. The Join() will create a string from a multi dimensional object array.
The Split() function is the inverse function; it splits the string into a
multi dimensional array.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
news:05BDA0D3-9D01-4C62-A844-99FE9F2BD791@.microsoft.com...
> Hi Robert, don't worry about replying again. I think I figured it out. I
> need to split it after I join it, right?
> --
> Thank You!
>
> "Robert Bruckner [MSFT]" wrote:
>> Once you mark a parameter as "multi-value", the .Value property will
>> return
>> an object[] with all selected values. If only one value is selected, it
>> will
>> be an object array of length = 1. Object arrays cannot be directly
>> compared
>> with Strings.
>> 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
>> =Split("a b c", " ")
>> to create a multi value object array from a string (this can be used
>> e.g. for drillthrough parameters, subreports, or query parameters)
>> See also MSDN:
>> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
>> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Shane Eckel" <ShaneEckel@.discussions.microsoft.com> wrote in message
>> news:5D2D2514-991E-4D75-87AE-DDC546D171A9@.microsoft.com...
>> >I am trying to show/hide a table based on a multi-select parameter in VS
>> >2005
>> > beta 2.
>> >
>> > In the parameter I have the following:
>> > Data Type: String
>> > Multi-value - checked
>> > Label: General Info
>> > Value: GeneralInfo
>> >
>> > In the properties for the report I have the following under Visibility:
>> > =IIF(Parameters!DisplayInfo.Value = "GeneralInfo", False, True)
>> >
>> > When I execute the report and choose the only parameter value I get the
>> > following. (once I get this to work I will add additional parameters)
>> >
>> >
>> > Error:
>> > Processing Error
>> > "The Hidden expression for the table 'Table_Header' contains an error:
>> > Overload resolution failed because no Public '=' can be called with
>> > these
>> > arguments:
>> > Public Shared Operator =(a As String, b As String) As Boolen':
>> > Argument matching parameter 'a' connot convert from 'Object()' to
>> > 'String'.
>> >
>> > Thanks!!!
>> >
>> > --
>> > Thank You!
>>

multiple view create

Hi
How can I create multiple views in a single SQL script ?
(I get an error: 'CREATE VIEW' must be the first statement in a query
batch.)
thanksYou can use the batch terminator, GO, to seperate the batches:
CREATE VIEW xyz
AS
SELECT
..
GO
CREATE VIEW abc
AS
SELECT
..
GO
... etc
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"romy" <romy1000@.hotpop.com> wrote in message
news:e6ml7BM5FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I create multiple views in a single SQL script ?
> (I get an error: 'CREATE VIEW' must be the first statement in a query
> batch.)
>
> thanks
>sql

Multiple Value TextBox

Hi Folks,

I'm trying to assign multiple values to a textbox and I'm receiving an error. The error says, "The value expression for the textbox AcctName contains an error." The first value is account number and the second value is account name. An example follows:

1234 - SPC Travel Agency

My expression for the textbox contains the following:

=Fields!AcctNum.Value + ' - ' + Fields!AcctName.Value

Please help.

Hello,

Try this:

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Hope this helps.

Jarret

|||

Hello:

Jarret thanks for responding however, I figured it out by doing the following:

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

Best regards

|||

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

|||

Jarret wrote:

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

He used & for string concat, while you used + for integer concat/sum though

I do that all the time in SSRS, using + (in T-SQL) instead of & (in VB)

|||

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

|||

Jarret wrote:

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

Thanks Jarret

That's nice to know that it knows how to interpret the + concat as well

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005

I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.

Update Race:

IFNotUPDATE(Edited)

UPDATE Patient

SET Race =(SELECTCASE Racecode

WHEN'API'THEN'Asian, Pacific Islander'

WHEN'BH'THEN'Black, Hispanic'

WHEN'BNH'THEN'Black, Non-Hispanic'

WHEN'HIS'THEN'Hispanic'

WHEN'OTH'THEN'Other, Black & White'

WHEN'WH'THEN'White, Hispanic'

WHEN'WNH'THEN'White, Non-Hispanic'

END)

WHERE id# IN(SELECT id# FROM inserted)

END

Update Religion:

IF Not UPDATE (Edited)

Update Patient

SET Religion = (SELECT CASE Religioncode

WHEN 'C' THEN 'Catholic'

WHEN 'J' THEN 'Jewish'

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Protestant'

WHEN 'U' THEN 'Unknown'

END)

WHERE id# IN (SELECT id# FROM Inserted)

END

why not combining them into one update.

IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
....
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
Rusty
I've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!
|||Hi
"rnbwil@.gmail.com" wrote:

> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John
|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.
|||Hi
"rnbwil@.gmail.com" wrote:

> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John
|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:

> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to d
o
> with it!
> What command have you used to create this database? It seems to me that yo
u
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locatio
ns
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:

> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted
!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
> > Hi,
> > We tried to create a new database on an application server (Win 2003
> > server/SQL Server 2003) and got the following error.
> > error 945...
> > ....
> > device activation error. The physical filename g:\mssqldata
> > \templog.ldf may be incorrect.
> > The problem is that tempdb is on f:\mssql as shown in the database
> > properties and with sp_dbhelp.
> > Poking around in master the real wierdness comes through. sysaltfiles
> > has 2 entries each for tempdb logs and data files. One of them is on
> > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > g: (actually the last two rows in the table).
> > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > the log and data files to the g: drive and one pair of rows points
> > them to the f: drive. The references to g: drive need to go away but
> > I've been googinling for a while now and haven't come up with much.
> > I tried running the alter database command normally used to move
> > tempdb and though it didn't fail, it didn't change anything.
> > Several months ago our software vendor moved tempdb from g: to f: to
> > try and speed it up a bit. Appearantly they messed it up and now have
> > written us off till WE fix it.
> > The entries in sysaltfiles were the only references to g: that turned
> > up (though we didn't look at every table and aren't even remotely sure
> > where other references might be located).
> > Any pointers on getting this corrected would be greatly apprecieated.
> > We thought about trying a reconfigure and restarting but I'm not real
> > hopeful. We also thought about just updating the wrong entries to
> > reflect the right locations but that smacks of kluge.
> > Tangential wierdness is that while trying to isolate the source of g:
> > \mssql in the error I found that in master.sysdevices the file
> > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > \tempdb.mdf.
> > I believe this is from the initial install then while configuring the
> > server it got moved to g: then to f:.
> > HELP!!!
> > Thanks in advance for any input!
> > Rusty
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> > "rnb...@.gmail.com" wrote:
> >
> > > Hi,
> >
> > > We tried to create a new database on an application server (Win 2003
> > > server/SQL Server 2003) and got the following error.
> >
> > > error 945...
> >
> > > ....
> > > device activation error. The physical filename g:\mssqldata
> > > \templog.ldf may be incorrect.
> >
> > > The problem is that tempdb is on f:\mssql as shown in the database
> > > properties and with sp_dbhelp.
> >
> > > Poking around in master the real wierdness comes through. sysaltfiles
> > > has 2 entries each for tempdb logs and data files. One of them is on
> > > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > > g: (actually the last two rows in the table).
> >
> > > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > > the log and data files to the g: drive and one pair of rows points
> > > them to the f: drive. The references to g: drive need to go away but
> > > I've been googinling for a while now and haven't come up with much.
> >
> > > I tried running the alter database command normally used to move
> > > tempdb and though it didn't fail, it didn't change anything.
> >
> > > Several months ago our software vendor moved tempdb from g: to f: to
> > > try and speed it up a bit. Appearantly they messed it up and now have
> > > written us off till WE fix it.
> >
> > > The entries in sysaltfiles were the only references to g: that turned
> > > up (though we didn't look at every table and aren't even remotely sure
> > > where other references might be located).
> >
> > > Any pointers on getting this corrected would be greatly apprecieated.
> > > We thought about trying a reconfigure and restarting but I'm not real
> > > hopeful. We also thought about just updating the wrong entries to
> > > reflect the right locations but that smacks of kluge.
> >
> > > Tangential wierdness is that while trying to isolate the source of g:
> > > \mssql in the error I found that in master.sysdevices the file
> > > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > > \tempdb.mdf.
> >
> > > I believe this is from the initial install then while configuring the
> > > server it got moved to g: then to f:.
> >
> > > HELP!!!
> >
> > > Thanks in advance for any input!
> > > Rusty
> >
> > When creating the database I would not expect tempdb to have anything to do
> > with it!
> >
> > What command have you used to create this database? It seems to me that you
> > have specified g:\mssqldata\templog.ldf as the log file instead of
> > g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> > have been correctly set.
> >
> > What happens is you just use the T-SQL
> >
> > CREATE DATABASE NewDatabase
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

Multiple step OLEDB Error

The error message is

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

It is hard to pinpoint the exact problem w/o any additional info other than just the error msg here.

The error msg itself basically indicates that the application program was passing in an OLEDB property set, one or more of the properties were causing problem. What you should do is to enumerate through the property set and examine the corresponding status value, the error status should shed light on what went wrong.

Multiple Step OLE DB error from VB

One of our users is getting this error message.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was done.

This is the only user that is currently getting the message. Last week another user got this one day and then it stopped.

Inside VB6 code, we're sending some filters to an ASP that in turn sends a SELECT based on those parameters the SQL database and we get data back to the program.

Stepped through the code and found the error returns after this line

oSqlServConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl.2.6"

Any thoughts out in ForumLand? Please take it easy on me since I didn't write most of this code. Just trying to solve the problem. :)Check if this (http://support.microsoft.com/default.aspx?scid=kb;en-us;312288) pertains to your situation.|||Nope. Unfortunately not. Only sending a handful of filters and the user has Service Pack 4 already. Wouldn't be as concerned (since this is a user that rarely needs the data and can get it via other means) except this person has the newest computer on the block.|||Still having this problem. Any thoughts out there?|||Check tables names and columns names in SQL. I think there may be issue in deploying columns referred by the page.

Check the following :
Use client-side cursors (by setting the ADO recordset Cursor Location property to adUseClient).
Remove the insert trigger from the source table.
Put SET NOCOUNT ON line at the beginning of the trigger code.
After calling Update the first time to insert the new row in the table, scroll the recordset using methods like MoveNext and MovePrevious before calling Update again.

HTH|||Setting cursor location may affect the situation, but the rest simply does not apply, - the error occurs at the time of initializing the connection, not at the time of execution of an action query that fires a trigger.|||Tried setting cursor location on either side of the equation and still no go. I can recreate the problem on my computer now as long as I'm pointing to a test SQL server. When I switch over and point to our Production SQL server, I get no error.

Multiple servers

Hi All,

I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?

Thanks

Bryan

Are you storing the database connection information in the web application's web.config file? Or did you hard-code it into each sql command?

|||

all my other connections are stored in the webconfig. But as for the ASPNETDB.mdf connection string i have no idea where it is stored by default. Does it matter if there is no sql server on the pc that the site resides on. To my knowledge it does, so i need to find away to have the database on the database server and the site on our webserver. I dont know if i am making myself clear... if you create a simple site with a login.aspx with a login control and a default.aspx with a simple "HELLO" on it and choose ASP.net configuration web utility and setup some users and roles, it created a folder called App_Data where the ASPNETDB database is stored.

Now if this project was a piece of electornic equipment seperated into distinct pieces, i would like to remove the database piece from where it is in my application and physically move it to another geographical place namelly my other server (database server), but if i remove it totally, clip all the wires and remove it then my equipment (website) does not work correctly. So what i want to do is to extend the cables so that they are able to reach the other server (databse server) so my equipment (website) still works fine, just with the extended cable ( some sort of connection string stored somewhere! ) :-) kind of a dumb ass analogy, but im sure you get what I mean now.

I dont know if i am missing something or the only person that has done something like this but all i do is create a simple website, which works fine on my pc, and deploy it and the login doesnt work...

ANY suggestions will be greatly appreciated, ive been pulling my hair out for almost 2 days now trying to figure this out.

thanks for all the help

Bryan

|||

Hello,

The solution was: mounted the default sql serverdatabase (ASPNETDB) on our database server and added a connectionstring in the webconfig to point to it. what i had to do was remove theconnection string and then recreate it as follows

<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="DataSource=CORE;Initial Catalog=ASPNETDB;Persist Security Info=True;UserID=user;Password=password" providerName="System.Data.SqlClient"/>

this sorted out all my issues.

Thanks

Bryan

Multiple row delete syntax error

Hello

I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors:

DELETE From NameList

WHERE LName = 'Smith';
and
LName = 'Jones';
and
LName = 'Peters';
and
LName = 'Adams';
and
LName = 'Conner';
and
LName = 'Simon';

I have tried editing the syntax in a variety of ways, but I just can't find the correct solution.

I have Googled and so far not found another syntax format. What am I doing wrong.

Thanks.

Lynn

First, a semi-colon ( ; ) tells SQL Server that is the end of the statement. So you are probably getting incorrect syntax on line 3 above. I think what you want to do is this:

DELETE From NameList
WHERE LName = 'Smith'
OR
LName = 'Jones'
OR
LName = 'Peters'
OR
LName = 'Adams'
OR
LName = 'Conner'
OR
LName = 'Simon';

|||

Try this to work faster

DELETE From NameList
WHERE LName in( 'Smith','Jones','Peters','Adams','Conner','Simon')

and is much shorted and easy to create in .Net

Thanks

|||

Hi Guys

Thanks a lot your syntax works great and so fast too.

You have saved me hours of manually deleting rows. As I also have have many other tables needing rows deleted.

Cheers

Lynn

|||

Hello jpazgier

By the way, before I try this out and perhaps create a problem.

Does your shortened version work to inset data into multiple rows also?

This is the way I have inserted in the past, row by row.:

INSERT INTO LName ( [LName])
VALUES( N'Murphy')

I need to insert multiple rows, will this do the job?

INSERT Into NameList ( [LName])
VALUES (N' 'Smith','Jones','Peters','Adams','Conner','Simon')

Thanks

Lynn

|||

no it will not allow you to insert multiple rows this way, but for delete it will work perfectly

If you would like to use comma delimited values to insert you can create table returned function which will accept your string and generate table for you and next you can use

INSERT Into NameList ( [LName])
SELECT NAME
from dbo.SplitNamesByComma('Smith,Jones,Peters,Adams,Conner,Simon')

You can also loop throw string from comma to comma and insert names one by one inside stored procedure.

Thanks

|||

Hello jpazgier

Thanks for the informaton.

I thought I had found a quick solution to multiple row inserts, I was feeling quite pleased with myself.

Thanks

|||

maybe you can try this for multiple inserts?

create

table #aa(aavarchar(10))
insertinto #aa
SELECT'aa'
UNION
SELECT'bb'
UNION
SELECT'cc'

select*from #aa

So you only have to replace comma by UNION SELECT maybe it will work for you. It will save you some amount of time because it will be single insert.

Thanks