Easy Question Hopefully

This happens in both SQL Server 2000 and SQL Server 2005

Database name: STAPLE

Owner: Scott

Table: ScottTab

I could login as Scott and state: Select count(1) from ScottTab; and results would be returned because the table ScottTab was associated to Scott and I did not have to prefix the table

It was backed up and restored to another SQL Server.

I tried the same query and am receiving the error table does not exist. I have tried to run: exec sp_changedbowner 'SCOTT' and exec sp_change_users_login 'Change_One','SCOTT','SCOTT' as well and get errors

Please let me know the proper steps I need to take to re-associate the new sql server engine to the restored database. The user SCOTT was setup the same way on both machines

When you say the user was setup the same way, does that include the SID? If you have SQL logins on two servers and want to be able to move databases between them with backup/restore or detach/attach, you should synchronize the logins by making sure they have the same SID value. The users in a database are matched to logins by SID, not by name.

Both sp_addlogin (2000) and CREATE LOGIN (2005) have an optional SID parameter that will help solve your problem.

|||There are other databases that already exist on the server so the user can not be dropped or re-created at the global level. I am also restoring databases from different servers. Any other ways to get around this problem?|||User exists at the database level and is mapped to a sql login. It's certainly okay to drop a user from a database without affecting other databases.

You're indeed seeing orphan user. Sp_change_users_login 'Update_One' should fix it.
Perhaps, you should run Sp_change_users_login 'Report' to see if there is an orphan.sql

Earlier question on ODBC compatibility

Hi Chris:

I am sure as you are aware only data adpaters in SQL 2005 out of the box are .NET Frameworks, in your answer to Stoobs, you state the ADO.Net framework does not have all the APIs.

We're experiencing the same problem with the product and ODBC. Unfortunately we have a legacy MUMPs DB (that KBSQLODBC layer sits on top of, its a flavor of SQL from Knowledge Based systems) that our vendor is not going to update. Given that, how can we address the ODBC driver problem with SQL 2005? Is there going to be an update to ADO.NET?

Thanks,

dfreshman

ADO.Net v2.0 hs new APIs to support metadata retrieval.

Note that you can transfer data via your existing ODBC driver and the .Net managed provider for ODBC, it's just that you have to provide a query in this case rather than being able to select from a list of tables.

|||But why is it like this?

Because at the moment it feels like ODBC has been deliberately ham-strung in order to promote OLEDB?|||

This is a tricky area, and to understand it you have to seperate concerns about the APIs and tools that use the APIs.

My responsibility is for OLE DB and ODBC so I'll limit my comments to them. If you want to follow up on tool issues or other APIs then you need to find appropriate forums and post there.

Neither OLE DB nor ODBC have been hamstrung in SQL Server 2005 in any way shape or form. They both expose new SQL Server 2005 features and datatypes to the maximum extent that is possible and consistent with each API. Microsoft is fully committed to both APIs. Yes, there was a period of time when OLE DB was being pushed harder than ODBC, but that is no longer the case. In fact for SQL Native Client we managed to speed ODBC up somewhat more that OLE DB, so we were definitely not holding back ODBC there.

|||

The trick to this was knowing not to use the Data Source View.

I had to create the Data Sources, add them to the connection manager make sure their uid and pwd were set, and then use the Data Flow and Data Source Reader with a query as you stated.

Thanks!

|||

Hi Chris

Apologies for backing off an old topic, but it seems to be the only one I have found on my search to the answer to my query. I have a large clinical system that uses MUMPS and has the KB_SQL engine. Can you with SQL 2005 connect to KB_SQL, to synconize all tables? I can connect to KB_SQL via access, but cannot do it via SQL Server, unless there is a different ODBC string required or to use a API or ADO method?

You help would be most appriciated!

M

|||

With an ODBC driver you could set up a linked server using the MSDASQL provider. SQL Server 2005 uses OLE DB internally and MSDASQL is an OLE DB to ODBC bridge. The starting point in Book Online is http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx MSDASQL is described here http://msdn2.microsoft.com/en-us/library/ms191462(SQL.90).aspx. If you have an OLE DB provider for KB-SQL then it can be used directly to set up a linked server and you could also consider using SQL Server Integration Services http://msdn2.microsoft.com/en-us/library/ms141026(SQL.90).aspx.

I hope this helps,

Chris

Earlier question on ODBC compatibility

Hi Chris:

I am sure as you are aware only data adpaters in SQL 2005 out of the box are .NET Frameworks, in your answer to Stoobs, you state the ADO.Net framework does not have all the APIs.

