problem with cascading parameters after CTP2

In the standard report manager I'm running a report with 5 parameter choices. The first 3 are determinants of the 4th, and the combined 1-4 determine the 5th.

Although this works fine in my VS BIDS (sp1) with SQL 2005 sp2 CTP, when I deploy it to my sp2 CTP Report server, it throws an error as soon as I select the 3rd parameter.

One or more data sources is missing credentials

I found this answer from MS in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923684&SiteID=1) and since it deals with my problem, wanted to pass it along to anyone else that is dealing with this problem.

" This is a known regression with CTP2 that will be fixed before SP2 is released. The problem is not specific to multivalue parameters. It has to do with dependencies between parameters.

In some cases, you can work around the issue if you give the dependent parameter a default value. You can also work around the issue by removing the valid values dependency between the parameters, though this may not be possible for a given report."

The suggested workaround does work! I can get beyond the 3rd parameter now because I applied a default value!

The workaround is useless. It only alleviated the problem when I stayed with that default value for my 3rd parameter. As soon as I changed it I'm right back to the 'one or more data sources is missing credentials'

SP2 cannot be soon enough...

|||

The workaround suggested can work, but it's tricky, especially with nested parameters that are dependent on each other. I finally got a six parameter functional again but with some pain. I made all the dependent parameter lists 'multi-select' and gave them a functional default value. Only then do they populate correctly.

And this all because I was hoping to use these reports in WSS 3.0 with the report server addin. Got very close and now have stumbled across similar bug with multiple parameters in Sharepoint. Will post entry to Sharepoint forums.

sql

Problem with ADO and MSSQL

We have just changed all of our DB functionality from the BDE with the standard component query to ADO with the ADOquery. The follow doesnt work anymore now :

qRooms->Close();
qRooms->SQL->Clear();
qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and");
qRooms->SQL->Add("SDATE <=:enddate) or (EDATE >=:startdate and");
qRooms->SQL->Add("EDATE <=:enddate) or (SDATE <:startdate and");
qRooms->SQL->Add(" :startdate < EDATE) order by BLDG");
qRooms->Parameters->ParamByName("startdate")->Value = StrToDate(VOQDate);
qRooms->Parameters->ParamByName("enddate")->Value = (StrToDate(VOQDate) + 14);
qRooms->Open();

