Invalid Object Name Error

I'm trying to create a report model using a set of tables from two different servers. Creating the Data Sources and the Data Source View is no problem, however, while trying to create a Report model I run into an error that says,

An error occurred while executing a command.
Message: Invalid object name 'dbo.table_name.
Command:
SELECT COUNT(*) FROM [dbo].[table_name] t

I've checked the schemas for both these tables and they are correct. Why is this error occuring?

Any suggestions would be appreciated!

I'm not sure why the error occurs here but I have a found solution around the problem. You can create named queries that Union your tables from your different data sources into one query.sql

Invalid namespace

I'm getting the following error message when I tried to configure Reporting Services in 2005 SQL:

"No report servers were found on the specified machine.

Invalid namespace" Any assistance to get over this hurdle will be appreciated!

We have the same problem. Our reportserver suddenly went down and now shows this problem.

Invalid Descriptor Index problem

I've successfully set up replication on two servers, but the third one
is giving me problems. Naturally, the first two were development test
servers and the last one is the customer's server. It's a Win2000
server, with SP4. The SQL Server version is 8.00.760 (SP3). I
upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
2000.85.1022.00.
I am trying to set up transactional publications between databases.
When they initialize--before the first transaction can run--I get the
Invalid Descriptor Index error. If anyone can help, please do!
(Please reply to the group, so that all who search may benefit from
your wisdom.)
I ran distrib from the command line with OutputVerboseLevel set to 2.
The command I used was:
>distrib -Publisher SQL1S -PublisherDB B_DB_Copy -Subscriber SQL1S
>-OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword
><password_removed> -SubscriberDB RC_DB_Copy -SubscriberLogin sa
>-SubscriberPassword <password_removed> -Distributor SQL1S
>-SubscriptionType 0 -SubscriptionTableName reg_geo_cfg
>-Publication B_DB_Copy
Here's the result (sanitized so the customer won't get mad):
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Connecting to Distributor 'SQL1S'
Connecting to Distributor 'SQL1S.'
[6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
[6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
Server: SQL1S
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
ANSI codepage: 1
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
Subscriber security mode: 0, login name: sa.
[6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
from master..sysservers where upper(srvname) = upper(N'SQL1S')
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
N'RC_DB_Copy', 0)}
Agent message code 20046. Invalid Descriptor Index
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
Transaction Seqno = 0000000000000000000000000000, Command ID = -1
Message: Replication-Replication Distribution Subsystem: agent
SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
N'SQL1S', N'RC_DB_Copy', ?)}
ErrorId = 3, SourceTypeId = 4
ErrorCode = 'S1002'
ErrorText = 'Invalid Descriptor Index'
[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
Category:ODBC
Source: ODBC SQL Server Driver
Number: S1002
Message: Invalid Descriptor Index
Disconnecting from Distributor History 'SQL1S'
The process finished. Use CTRL+C to close this window.
there have been reports that this problem has been solved by reapplying the
latest sp (sp3a).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...
> I've successfully set up replication on two servers, but the third one
> is giving me problems. Naturally, the first two were development test
> servers and the last one is the customer's server. It's a Win2000
> server, with SP4. The SQL Server version is 8.00.760 (SP3). I
> upgraded MDAC last night to 2.8, so the SQL Server ODBC version is
> 2000.85.1022.00.
> I am trying to set up transactional publications between databases.
> When they initialize--before the first transaction can run--I get the
> Invalid Descriptor Index error. If anyone can help, please do!
> (Please reply to the group, so that all who search may benefit from
> your wisdom.)
> I ran distrib from the command line with OutputVerboseLevel set to 2.
> The command I used was:
> Here's the result (sanitized so the customer won't get mad):
> Microsoft SQL Server Distribution Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Connecting to Distributor 'SQL1S'
> Connecting to Distributor 'SQL1S.'
> [6/10/2004 10:14:50 PM]SQL1S.: exec sp_helpdistpublisher N'SQL1S'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select @.@.SERVERNAME
> Server: SQL1S
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/10/2004 10:14:50 PM]SQL1S.distribution: execute sp_server_info 18
> ANSI codepage: 1
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {?=call
> sp_MShelp_subscriber_info (N'SQL1S', N'SQL1S')}
> Subscriber security mode: 0, login name: sa.
> [6/10/2004 10:14:50 PM]SQL1S.distribution: select datasource, srvid
> from master..sysservers where upper(srvname) = upper(N'SQL1S')
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MShelp_distribution_agentid(0, N'B_DB_Copy', N'B_DB_Copy', 0,
> N'RC_DB_Copy', 0)}
> Agent message code 20046. Invalid Descriptor Index
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1,
> 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 3,
> Transaction Seqno = 0000000000000000000000000000, Command ID = -1
> Message: Replication-Replication Distribution Subsystem: agent
> SQL1S-BDBCopy-B_DB_Copy-SQL1S-1 failed. Invalid Descriptor
> Index[6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_alert(3, 1, 3, 14151, ?, -1, N'SQL1S', N'B_DB_Copy',
> N'SQL1S', N'RC_DB_Copy', ?)}
> ErrorId = 3, SourceTypeId = 4
> ErrorCode = 'S1002'
> ErrorText = 'Invalid Descriptor Index'
> [6/10/2004 10:14:50 PM]SQL1S.distribution: {call
> sp_MSadd_repl_error(3, 0, 4, ?, N'S1002', ?)}
> Category:ODBC
> Source: ODBC SQL Server Driver
> Number: S1002
> Message: Invalid Descriptor Index
> Disconnecting from Distributor History 'SQL1S'
>
> The process finished. Use CTRL+C to close this window.
|||Reapplying the service pack worked. Thank you for your help!!
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<uO#U0aWUEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> there have been reports that this problem has been solved by reapplying the
> latest sp (sp3a).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "KenRobertson" <google.nospam.roberken@.spamgourmet.com> wrote in message
> news:4c25a332.0406110528.5bfd7b97@.posting.google.c om...

