Microsoft OLE DB Provider for SQL Server error '80004005'

Hello Group,

I need some help in the worst way. I had a program/database I was working on a particular machine. I took all the data from there and placed it on a another machine. Now I'm unable to navigate through the web pages on the new machine. I am getting the error...

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

Quote:

Originally Posted by ConfusedDeuce

Hello Group,

I need some help in the worst way. I had a program/database I was working on a particular machine. I took all the data from there and placed it on a another machine. Now I'm unable to navigate through the web pages on the new machine. I am getting the error...

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

On line 19, your connection to the database is failing. Are you on the same network segment/subnet as you previously were? What is the physical difference between the computers?|||

Quote:

Originally Posted by ConfusedDeuce

Hello Group,

I need some help in the worst way. I had a program/database I was working on a particular machine. I took all the data from there and placed it on a another machine. Now I'm unable to navigate through the web pages on the new machine. I am getting the error...

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

......asp, line 19

Can you please help!!

How are you now specifying the server name when connecting to this machine?
You'll need to allow the server port on that new machine if it's running a firewall.|||

Quote:

Originally Posted by Motoma

On line 19, your connection to the database is failing. Are you on the same network segment/subnet as you previously were? What is the physical difference between the computers?

The thing is that I'm not completely sure what the other machine had on it because they data was copied over from that machine and the machine was immediately taken away. Now I am using VMWare and virtually connected to new machine (which shouldnt be the problem). I'm not sure of any of the specs of the last machine : (|||

Quote:

Originally Posted by r035198x

How are you now specifying the server name when connecting to this machine?
You'll need to allow the server port on that new machine if it's running a firewall.

I'm not connecting to the old machine anymore. That machine is out of the office and no longer in physical reach.|||

Quote:

Originally Posted by ConfusedDeuce

The thing is that I'm not completely sure what the other machine had on it because they data was copied over from that machine and the machine was immediately taken away. Now I am using VMWare and virtually connected to new machine (which shouldnt be the problem). I'm not sure of any of the specs of the last machine : (

Do you have the SQL Server database up and running on the new machine? Have you added the appropriate accounts and permissions for the users?|||

Quote:

Originally Posted by Motoma

Do you have the SQL Server database up and running on the new machine? Have you added the appropriate accounts and permissions for the users?

Yes it is up and running. How do i add the accounts and permissions?|||

Quote:

Originally Posted by ConfusedDeuce

Yes it is up and running. How do i add the accounts and permissions?

Enterprise Manager -> Server -> Security -> Logins
Enterprise Manager -> Server -> Databases -> Database -> Users
Enterprise Manager -> Server -> Databases -> Database -> Roles|||

Quote:

Originally Posted by ConfusedDeuce

I'm not connecting to the old machine anymore. That machine is out of the office and no longer in physical reach.

Actually I was refering to that new machine. That error means that the connection to the database server was not done correctly. The reasons could be
wrong hostname being specified
firewall blocking connections
change of IP
wrong port being used or break in TCP/IP conection while accessing the database server
e.t.c

MERGING Variable in FOR LOOP COntainer

Hi All,

Seems like a simple task, but been a struggle.

Simply trying to move a group of files from one folder to another folder and renaming the files with the monthyear in the middle of the filename..

I'm using a FOR Loop container and works find. The added complexity is I'm trying to rename the files the same tine and putting the Month Year into the file name.

I guess the struggle is how to get the file name out so I can manipulate it.

I tried creating variable(V_SOURCE) which stores the file path and create another variable(V_FILENAME) to hold the filename. I believe on the expression page of the FOR LOOP editor if I select the the file name and extension radio button, it should put the file name in the V_FILENAME variable.

In my file task trying to join the two variables together in another variable, keep saying my path is wrong with the file task kicks off.

here's the syntaxt

@.[User::V_SourcePath] + @.[User::V_FILE_NAME]

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

|||

I haven't checked myself, but do you need to have a \ between the folder and file, or is it included?

You can check the value of your variables by setting a breakpoint and typing the variable into the watch window.

|||THanks is helping out getting a picture of what's going on|||

Dan Cleary wrote:

JUst to follow up if someone can help me with syntaxt for the merge and if they have a better approach

to moving and renaming the files

I have done that using the File System task. I just posted an example in my blog:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

I hope you find it helpful.

|||

Rafeal, looks like just what the docotor ordered, does the scope of the variable make a difference?

Having an issue when joining the SourcePath with the file name. The scope of those variables are at the package level not at For Loop.

I do appreciate your response

|||

Variable scope is not an issue here; if you need it, just define all the variables at the package level.

regards

|||

Rafeal your Blog was great and very useful. I uess my struggle is doing the syntaxt on the file name in the expression builder.

I'm trying to take a group of file that are currently named RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.XLS and

convert it to RPT_BANK_NAME_03_2007_BANKNAME.XLS. I have the report named storec in the variable just not sure on the syntaxt to strip out the @.MONTHYEAR and replace with the month and the year.

Any suggestions?

I tried to do a substring but not recognizing that function in the eexpresion builder

|||

Dan,

I guess I don't understand what is the format of the original name of the file. What I don;t get is the @.MONTHYEAR part.

is 03_2007 the month and year of the package execution date? or are they part of the original file name?

|||

THe @.monthyear is confusing, it's just hardcoded in the report name.

SO what I'm attempting to do in my expresion is take the Variable which is storing the report name

RPT_BANK_NAME_MONTHYEAR_BANKNAME.XLS and replace the monthyear with the curent Month and date.

I tried this but the expresion keeps failing the validation checks. Pretty much what you had in your blog

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

|||

You've got an extra 1 in the expression (see red below) After correction, and assuming V_DestinationFolder is c:\temp\, it gives c:\temp\RPT_BANK_NAME_@.MONTHYEAR_BANKNAME.-32007.XLS, which I don't think is exactly what you want.

@.[User::V_DestinationFolder] + SUBSTRING( @.[User::V_Invoice_File] , 1 , FINDSTRING( @.[User::V_Invoice_File],".",1) 1 ) + "-" + (DT_STR, 2, 1252) Month( @.[System::StartTime] )+ (DT_STR, 4, 1252) Year( @.[System::StartTime] )+ SUBSTRING( @.[User::V_Invoice_File] , FINDSTRING( @.[User::V_Invoice_File],".",1) , LEN( @.[User::V_Invoice_File] ) )

The one below returns c:\temp\RPT_BANK_NAME_3-2007_BANKNAME.XLS and uses the Replace function. However, I have not tested it in the context of Rafael's example, so you might need to make some modifications to get it to work for you.

@.[User::V_DestinationFolder] + REPLACE( @.[User::V_Invoice_File] ,"@.MONTHYEAR", ((DT_STR, 2, 1252) MONTH( GETDATE() )) + "-" + ((DT_STR, 4, 1252) YEAR( GETDATE() )) )

|||

John is spot on.

In your case, REPLACE is a better option since '@.MONTHYEAR' is a literal that is always part of the file name. The expression in my example adds the month and year at the end of the file name and before the file extension (.txt); so I used FINDSTRING.

BTW, notice the expressions we are given use @.system::startTime; which gives you the month and year of the package execution date; so make sure that meets your requirements.

|||

Guys, you've been a huge help! I'm doing a watch and looking at the V_DESTINATION_PATH and it shows the

following in the watch window

+ User::V_DestinationPath {E:\\Client Billing 2\\FTP_Incoming\\INVOICE\\RPT-234-3-2007-BILL234.xls} String

but when running the task getting a path error

[File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.". , any idea?

|||Looks like your variable V_Destination_Folder contains too many slashes, perhaps.|||

When you view it in the watch window, it shows the escape characters, thus the doubled slashes. You can validate the value by using a script task with a MsgBox.

Dan, you might want to double-check that the folder and filename you are referencing exist. Also, check permissions to ensure you can write to that location.

Merging two tables then grouping them

I have two tables, one is named Employee and the other Job_title. I'm trying to combine the two tables so I can group certain columns.

This is what I thought of so far and please correct me if I'm wrong.

SELECT Last_name FROM Employee
UNION
SELECT Job_title_code FROM Job_title
GROUP BY Exempt_non_exempt
FROM Job_title

Now this is just a theory (obviously it doesn't work) but basiclly I'm trying to have columns from two tables and have the grouped.Huh?

Wha?

There is no relationship between these tables?
No foreign keys?
You are selecting a single column and performing GROUP BY without any aggregate functions?

This makes no sense.|||Well, in both tables, they each have a job_title_code column.

Employee table
lastname, firstname, job title code

Job_title Table
job title code, job title, salary, exempt/non exempt|||Is this homework?

It looks like homework.
It sounds like homework.

Are we studying relational databases?|||So why aren't you using a join?

SELECT Last_name,
Job_title_code,
Exemp_non_exempt
FROM Employee
INNER JOIN Job_title on Employee.Job_title_code = Job_title.Job_title_code

I don't think this is homework. If it was homework his question would be more clearly phrased!|||sorry for the mix up, yes it is homework. that's for suggesting the INNER JOIN command. i found a variation of what you did and it worked out for my db. by the way, how did you put your code in a window like that?

SELECT Employee.Last_Name, Job_title.Exempt_non_exempt_status
FROM Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code|||Enclose your code in CODE tagsL

[XCode]
Your code here
[X/Code]

Remove the X characters...

Your code here