Why doesnt this work? What am I doing wrong?What programming environment are you using ?|||Borland C++ Builder 6|||I am not familiar with bc++ 6 builder - but does it matter that no spaces exist between the 1st and 3rd add methods (I put # where a space was lacking) ?

qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and #");
qRooms->SQL->Add("# SDATE <=:enddate) or (EDATE >=:startdate and #");
qRooms->SQL->Add("# EDATE <=:enddate) or (SDATE <:startdate and");|||What error are you getting?

Is it a runtime error, compile error - do you get an error at all..

More details please.

Stefan|||No errors, I just don't get all the data back. I run that in SQL analyzer and I get all the correct data back, but in Builder I only get some of the data back.sql

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem when saving XML results to a file

Hi all,

I'm using SQL Server 2005 Standard, and i'm trying to generate a large XML file to be used as an archive for a table I want to query for analysis/reporting. The problem is, when I save the query results as XML the file has many embedded \r\n in the middle of my data rows, causing this file to throw errors.

What could possibly be causing these characters to show up in the rows? They do not appear in the data, but rather in the field/property names like this:

QUERY:

Code Snippet

SELECT AccountNumber,TenantNumber,SequenceNumber,RecordType,Date,Reference,Code,ServiceCode,RateCode,
MeterNumber,Amount,BudgetBillAmount,Reading,DemandReading,Usage,DemandUsage,ServiceSequence,ReasonCode
FROM UBAccountHistory as dt
WHERE Date < '01/01/2007'
FOR XML AUTO, ROOT('rdData')

Returns something like:

Code Snippet

<rdData>

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="0" RecordType="1" Date="1901-01-01T00:00:00" Ref

erence="0" Code="0" ServiceCode="" RateCode="" MeterNumber="" Amount="0.00" BudgetBillAmount="0.00" Reading="0" DemandReading="0.0000" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

<UBAccountHistory AccountNumber="10020.00" TenantNumber="98" SequenceNumber="49" RecordType="2" Date="2005-12-02T00:00:00" Reference="2881" Code="5" Servi

ceCode="WA" RateCode="W41" MeterNumber="99990020" Amount="0.00" BudgetBillAmount="0.00" Reading="23817" DemandReading="2.3817" Usage="0" DemandUsage="0.0000" ServiceSequence="0" ReasonCode="0" />

</rdData>

I'm completely baffled by this. Someone recommended I try the BCP utility to export to XML as opposed to saving to a file...any other thoughts?

Thanks!

Mike

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

|||

a guy named Mike wrote:

You can use SQLCMD to create the file. You will want to use the :XML ON comand feature.

First create a file containing the :XML On command just ahead of your query, like the following example

Code Snippet

:XML ON
select [dbid], [name], [crdate]
from sysdatabases
for xml auto, root('rdData')

Then use the SQLCMD to call the script file you just created.

Here's an example:

Code Snippet

sqlcmd -Smyserver -dMaster -E -i"xmlbuild.sql" -r1 -h-1 -o"results.xml"

For an explaination of the SQLCMD command and all of the switches and the other scripting variables available here is the Books Online article

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

That is exactly what I was looking for. PERFECT! Thanks so much!

Cheers,

Mike

Problem using Client Tools to Remotely Connect to a SQL Server Instance on Anoth

Someone please help me before I loose all of my hair :mad: !!!

I have two machines, a Windows Server 2003 machine running an SQL Server Standard Edition database instance and client tools (Enterprize Manager etc.), and a workstation PC running Windows XP SP2 which has just the SQL Server client tools (not an actual DB instance).

I'm trying to use the Enterprize Manager client tool on my WinXP machine to remotely connect to the server instance on the server PC (via 'New SQL Server Registration' option). Everytime I do this I get the following error message:

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server Connection.

Now I've tried using different usernames and passwords. I have login details for my SQL Server set to use Windows Login rather then SQL Server login, so I tried remotely connecting with the username and password for the server PC, tried 'sa' with server password, even tried setting up a new user on the server and logging in from the client with that, no luck. I've opened up correct ports 1433 on both PCs, even disabled the windows firewalls algother. Both server and client have SP3a installed, WinServer2003 has SP1 and XP SP2.

So I'm at a loss to how to remotely connect to my SQL Server. FYI I know the server itself is working fine because I can connect to it using Enterprize Manager/Query Analyser on the local machine. Any ideas? Many thanks for your help!If the client and the server are on the same domain, or if the client account belongs to the tree where domain belongs to, then you can use Windows authentication. But if you already tried this, can you do a TELNET from the client with 1433 successfully?|||I've not setup a Domain Controller. Anyway I've solved it, I had SQL Server running in Windows Authentification Mode, so I switched to mixed mode and all is well now :)

Problem upgrading from Standard to Enterprise

I'm hoping someone can explain why the following is happening and how
I can resolve the issue. My company uses SQL Standard for it's prod
server. We decided to setup another server to utilize log shipping.
The server that was installed with Enterprise from scratch worked
perfect. The server that we upgraded from standard appears to work
fine as a server and I can now see the server as a destination server
for log shipping. (hence it does recognize it as Enterprise edition)
The problem is that the upgraded server does not contain any of the
log shipping tables or stored procedures required for log shipping.
Why is this? What is my solution to resolve this? The database is
our production server and is very large so I don't even want to think
about scratching the drive, reinstalling sql, and restoring the
database. Any clues?
Thanks,
willDid you run the log shipping wizard against it? The tables are not there by
default.
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407202028.448f131e@.posting.google.com...
> I'm hoping someone can explain why the following is happening and how
> I can resolve the issue. My company uses SQL Standard for it's prod
> server. We decided to setup another server to utilize log shipping.
> The server that was installed with Enterprise from scratch worked
> perfect. The server that we upgraded from standard appears to work
> fine as a server and I can now see the server as a destination server
> for log shipping. (hence it does recognize it as Enterprise edition)
> The problem is that the upgraded server does not contain any of the
> log shipping tables or stored procedures required for log shipping.
> Why is this? What is my solution to resolve this? The database is
> our production server and is very large so I don't even want to think
> about scratching the drive, reinstalling sql, and restoring the
> database. Any clues?
> Thanks,
> will

Problem upgrading from Standard to Enterprise

I'm hoping someone can explain why the following is happening and how
I can resolve the issue. My company uses SQL Standard for it's prod
server. We decided to setup another server to utilize log shipping.
The server that was installed with Enterprise from scratch worked
perfect. The server that we upgraded from standard appears to work
fine as a server and I can now see the server as a destination server
for log shipping. (hence it does recognize it as Enterprise edition)
The problem is that the upgraded server does not contain any of the
log shipping tables or stored procedures required for log shipping.
Why is this? What is my solution to resolve this? The database is
our production server and is very large so I don't even want to think
about scratching the drive, reinstalling sql, and restoring the
database. Any clues?
Thanks,
will
Did you run the log shipping wizard against it? The tables are not there by
default.
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407202028.448f131e@.posting.google.c om...
> I'm hoping someone can explain why the following is happening and how
> I can resolve the issue. My company uses SQL Standard for it's prod
> server. We decided to setup another server to utilize log shipping.
> The server that was installed with Enterprise from scratch worked
> perfect. The server that we upgraded from standard appears to work
> fine as a server and I can now see the server as a destination server
> for log shipping. (hence it does recognize it as Enterprise edition)
> The problem is that the upgraded server does not contain any of the
> log shipping tables or stored procedures required for log shipping.
> Why is this? What is my solution to resolve this? The database is
> our production server and is very large so I don't even want to think
> about scratching the drive, reinstalling sql, and restoring the
> database. Any clues?
> Thanks,
> will

Problem upgrading from Standard to Enterprise

I'm hoping someone can explain why the following is happening and how
I can resolve the issue. My company uses SQL Standard for it's prod
server. We decided to setup another server to utilize log shipping.
The server that was installed with Enterprise from scratch worked
perfect. The server that we upgraded from standard appears to work
fine as a server and I can now see the server as a destination server
for log shipping. (hence it does recognize it as Enterprise edition)
The problem is that the upgraded server does not contain any of the
log shipping tables or stored procedures required for log shipping.
Why is this? What is my solution to resolve this? The database is
our production server and is very large so I don't even want to think
about scratching the drive, reinstalling sql, and restoring the
database. Any clues?
Thanks,
willDid you run the log shipping wizard against it? The tables are not there by
default.
--
Andrew J. Kelly SQL MVP
"Will Wirtz" <WillCWirtz@.Yahoo.com> wrote in message
news:223f5e88.0407202028.448f131e@.posting.google.com...
> I'm hoping someone can explain why the following is happening and how
> I can resolve the issue. My company uses SQL Standard for it's prod
> server. We decided to setup another server to utilize log shipping.
> The server that was installed with Enterprise from scratch worked
> perfect. The server that we upgraded from standard appears to work
> fine as a server and I can now see the server as a destination server
> for log shipping. (hence it does recognize it as Enterprise edition)
> The problem is that the upgraded server does not contain any of the
> log shipping tables or stored procedures required for log shipping.
> Why is this? What is my solution to resolve this? The database is
> our production server and is very large so I don't even want to think
> about scratching the drive, reinstalling sql, and restoring the
> database. Any clues?
> Thanks,
> will