Invalid cast when trying to use SQLXMLBulkload

I am trying to move data between two servers through my application by outputting the data as XML and then importing it later. I am using .WriteXML and .WriteXMLSchema in my .NET application to create the files, but I get "invalid cast Exception" when trying to BulkLoad them in using SQLXMLBulkLoad4. I even adjust the XSD file to add the sqlatatype in there for the DateTime fields. Is there another field I am breaking on. I looked at the docs and I thinbk my GUIDs are OK since they do not have the {} on them.

Example file: XSD file

<?xml version="1.0" standalone="yes"?>
<xschema id="STG_Additional_Labs" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata">
<xs:element name="STG_Additional_Labs" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xsequence>
<xs:element name="FacilityID" type="xs:int" minOccurs="0" />
<xs:element name="Comment" type="xstring" minOccurs="0" />
<xs:element name="Deleted" type="xs:boolean" minOccurs="0" />
<xs:element name="Lab_Date" type="xsateTime" sqlatatype="dateTime" minOccurs="0" />
<xs:element name="Lab_Name" type="xstring" minOccurs="0" />
<xs:element name="Lab_Time" type="xstring" minOccurs="0" />
<xs:element name="Lab_Value" type="xstring" minOccurs="0" />
<xs:element name="PIID" type="xs:int" minOccurs="0" />
<xs:element name="rowguid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="RowIDGuid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="SaveDateTime" type="xsateTime" sqlatatype="dateTime" minOccurs="0" />
<xs:element name="VersionGuid" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
<xs:element name="PatientID" msdataataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xstring" minOccurs="0" />
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>

Example file: XML file

<?xml version="1.0" standalone="yes"?>
<STG_Additional_Labs>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-30T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Subst</Lab_Name>
<Lab_Value>trace</Lab_Value>
<PIID>14</PIID>
<rowguid>03cc9264-8829-464f-b01d-2b18ee4ccdfb</rowguid>
<RowIDGuid>ff9e4e59-6716-46d4-bfcb-61777ed8cf5d</RowIDGuid>
<SaveDateTime>2007-05-30T10:18:24.52-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
<Table>
<FacilityID>648</FacilityID>
<Deleted>false</Deleted>
<Lab_Date>2007-05-19T00:00:00-04:00</Lab_Date>
<Lab_Name>Stool Red Sub</Lab_Name>
<Lab_Value>&lt;0.25/ neg</Lab_Value>
<PIID>14</PIID>
<rowguid>876ca5f9-5c0f-4a74-bf03-2e86260ca2a1</rowguid>
<RowIDGuid>45c45895-8008-40ed-a779-c478d476c15b</RowIDGuid>
<SaveDateTime>2007-05-19T10:13:53.857-04:00</SaveDateTime>
<VersionGuid>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</VersionGuid>
<PatientID>b4b29c65-71e2-425a-9eb9-037acc7ae4d0</PatientID>
</Table>
</STG_Additional_Labs>Ack! Sorry about the emoticons!|||Actually, further work on this problem seemed to reveal that the Cast error was actually due to the connection string in my application being wrong (although it is used for all the other connections in the application). The error now coming back is this:

