Error creating new ODBC data source

[splat78423]

I'm trying to setup a ODBC data soure for my computer so I can hook up crystal reports to my SQL server (which I have administrative access to). However I cant connect in data source administrator even after I have verified servername, database name, and login info several times...The error basically tells me that I either typed in the wrong username and password or the server doesnt exist...I am running SQL Server 2005 and am using Managment Studio Express. Someone told me that express may not be able to except the type of connection I am trying to do...If this is the case then how else can I link up to the thing with crystal reports.....The crystal reports people basically tell me its a database problem and the database people tell me it a crystal reports problem so I am at a loss as to what to try next....?

 

[mrdenny]

What's the exact error message?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[splat78423]

I'll try it out again tomorrow when I'm back in the office and let you exactly what the message is. It basically said that either the username or passwor dis bad or the server does not exist.....I'll see if I can get you a message number or error code in the morning.

I certainly do appreciate your intertest in the issue

 

[splat78423]

while creaing the ODBC data source connection I get the following error...

Connection failed
SQL State: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL ServerDriver][DBNetLib] ConnectionOpen(connect())
Connection failed:
SQL State '08001'
SQL Server Error: 17
Server does not exist or access denied

 

[splat78423]

splat78423 (IS/IT--Management) 24 Aug 06 10:38
I have never used crystal befor and I am simply trying to set it up on my workstation and start a new report with access to my SQL 2005 database....I have full administrative access across all systems so that wont be a problem...... I just need a push in the right direction. Do I create a new connection......choose OLE DB...and then microsoft OLE data provider then enter my login info? Will this allow me to present my SQL text files into my new report? Assistance beyond a link to a Newb tutorial is appreciated much...



Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!




Start your own Tek-Tips Group! Click Here!
IdoMillet (Instructor) 24 Aug 06 10:41
There's good online help chapter on "Accessing Data Sources" if you just hit F1...

What are "SQL text files"?

- Ido
Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

Thank IdoMillet
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



synapsevampire (Programmer) 24 Aug 06 10:45
You may find that ODBC proves faster than OLE DB.

But yes, create a connection to the database and you will have access to the Tales, Views and Stored Procedures. You will need to djust the File->Options->Database to include Views and SPs.

SQL Server doesn't use text files, although they could be embedded in specific fields.

-k

Thank synapsevampire
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 24 Aug 06 11:41
Ill try all of that out and let you know how it goes. I am new at this DBA stuff (sql, crystal, etc....) but my SQL server imports text files into the database every night which contain updates to my tables. All of the info in the tables are stored in 5 text files for the import so I was also wondering if I could pull data directly from them as an alternative to going through my server....


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



synapsevampire (Programmer) 24 Aug 06 11:48
You could pull from text files, which would prove slower and if you need all of the data in the report, you'd have to read in every text file (which can be cheated with a UNION ALL), but that's not the way to go.

Relational databases are much faster and easier to use than text files.

-k

Thank synapsevampire
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 24 Aug 06 13:09
I am having problems just connecting to my database now and its on the server I am sitting right next to! I am entering the correct servername and database, as well I am entering the network admin username and password but it is not taking! I am sure the net connection is fine and this is a fresh install of crystall! Could it be a permission problem that can be changed through SQL manager....if so (I know this would be getting off the topic of the board) do I need to start an entire whole new thread to figure this out or are their other steps that can be taken through crystal reports?


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 24 Aug 06 13:16
I found an article that say not to try to connect natively to a SQL database with crystal because a .dll file in crystal reports hasnt been updated... Is this what I am trying to do here? I am trying to connect the same way I've described in my first post.....I just dont get it...


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 24 Aug 06 16:24
I tried installing crysal reports on the computer that hosts the SQL server software but I just get a hundred Error 1904 Module blah blah blah failed to launch messages....Individually registering each individual .dll is not possible because there are way to many to register manaully.....No one seems to have the solution to my problem and I've had probally seven other IT people try to help out!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



synapsevampire (Programmer) 25 Aug 06 9:51
Post the error message, not "but it is not taking!".

Create an ODBC connection to SQL Server and test the connection while doing so, once it works there it should work in Crystal.

-k

