Category Archives: Sql Server

Sql Server remote connection troubleshooting

Resolving could not open a connection to SQL Server errors.

Really helpful link for troubleshooting through the issues for why you may not be able to connect to a Sql Server remotely. It steps through several possible issues. I had to google how to fix them, once I identified them, but this was a great help in discovering some of the issues.

For me, in short it was the following:

1. Make sure you have only one firewall running (I had Norton and Windows firewalls running).

Go to Windows Firewall settings and select “Advanced settings”

WindowsFirewall

Then ensure that all of the firewalls are set to off (WARNING: don’t turn off Windows firewalls unless you have another firewall running, and don’t configure firewalls unless you know what you are doing or you could lose data, your identity information, etc. If you have any doubts, get someone to help you!)

WindowsFirewallAdvancedSettings

This is how it will look after those second, redundant firewalls have been turned off.

2. Make sure the SQL Server services are enabled (SQL Server, SQL Server Browser minimally in order to see the Server on another box).

3. Enable the communication protocol that you are using (typically TCP or Named Pipes)

4. Allow port 1433 and possibly port 1434 through the firewall and limit the connection to your local subnet, or the IPs that will be connecting to the server.

5. This was the kicker for me – go into the configuration for the TCP and enable the ip addresses that you need. For me, since this server is behind a firewall, I allowed all connections.

Hope this saves someone the extra hours I spent finding out the final steps.

Advertisements

LinqPad – Get Pdf from binary column

A friend asked me to share this technique (which rocks!) for a simple way to pull a pdf from a table without writing an application to do it. It has saved me a lot of time when I need to get to a stored pdf quickly.

LinqPad (which if you do any Linq, and in this case even if you just need to get to a binary object) is a must-have program that allows you to do this.

Here is the simple code snippet that pulls the pdf, saves it to a file, and opens it. You have to connect your database on the top right, and select “C# Statements” in the middle drop down list:

string id = "60821 003";
string dir = "C:\\TempDocs";

// create output directory
Directory.CreateDirectory(dir);

// create output filename
string outputPDFFile = Path.Combine(dir, id + ".pdf");

// find the record we want (you have to be connected 
// to the database you want to search, and
// PDFDocuments below must match the table you are pulling data from
var mypdf = (from q2 in PDFDocuments where q2.PrintBatchId == id select q2);

// above returns only one record, so below I use SingleOrDefault to get that record.
// otherwise here you could iterate through the records...
// the ".Pdf portion pf this is the column name of the pdf binary data in the table
File.WriteAllBytes(outputPDFFile, mypdf.SingleOrDefault().Pdf.ToArray());

// this part opens up the pdf for viewing / verifying I have the right one
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo.FileName = outputPDFFile;
proc.Start();

Have fun!

Iterating Through Databases on Sql Server

This is a way to find all of the databases matching a particular name on a SQL Server and process something in those databases.

USE master
GO

set ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— iterate through DBs
DECLARE @DbNames TABLE (
rowNum int identity (1,1),
dbname sysname NOT NULL )

INSERT INTO @DbNames
SELECT name
FROM sys.databases
WHERE state=0 AND user_access=0 and has_dbaccess(name) = 1
AND [name] like ‘NameToMatch%’
ORDER BY [name]

DECLARE @EndCount int;
SELECT @EndCount = count(*) FROM @DbNames

DECLARE @RowCounter int;
SELECT @RowCounter = 1;

DECLARE @DbName varchar(20);
DECLARE @sql varchar(2000);

WHILE (@RowCounter <= @EndCount)
BEGIN
SELECT @DbName = dbname FROM @DbNames WHERE @RowCounter = rowNum;
SELECT @sql =
‘USE ‘ + @DbName — do something here…
EXEC (@sql)
–PRINT @sql
SELECT @RowCounter = @RowCounter + 1
END

More on Scope_Identity()