We're experiencing the same problem with the product and ODBC. Unfortunately we have a legacy MUMPs DB (that KBSQLODBC layer sits on top of, its a flavor of SQL from Knowledge Based systems) that our vendor is not going to update. Given that, how can we address the ODBC driver problem with SQL 2005? Is there going to be an update to ADO.NET?

Thanks,

dfreshman

ADO.Net v2.0 hs new APIs to support metadata retrieval.

Note that you can transfer data via your existing ODBC driver and the .Net managed provider for ODBC, it's just that you have to provide a query in this case rather than being able to select from a list of tables.

|||But why is it like this?

Because at the moment it feels like ODBC has been deliberately ham-strung in order to promote OLEDB?
|||

This is a tricky area, and to understand it you have to seperate concerns about the APIs and tools that use the APIs.

My responsibility is for OLE DB and ODBC so I'll limit my comments to them. If you want to follow up on tool issues or other APIs then you need to find appropriate forums and post there.

Neither OLE DB nor ODBC have been hamstrung in SQL Server 2005 in any way shape or form. They both expose new SQL Server 2005 features and datatypes to the maximum extent that is possible and consistent with each API. Microsoft is fully committed to both APIs. Yes, there was a period of time when OLE DB was being pushed harder than ODBC, but that is no longer the case. In fact for SQL Native Client we managed to speed ODBC up somewhat more that OLE DB, so we were definitely not holding back ODBC there.

|||

The trick to this was knowing not to use the Data Source View.

I had to create the Data Sources, add them to the connection manager make sure their uid and pwd were set, and then use the Data Flow and Data Source Reader with a query as you stated.

Thanks!

|||

Hi Chris

Apologies for backing off an old topic, but it seems to be the only one I have found on my search to the answer to my query. I have a large clinical system that uses MUMPS and has the KB_SQL engine. Can you with SQL 2005 connect to KB_SQL, to synconize all tables? I can connect to KB_SQL via access, but cannot do it via SQL Server, unless there is a different ODBC string required or to use a API or ADO method?

You help would be most appriciated!

M

|||

With an ODBC driver you could set up a linked server using the MSDASQL provider. SQL Server 2005 uses OLE DB internally and MSDASQL is an OLE DB to ODBC bridge. The starting point in Book Online is http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx MSDASQL is described here http://msdn2.microsoft.com/en-us/library/ms191462(SQL.90).aspx. If you have an OLE DB provider for KB-SQL then it can be used directly to set up a linked server and you could also consider using SQL Server Integration Services http://msdn2.microsoft.com/en-us/library/ms141026(SQL.90).aspx.

I hope this helps,

Chris

DynamicValuesUnavailable state using wsRS.GetReportParameters

Hi,
I am using the webservice ReportingService from the Reporting Services Web
Service Library.
I'm having a problem when retrieving Parameters for a RS report, valid
values are not returned when they are QueryBased.
It works fine when accessing the report through the RSServerName/reports
virtual dir. Then a dropdown with valid values for the parameter is perfectl
y
populated. As far as I know the "Reports" website uses the ReportingServices
WebService methods?!
When parameters are return from the GetReportParameters method, the
ReportParameters array is correct populated at first glance. The parameters
prm.ValidValuesQueryBased is True, but further investigation reveals that th
e
prm.ValidValues is nothing (VB) and the prm.State of the parameter is
DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
the ValidValue class.....
I cannot seem to find any documentation why the prm.State is
DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
solution?
Thanks GlennI have now found the answer to my question. So for your information if anyon
e
should need it:
When invoking the method GetReportParameters on the ReportingServices
service be sure to pass True as value for the ForRendering parameter. When
this is done ValidValues are returned!
/Glenn
"Glenn S?rensen" wrote:

> Hi,
> I am using the webservice ReportingService from the Reporting Services Web
> Service Library.
> I'm having a problem when retrieving Parameters for a RS report, valid
> values are not returned when they are QueryBased.
> It works fine when accessing the report through the RSServerName/reports
> virtual dir. Then a dropdown with valid values for the parameter is perfec
tly
> populated. As far as I know the "Reports" website uses the ReportingServic
es
> WebService methods?!
> When parameters are return from the GetReportParameters method, the
> ReportParameters array is correct populated at first glance. The parameter
s
> prm.ValidValuesQueryBased is True, but further investigation reveals that
the
> prm.ValidValues is nothing (VB) and the prm.State of the parameter is
> DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array o
f
> the ValidValue class.....
> I cannot seem to find any documentation why the prm.State is
> DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
> solution?
> Thanks Glenn

DynamicValuesUnavailable state using wsRS.GetReportParameters