Thank synapsevampire
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 25 Aug 06 11:20
while creaing the ODBC data source connection I get the following error...

Connection failed
SQL State: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL ServerDriver][DBNetLib] ConnectionOpen(connect())
Connection failed:
SQL State '08001'
SQL Server Error: 17
Server does not exist or access denied


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



synapsevampire (Programmer) 25 Aug 06 11:29
Sounds like you either have the name of the server wrong or the user/pw is wrong.

You should be able to hit the down arrow next to Server and select the server.

-k

Thank synapsevampire
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 25 Aug 06 12:07
I have verified the servername and password several times already....


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



synapsevampire (Programmer) 25 Aug 06 12:09
How are you verifying it?

Try placing the Enterprise Manager on the same development machine and see if it can hit the database.

SQL Server 2005 definitely supports ODBC, so something else is amiss.

-k

Thank synapsevampire
for this valuable post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



splat78423 (IS/IT--Management) 25 Aug 06 13:23
I am verifying it by logging into my file server with admin access rights then into SQL Server Management studio express with and checking the instance name, etc. I have administrative access to the server so I am just using those credentials. All the info I need is right there as far as database and server names are concerned so I am simply copying it word for word. I tried installing crytal reports directly onto my database server

 

[SQLBill]

When you set up the ODBC connection, you get to a window where you select your authentication mode and can enter a login and password. You should also see a button for Client Connection or something like that. Click on it. Make sure the information in that window is correct - check the IP or names, check the port, check the type of connection (it should probably be set for TCP/IP and may be set for Named Pipes instead).

Second possible issue....what type of authentication are you using to connect? Is your SQL Server login using Windows Authentication or SQL Server Authentication? If it's Windows...then is the client machine on the same domain? If not, is it a 'trusted' domain?

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Assuming that your ODBC connection is setup correctly it sounds like there is some sort of firewall between your workstation and the SQL Server.

Try to telnet from your workstation to the SQL Server of port 1433. From a command prompt run:

telnet ServerName 1433

If you get a blank window with a flashing cursor then the network is allowing your connection. If you get an error then there is some thing blocking the network connection.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[splat78423]

I'll give it a shot but since I setup the entire network I know for sure that my firwall is not blocking the connection. I have checked and double checked my server name and, username, and password but the ODBC connection wont setup. My database server and my client machine are on the same local switch...I'll try the telnet idea though and and I'll let you know what happens.

 

[SQLBill]

Have you checked the part that I sugggested (the client connection - to make sure the port is set properly and you are using TCP/IP and not Named Pipes)?

-SQLBIll

Posting advice: FAQ481-4875

 

[splat78423]

I double checked the client configuration when I tried setting it up the last time and it was was set properly to tcp/ip.....I can ping the server itself
ie:

ping servername

but I cant ping or telnet to the database

ping servername\database
or
telent servername\database 1433

I have twenty users connecting to this thing through an application server w/o a problem.......I'm searchin around google for more ideas.....Could it be a permission problem within the database? I dont see how that could be though because I am using the username that is the grantor of all permissions (I bring that up cause I have no other ideas at this point.).

 

[splat78423]

I am able to telnet to the server also via 445

telnet ipaddr 445

 

[SQLBill]

Are you using a login with Windows Authenication? If so, create a login with SQL Server authentication and try that. Your users might be authenicating to the application and then the application using a different login to actually access the database. I have a system like that. The application acts like a 'middle-man' and has its own login and password for the database. So, my users never really access the database. They send their requests to the application server, which then queries the database.

Another thing to try, if you haven't, is to PING the database by IP and not name. If that works, you could try setting up the ODBC connection using IP address instead of the name.

-SQLBill

Posting advice: FAQ481-4875

 

[splat78423]

I finally got it....When SQL is setup it doesnt enable these types of remote connections by default. I had to enable tcp/ip connections directly to the database and also enable named pipes.....I then stopped and restarted the SQL service..I then went to setup my ODBC connection and it took by using named pipes in the client configuration.

 

[splat78423]

By the way, thanks again for all the help guys...You've pointed me in the right direction several times and this is a fine example of persitance paying off.

cheers,
splat78423