In the past I posted a blog about the use of SCOPE_IDENTITY() instead of @@identity because the SCOPE_IDENTITY() returns the key created in the context of the scope of the currently executing script, which is likely what you intended, rather than the last key inserted on the connection you are using.

Recently I was using a stored procedure to insert a record and I used

SELECT Scope_Identity()

to return the key (defined as a bigint) of the newly created record. I then used the .net C# code:

long key = command.ExecuteScalar();

to get the value returned. This turned out to actually return a decimal type, which surprised me. It turns out that SQL Server will sometimes convert a bigint to a NUMERIC type and thus you really need something more like this:

long key = Convert.ToInt64(command.ExecuteScalar());

The need to do this surprised me, but I was reassured that this is a normal treatment of a bigint key and conversion is necessary.

Using PATINDEX to scrub data…

I was reading the local NTSSUG (North Texas Sql Server Users Group) and found a query that was a prime candidate for a regular expression, but another user suggested the use of PATINDEX.

The problem was embedded data that had a specific format: two alpha chars followed by 4 or 5 digits.

I tried a little test using PATINDEX and it worked very well:


DECLARE @zot6 varchar(7),

@zot7 varchar(7),

@test1 varchar(20),

@test2 varchar(20);

set @test1 = 'Ref PO:NY1234';

set @test2 = 'PO#MN12345XYZ';

SELECT @zot6 = SUBSTRING(@test1,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9]%',@test1),6);

SELECT @zot7 = SUBSTRING(@test1,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@test1),7);

IF (DATALENGTH(@zot7) = 7)

SELECT @zot7

ELSE

SELECT @zot6

SELECT @zot6 = SUBSTRING(@test2,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9]%',@test2),6);

SELECT @zot7 = SUBSTRING(@test2,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@test2),7);

IF (DATALENGTH(@zot7) = 7)

SELECT @zot7

ELSE

SELECT @zot6

Raising Errors in SQL Server

This link was handy in learning a little about error handling in SQL Server, though it is dated:
Error Handling in SQL Server – a Background
This article explained the usage of RAISERROR. It also suggests that TRY / CATCH blocks can be used to raise
specific errors for specific sections in a stored proc:
Using RAISERROR

Difference between IDENT_CURRENT,SCOPE_IDENTITY and @@IDENTITY

This is an important distinction and thus I am pointing to this post for reference.

SQL 7 only allowed @@identity, however now we have other properties we can access to get the most recent identity. This article by Kamal on DotNetSpider covers those distinctions. It is also covered by Microsoft HERE.

Writing BLOBs to a table in Sql Server

I have been storing large binary objects related to a database for a long time, but always ended up using a file system and storing the reference to the file (i.e. a URI, or pathname). Recently I had to actually store the object itself, and did not know where to start looking.

Since I am dealing with multi-threading and insertion of .5 gig files, I needed something that would allow me to buffer the write and not have to put the whole thing into an array before writing it. This is the solution. I know that Sql Server 2008 has a file system solution for BLOB storage and it integrates with file IO for updates, but like it seems to be so often in this career, it is too late for me to implement a new method of storage of my BLOBs. Maybe next time.

I used all sorts of search terms: binary, buffering, sql server, blob, update, insert, etc, and came up with lots of pages, but no joy until this page. It turns out that the search terms I needed were: UPDATETEXT, SCOPE_IDENTITY, and TEXTPTR. I spent a long time looking for this article and finally found and article titled:
Conserving Resources When Writing BLOB Values to SQL Server

NOTE: This was originally where this post stopped, but I have found that the command that this article uses is not going to be supported in future versions of SS, and instead we should be using UPDATE.WRITE() (available in SS2005 and beyond). The code sample above should still be helpful in integrating this new command.

Here is a blog post about UPDATE .WRITE…

Reading BLOBs in Sql Server

This post was one of the best I found on reading large binary objects from a database.
I was a bit surprised / disappointed to find that it was still writing the entire object at once in its example for writing a BLOB. So for that there is another post…
Read / Write BLOBs from / to SQL Server using C# .NET DataReader