<?xml version="1.0"?><Result State="FAILED"><Error><HResult>0x80004005</HResult><Description>
<![CDATA[Reference to undeclared namespace prefix: 'sql'.
]]></Description><Source>Schema mapping</Source><Type>FATAL</Type></Error></Result>

What would be the proper way to specify the SQL namespace in the top of the file?|||I have essentially resolved my problem by not trying to output the XML data to then read it in later. I am now using the SQLBulkLoad classes to pump the data from a DataTable to the destination SQL server. It would be nice however to have a method in the SQLReader/Writer that can write out a SQL friendly XSD file, so that it can be used later by the SQLXMLBulkLoad class.

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?
Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?
|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:
> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
> > Hi,
> > I am trying to run some queries across the servers.
> > I have admin rights on both the boxes, I have linked the server.
> > But whenever i run a query on the remote server, I get the following error
> > --
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'SQLOLEDB' reported an error.
> > [OLE/DB provider returned message: Invalid authorization specification]
> > --
> > The query is a basic one like
> > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> >
> > Any suggestions pls ?|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:
> > Hi Arun,
> >
> > Possible reasons...
> >
> > * Check whether you have added your login id in the remote sql server. If
> > you haven't added the login id, use sp_addremotelogin system stored procedure
> > to add your local login in the remote server and give appropriate access
> > rights.
> >
> > "Arun" wrote:
> >
> > > Hi,
> > > I am trying to run some queries across the servers.
> > > I have admin rights on both the boxes, I have linked the server.
> > > But whenever i run a query on the remote server, I get the following error
> > > --
> > > Server: Msg 7399, Level 16, State 1, Line 1
> > > OLE DB provider 'SQLOLEDB' reported an error.
> > > [OLE/DB provider returned message: Invalid authorization specification]
> > > --
> > > The query is a basic one like
> > > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> > >
> > > Any suggestions pls ?

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedur
e
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored proced
ure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
>|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserve
r.
> Arun
> "Shri.DBA" wrote:
>

Internet Replication

I am attempting to setup a publication where I'm replicating date between 2
sql 2000 servers via the internet. The snapshots are configured to be written
to ftp to be downloaded by the untrusted subscribers via ftp. I'm having
troubles with not having access to the distributor, this wasnt an issue until
I restricted access to RPC on the firewall seperating the
distributor/publication and the subscriber. The subscribers get the error
'the process could not connect to Distributor <SERVER>'
Is there a way to give access the distributor via ftp (or some other way)
and how is this done?
Do particular types of replication require access to the distributor (can I
get away with no distributor access)?
If I setup up an updating subscription, what is the best way to achieve an
updating subscription over the internet without a vpn?
Any help would be greatly appreciated.
-James
Nobody can help?
"James" wrote:

> I am attempting to setup a publication where I'm replicating date between 2
> sql 2000 servers via the internet. The snapshots are configured to be written
> to ftp to be downloaded by the untrusted subscribers via ftp. I'm having
> troubles with not having access to the distributor, this wasnt an issue until
> I restricted access to RPC on the firewall seperating the
> distributor/publication and the subscriber. The subscribers get the error
> 'the process could not connect to Distributor <SERVER>'
> Is there a way to give access the distributor via ftp (or some other way)
> and how is this done?
> Do particular types of replication require access to the distributor (can I
> get away with no distributor access)?
> If I setup up an updating subscription, what is the best way to achieve an
> updating subscription over the internet without a vpn?
> Any help would be greatly appreciated.
> -James