Hi,
I am using the webservice ReportingService from the Reporting Services Web
Service Library.
I'm having a problem when retrieving Parameters for a RS report, valid
values are not returned when they are QueryBased.
It works fine when accessing the report through the RSServerName/reports
virtual dir. Then a dropdown with valid values for the parameter is perfectly
populated. As far as I know the "Reports" website uses the ReportingServices
WebService methods?!
When parameters are return from the GetReportParameters method, the
ReportParameters array is correct populated at first glance. The parameters
prm.ValidValuesQueryBased is True, but further investigation reveals that the
prm.ValidValues is nothing (VB) and the prm.State of the parameter is
DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
the ValidValue class.....
I cannot seem to find any documentation why the prm.State is
DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
solution?
Thanks Glenn
I have now found the answer to my question. So for your information if anyone
should need it:
When invoking the method GetReportParameters on the ReportingServices
service be sure to pass True as value for the ForRendering parameter. When
this is done ValidValues are returned!
/Glenn
"Glenn S?rensen" wrote:

> Hi,
> I am using the webservice ReportingService from the Reporting Services Web
> Service Library.
> I'm having a problem when retrieving Parameters for a RS report, valid
> values are not returned when they are QueryBased.
> It works fine when accessing the report through the RSServerName/reports
> virtual dir. Then a dropdown with valid values for the parameter is perfectly
> populated. As far as I know the "Reports" website uses the ReportingServices
> WebService methods?!
> When parameters are return from the GetReportParameters method, the
> ReportParameters array is correct populated at first glance. The parameters
> prm.ValidValuesQueryBased is True, but further investigation reveals that the
> prm.ValidValues is nothing (VB) and the prm.State of the parameter is
> DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
> the ValidValue class.....
> I cannot seem to find any documentation why the prm.State is
> DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
> solution?
> Thanks Glenn

DynamicValuesUnavailable state using wsRS.GetReportParameters

This is a repost of a message from sqlserver.server of 9/1/2004 which is MSDN
managed, but there is no answer yet. So here goes in "reportingsvcs"
I am using the webservice ReportingService from the Reporting Services Web
Service Library.
I'm having a problem when retrieving Parameters for a RS report, valid
values are not returned when they are QueryBased.
It works fine when accessing the report through the RSServerName/reports
virtual dir. Then a dropdown with valid values for the parameter is perfectly
populated. As far as I know the "Reports" website uses the ReportingServices
WebService methods?!
When parameters are return from the GetReportParameters method, the
ReportParameters array is correct populated at first glance. The parameters
prm.ValidValuesQueryBased is True, but further investigation reveals that the
prm.ValidValues is nothing (VB) and the prm.State of the parameter is
DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
the ValidValue class.....
I cannot seem to find any documentation why the prm.State is
DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
solution?
Thanks GlennThanks Glenn! I had the same problem, and your workaround worked for me.
Ron
"SoGMo" wrote:
> I have now found the answer to my question. So for your information if anyone
> should need it:
> When invoking the method GetReportParameters on the ReportingServices
> service be sure to pass True as value for the ForRendering parameter. When
> this is done ValidValues are returned!
> /Glenn
> "SoGMo" wrote:
> > This is a repost of a message from sqlserver.server of 9/1/2004 which is MSDN
> > managed, but there is no answer yet. So here goes in "reportingsvcs"
> >
> > I am using the webservice ReportingService from the Reporting Services Web
> > Service Library.
> > I'm having a problem when retrieving Parameters for a RS report, valid
> > values are not returned when they are QueryBased.
> > It works fine when accessing the report through the RSServerName/reports
> > virtual dir. Then a dropdown with valid values for the parameter is perfectly
> > populated. As far as I know the "Reports" website uses the ReportingServices
> > WebService methods?!
> >
> > When parameters are return from the GetReportParameters method, the
> > ReportParameters array is correct populated at first glance. The parameters
> > prm.ValidValuesQueryBased is True, but further investigation reveals that the
> > prm.ValidValues is nothing (VB) and the prm.State of the parameter is
> > DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
> > the ValidValue class.....
> >
> > I cannot seem to find any documentation why the prm.State is
> > DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
> > solution?
> >
> > Thanks Glenn

Dynamic SQL question

Can someone tell me why the exec statement throws the following error?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Code Snippet

create table #t (Val1 smallint, Val2 smallint )
go
DECLARE @.Diff as int;
set nocount on

insert into #t values(10, 15)

--Why does this work?
SET @.Diff=(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)
select @.Diff

--And yet this does not?
EXEC('(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

drop table #t
go

Thanks

Colin

It is because you dont have a SELECT or anything in your dynamic sql.

change to:

Code Snippet

EXEC('SELECT (SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

and it works fine.