Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS COnnection String Not Working Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 10:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1447633
Posted Monday, April 29, 2013 10:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
I don't see where you are providing the Username and password to connect to your source system.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447637
Posted Monday, April 29, 2013 2:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1447767
Posted Monday, April 29, 2013 2:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1447769
Posted Monday, April 29, 2013 2:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447779
Posted Tuesday, April 30, 2013 10:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #1448136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse