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

Had a totally weird thing ahppen to a sql server today.

I have 2 linked servers and for some unknown reason all queries to the linked server were being met with that error. I removed the linked server, re-added, tested i could connect to the port with telnet - all good.

Check remote connections enabled, checked tcpip enabled. Evrything seemed fine.

One of our machines rebooted with some critical update from Microsoft so wondered if something MS had pushed out may have caused the issue.

Anyhow, we finally fixed the issue but i am still 100% sure exactly what is the problem so maybe someone may be able to shed some light on things.

The way got around it was to remove the liked server and re-add it - but instead of adding it as a SQL Server, it was added using and OLEDB sql data source. After doing that it all sprung back into life.

Now to me that sounds like an issue with SQL Native Client on either the client or server. I wonder if microsoft has done something to some drivers somewhere along the line and pushed it out in a critical update???

Another strange this is i could not add a system datasource to point to the other server either - unless i specified the default port 1433! Totally whacky. The server i was trying to link was a named instance of sql2005. No idea why specifying the port it should have been using anyway worked - but it did!

Hope this sorts you out if you are having this problem. Had me stumped for about 3 hours.

Id be interested to hear your comments if you have an ideas of input. Its gonna bug me until i found out exactly whats happened as it was working fine the night before.

SSIS Package Execution from TSQL

I have been learning some SSIS and created a file transformation package to load and convert some data. I have come across 2 major headaches in doing so that i thought i would share.

1st - the deployment. This seemed so complex when i first set about doing it. If you follow the MS articles in BOL you will get most of the way there but they omit the part about security settings. When you deploy the build & deploy package id you encrypt sensitive data with user key it will now work as it will try and use your local workstation account which will not reside on the server. Basically you will need to tell it not to save sensitive data (property setting on the control flow page).

Also if you deploy the settings to a sql server table it appears to save the passwords encrypted! Truth is that it doeasnt save the passwords for user connections to databases etc! So the only way i found to get it to work is to store the password (which is in plain text) in the config! I just dont get that bit - just doesnt seem secure to me.

Next biggy was getting the package to run using the dtsexec util. I wanted to fire in the values for 2 variables i had to allow the package to be dynamic so i could re-use it for multiple files.

Here is the command line i tried running:

EXEC xp_cmdshell

'dtexec /DTS "\File System\DataLoad\DataLoad" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /SET "\Package.Variables[User::FileName].Properties[Value]";"c:\data\01052008b.txt"'

Do you see the problem?? No - didnt think so. Its just not obvious. What you cant see from the text above is some CRLF's (line breaks) the i put in improve readability while i was debugging. Turns out that all that will get passed to the package is the text up to the first CRLF!!! Hence my /SET wasnt getting parsed!!

Talk about head/brickwall!

So make sure you keep your command executions on a single line! Even is its huuuuuge!

OPENDATASOURCE or OPENROWSET linked server Error fix

This drove me mental for a while.

If you are trying to open an external file such as a TXT, XLS, CSV using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

The Errors:

OLE Database provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE Database provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


The fix:

I tried all the suggestions about SP2, restarting services, setting flags in the SQL settings etc but all to no avail.

What you need to do is visit the link below and download the

2007 Office System Driver: Data Connectivity Components download

Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

SERVER\Server Objects\Linked Servers\Providers

The new driver is called:

Microsoft.ACE.OLEDB.12.0

All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

Your new connection string should look something like this:

OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
''Data Source=C:\data\;
Extended Properties="Text;HDR=No;FMT=Delimited"'')...['+ @FileN +'#CSV]'

Voila! That should sort you out.

Find out what stored procs have changed

Been ages since ive posted on here as ive been in a cave working on a big project! Ive finally come out and found time to post something useful i found.

I wanted to know what had changed in a database in the last x days to check for things before i did a backup/restore so found the following queries useful!

This fella will tell you the stored procedures that have been modified in the last x days:

SELECT NAME ProcName, create_date Created, modify_date Modified
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 3 --Change 7 to any other day value
ORDER BY 3 DESC

This fella will tell you the stored procedures that have been created in the last x days:

SELECT NAME ProcName, create_date Created, modify_date Modified
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 3
ORDER BY 2 DESC

Very handy!

Fantastic List of Top Tips for Reporting Services

As ive begun digging deep into Reporting Services for a new project ive been hunting around for good resources. In doing so came across this list of extremely well put together tips, tricks and best practices.

Some great things in here that i will be definitely putting to good use.

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx

Granting Execute Permissions to a user in SQL2005

I found it a chore to create execute permissions on all stored procedures in a database.

This query will do the job for you:

CREATE A ROLE AS FOLLOWS:

1.
/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

2.
AND THEN when creating user, assign db_executor as role rather than dbo.


3.To assign user to all stored procedures in database run this.

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0

TrainingSpot.com

The team behind LearnVisualStudio.net have been hard at work behind the scenes again and produced another fine training website TrainingSpot.com. This site is tailored to SQL Server, Sharepoint, TSQL, Biztalk and Relational Database Design and Implementation. I'm sure these categories will also expand over time.

As a member of LVS they are currently offering a voucher code for 25% off the price which you will find on the member pages. If you are not a member click one of the banners at the top of my blog to take you to the sign up pages.

Ive gone through approximately 50% of the initial content releases on TrainingSpot.com (and they have already released the next set of videos on SQL Server 2008) and i must say im impressed again by the quality of the video training on offer. The thing that beats me is the price - its SOOOOO CHEAP! I think these guys are selling themselves short but on the other hand its nice to see someone offering a quality service for an amazing price.

If you are interedted in software and database design and development then i cannot recommend learnvisualstudio.net and trainingspot.com enough. For the price of a good book you will receive a LIFETIME of training!!