When trying to print to our Quote form we are getting this error. “Microsoft.Data.SqlClient.SqlException: Invalid object name ‘Erp.SalesRep’.” Yes my ssrs report has an external data source connection and we are having issue getting to connect to it. SSRs keeps saying it has a unknown object.
In the referenced datasoure i am having an issue query there. im not sure how to fix it.
any help is apprciated. This was working this morning and i am able to query the database with SSMS and no issues. the account is setup to use windows auth.
Might be a longshot but I just checked one of our homegrown Access DBs that we use with a read-only Kinetic connection. Our linked data tables are showing up as Erp_tablename - an underscore instead of a dot.
Is the issue strictly with the external db?
My first thought was that it has to do with the security issues related to “sales” tables. Like the base report is trying to access a field that is restricted (territories comes to mind). And this is breaking the link to your external DB.
That’s a “something doesn’t exist” error. Connection or security issues throw connection or security errors. If you have an opportunity to do a little reality check:
What happens if you point SSMS at that database and select * from Erp.SalesRep?
If that’s not happy, maybe
use <your database name by itself without the brackets>;
go
select * from Erp.SalesRep
just to make darn sure it’s querying in the right database…
If that’s still not happy, mouse around in the SSMS database tables pane. Drill down into your database, schema, tables, and look for the SalesRep table. Do you find your database.schema.table (matching that structure) where you expect it to be?
If the database collation is case sensitive, does the SSRS query case match the database objects?
If everything’s unhelpfully going great so far, what happens if you paste your SSRS query into an SSMS query session in the context of the right database? Are there any angry red squiggly lines in the query? Does it run? Does it throw a different error?
if you log in with different credentials for the two databases then the above won’t work. In that case you would probably have to use “Linked Servers”.
So this report is the standard quote from coming from the RDD for most of the report and then we have it connect back to the ERP database as it is unable to have salesRep table called more than once.
That’s what it’s saying there, I recall they needed to be Linked Servers. I guess what I am saying is, what if you use the same user that the SSRS report service is running as to access the ERP databsae?
We are using the same credentials for this process. And have been using it since we 10.2.300 without issue until we upgraded to 2025.1. Some of our other reports like the invoice form and job travaler work just fine without issue. But i have a feeling if i change or update them it will be in the same boat.
So we made a change to the report to delete a email address that is hard coded in the text field. then re uploaded the report and then it went belly up.
Do you have VS code DEvin? Or really any other application (i.e. notepad++) that can do a compare on the two files?
You can change the .rdl to .xml and then compare the old and new report to see if anything else in the report changed other than that one field. Download the latest version from the SSRS server.
Guessing it’s an auth issue that strikes when you make an update to the rdl… Instead of Windows Auth for this datasource connection, you could try SQL Server authentication with a SQL Login:
You just need to give that Login datareader permissions to the Kinetic DB. This is how I set these up to avoid weirdness and having to maintain a domain account for windows auth.
It never hurts to look in the Shared Reports Data Source.
Several times I tested a report file, copied it to Live, and forgot to redirect the Shared Report Data Source.
