Tag Archives: SQL Server

Parse JSON with TSQL in a SQL Server Stored Procedure

Posted on by
Reply

json_logo-555px

I’m a big fan of JSON as a data exchange format, it’s lightweight, and every API worth playing with is usually JSON-friendly. So in idle curiosity I wondered if it was possible to parse JSON with TSQL, and guess what, someone else has come up with a way to do so.

A hat tip to Phil Factor (and RedGate for their great Simple Talk blog) for his article Consuming JSON Strings in SQL Server. Phil not only has example code to parse JSON into a table format, he shows you how to go the other way – create JSON from table output.

SQL Server does handle XML, but let’s be honest, it’s a pain in rear to use. But it’s all we’ve got natively with SQL, as Phil says:

TSQL isn’t really designed for doing complex string parsing, particularly where strings represent nested data structures such as XML, JSON, YAML, or XHTML.

You can do it but it is not a pretty sight; but why would you ever want to do it anyway? Surely, if anything was meant for the ‘application layer’ in C# or VB.net, then this is it. ‘Oh yes’, will chime in the application thought police, ‘this is far better done in the application or with a CLR.’ Not necessarily.

Sometimes, you just need to do something inappropriate in TSQL.

I gave the code a quick try parsing JSON into a table and it worked great.

 

How to locate text string in a SQL server object – stored procedure, view or function

Posted on by
Reply

Sometimes it’s handy to just conduct a simple search of your SQL server objects to locate a text string.

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%text%'

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'isView') = 1
AND definition LIKE '%text%'

SELECT OBJECT_NAME(object_id),definition
FROM sys.sql_modules
WHERE
(OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1)
AND definition LIKE '%text%'

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyName%'

An item with the same key has already been added (SQL Report Services)

Posted on by
Reply

Screen Shot 2014-02-26 at 7.42.34 AM

 

I was creating a new report in SQL Reporting Services, I pasted in the SQL query for the report and up popped the message “An item with the same key has already been added”.

Turns out I had two columns with the same name – which as always with these error messages, begs the question, why not just say so!

Copy Azure SQL Database to local machine (SQL 2008 R2)

Posted on by
Reply

You would have thought in these days of point and click a simple task like copying a database from one place to another would be a walk in the park. So when I wanted to take a copy of a SQL database from Azure and move it to my local machine, I thought it surely would be straightforward. It’s not. Here’s what I came up with:

In Azure make sure you have a Storage account set up, with a container inside. My container is called ‘files’.

export1

In Azure go to your database. Click the Export button down the bottom.

export2

A new window will pop up. The file name will probably be pre-filled. Select your blob storage account (if you only have one then it’s already selected). Select a container (mine is ‘files’). Enter your SQL Server log in name and password – these are the credentials you set up when you added the database. Click OK and the export should start – you’ll see the status down the bottom.

export3

Once the export is done, you can click to view your Storage container, and you should see the export file there.

export4

The trick now is to get the file from Azure to your local machine. I use Azure Storage Explorer, it’s a free download. Download the file from your container.

export5

If you have SQL Server 2012, then you are home free, because you can import the BACPAC file in SQL Management Studio. Right click on the Databases folder under a server in the Object Explorer and choosing “Import Data-tier Application.

If like me you are on SQL Server 2008 R2, you need some help.

Create a new blank database on your local machine.

Go to  http://msdn.microsoft.com/en-us/jj650014 and install the SQL Server Data Tools for Visual Studio 2010. After install go and find SqlPackage.exe, I located it in C:\Program Files\Microsoft SQL Server\110\DAC\bin. Open up a DOS prompt and use the following command:

SqlPackage.exe /a:Import /sf:c:\Desktop\FILENAME.bacpac /tdn:DATABASENAME /tsn:SQLSERVERNAME
  • FILENAME.bapac is the file you downloaded from Azure, whereever it is located on your machine.
  • DATABASENAME is the name of the new blank database you created a minute ago.
  • SQLSERVERNAME is the name of the SQL server on your local machine.

I followed these steps, albeit for a fairly small file, and it worked perfectly.

nvarchar(max) doesn’t work on Azure SQL with Classic ASP

Posted on by
Reply

stress

This is one of those completely obtuse issues that one hits from time to time – nvarchar(max) fields behave oddly on Microsoft Azure SQL – yet it all works fine on SQL Server 2008 on my local machine.

I battled this for an hour before finally simultaneously figuring out that trying to return data from an nvarchar(max) column via Classic ASP on Azure yields a blank value (or weird binary garbage); and falling over someone else who’s had exactly the same problem recently.

It’s fine to have a table with nvarchar(max) in Azure. I can push data into it, I can see the data via SSMS, but returning via ASP – nada.

I’m returning the data via a UDF, so I altered the table definition returned from the function so the column is nvarchar(4000). My function grabs the data from the (max) column and sticks it into the output column without trouble, and the function returns values fine, don’t even need a CAST.

It’s frustrating because I’m anticipating these columns to hold large blocks of text, and now I’m size restricted.

I haven’t had time to explore whether messing with the connection strings might do the trick.

Convert decimal/dotted IP number to hex string

Posted on by
Reply

I had need today to pull a bunch of IP numbers from a database and return them as hex strings from a query to my code. It took a little messing, but finally figured out a solution.

DECLARE @b varchar(30) SET @b = '64.233.160.0'

SELECT 
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 3) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 2) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 1) AS int)), 2),2)

The above returns ’40E9A000′. Breaking it down:

— gives me the first octet as an integer

CAST(PARSENAME(@b, 4) AS int)

— converts the integer to a hex literal

CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int))

–converts the hex literal to a string

CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2)

–grabs the right hand 2 characters of the hex string

RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)