April 29, 2013 at 10:11 am
The sample connection striung as provided by Microsoft for the SQL Server Instance example (at http://msdn.microsoft.com/en-us/library/dd220525(v=sql.105).aspx) is not working for me and darned if I can figure out why.
The sample provided is:
Data Source=localhost\MSSQL10_50.InstanceName; Initial Catalog= AdventureWorks
I am using 2 SQL Servers, both of which are running 2008R2. SQL03\SS2008R2 is running reporting servies. SQL04\SS2008R2 is the instance hosting the DB I want to access. I have creat a SQL Login on SQL04\SS2008R2 with dbo access to the DB I am trying to connect to.
When RDP'd into SQL03 and using SSMS 2008@ I can connect to SQL04\SS2008R2 with the user/pwd for the user I created and I can query the various tables and views.
Yet any attempt to get the connection string to work fails. I am going the DATA SOURCES SSRS page on SQL03\SSS2008R2 and clikcing NEW DATA SOURCE and using the following connection string. Can anyone see what is wrong with this connection string? Its not the user & password because a review of the SQL Logs on SQL04\SS2008R2 show NO attempt to connect via the user I created except for the test I did when using SSMS .
Data Source=SQL04\MSSQL10_50.SS2008R2; Initial Catalog= MYTESTDB
Is this an issue with going across 2 DBs? Does the SSRS instance have to be on the SAME SQL Server ad the DB you are trying to connect to?
Kindest Regards,
Just say No to Facebook!April 29, 2013 at 10:24 am
I don't see where you are providing the Username and password to connect to your source system.
April 29, 2013 at 2:26 pm
Lynn,
The user name & password both go in seperate fields form the Connection string box. Yeah I thought that was odd to but thats the way its setup.
Thanks
Kindest Regards,
Just say No to Facebook!April 29, 2013 at 2:28 pm
SOLVED.
The proper connection string was:
Data Source=SQL04\SS2008R2;Initial Catalog=MYTESTDB
Thanks to those who replied.
Kindest Regards,
Just say No to Facebook!April 29, 2013 at 2:47 pm
YSLGuru (4/29/2013)
SOLVED.The proper connection string was:
Data Source=SQL04\SS2008R2;Initial Catalog=MYTESTDB
Thanks to those who replied.
So the fix was removing the spaces in your original connection string?
April 30, 2013 at 10:31 am
Lynn Pettis (4/29/2013)
YSLGuru (4/29/2013)
SOLVED.The proper connection string was:
Data Source=SQL04\SS2008R2;Initial Catalog=MYTESTDB
Thanks to those who replied.
So the fix was removing the spaces in your original connection string?
Actually it was more embarrising then that. The orginal example provded was :
Data Source=localhost\MSSQL10_50.InstanceName; Initial Catalog= AdventureWorks
I subsititied each piece for what I thought needed to be subsitiuted and so I had this:
Data Source=SQL04\MSSQL10_50.SS2008R2; Initial Catalog= MYTESTDB
It was only after I removed the 'MSSQL10_50.' piece that it finally worked. In my opinion that piece did not look like it was part of the instance name exxample but a fixed piece to perhaps designate SQL version or something.
All in all the whole SSRS thing is very confusing as I come from a CRYSTAL REPORTS & MS ACCESS Report writing back ground . I want very much to embrace the use of SSRS but each time I try its more and more frustration from how difficult it is to make a simple report to how it all fits togethere. Its almost as bad as trying to learn SSIS after having used DTS. GRRRRHHH.
Thanks Lynn.
Kindest Regards,
Just say No to Facebook!Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply