Archive for category SQL Server

SQL Server – Get data between two dates

This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:

select * from
tableName
where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and
datediff(day,'2011-06-28 12:28:34.480',theDate)<=0

The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:

select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')

It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.

If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.

For SQL Server 2000:

select * from
tableName
where datediff(day,?,theDate)>=0 and
datediff(day,?,theDate)<=0

For SQL Server 2005+:

select * from
tableName
where datediff(day,@StartDate,theDate)>=0 and
datediff(day,@EndDate,theDate)<=0

Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).

Advertisements

Leave a comment

How to write a Trigger in SQL Server

Just a quick post with a simple example trigger I put together. I’ve noticed allot of people seem to think that cursors are necessary to write a trigger, not so. In fact cursors are a bad way of doing things in general. They’re slow and run one command at a time, they loose out on the speed increase of running an update in batch. Al-Farooque Shubho touches on this concept in his article “Understanding “Set based” and “Procedural” approaches in SQL”.

Here’s the trigger, it’s written to audit changes on a date field. You need to create a table to handle the insert for this. Remember two things here:
1) The new table needs to have exactly the same data types (and null ability) of the parent table, but NOT the default values.
2) The primary key of the origin table will NOT be the same in the audit table. This is because a row can be changed multiple times, auditing those changes means the same primary key being inserted over and over. In my example I create a new column called EventOccuranceTime which is defaulted to getdate(). I then used the primary tables unique identifier plus the EventOccuranceTime as the PK in the new table. Note that triggers can seriously slow down your data access, putting a primary key on the audit table will slow down updates / inserts into the parent table more yet. If this speed reduction is an issue for you you may not want a primary key on the audit table, just take the hit when reading from the data (no index) instead of writing it with an index. I’ve included the table create script below as well:

CREATE TABLE [dbo].[SomeTable_audit](
	[ROWID] [int] NOT NULL,
	[EventOccuranceTime] [datetime] NOT NULL,
	[FromDATE] [datetime] NULL,
	[ToDATE] [datetime] NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
(
	[ROWID] ASC,
	[EventOccuranceTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SomeTable_audit] ADD  CONSTRAINT [DF_SomeTable_audit_EventOccuranceTime]  DEFAULT (getdate()) FOR [EventOccuranceTime]
GO

Something to know about triggers, they have two special tables available in them. One is the rows inserted, the other the rows deleted. If you made updates then they are tables that hold all the rows that were updated (treating old data as deleted and new data as inserted), even if only one column was updated.
If you change this trigger to include inserts and deletes the join below will thwart that. You will need to change this join to allow left and / or right joins depending on inserts / deletes, or write multiple queries, one for inserts, one for deletes, one for updates. You can write more than one query in a single trigger.

create trigger dbo.audit_SomeTable on dbo.SomeOriginTable for update -- This could include insert and delete, mine only handles updates.
as
if update(desired_date) --if the desired_date field is changing do the insert
begin
--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
	insert into dbo.SomeTable_audit (rowid,FromDATE,ToDATE) 
	select i.rowid,d.desired_date,i.desired_date
		from inserted i
	join deleted d on
		i.rowid = d.rowid 
	where datediff(day,d.desired_ship_date,i.desired_ship_date)<>0 --added because update() doesn't always seem to work, 
	--I believe it determines it's being updated if it's passed in the query, not if it's actually different.
end
go

Here’s a great MSDN magazine article with more examples of triggers (including triggers that prevent certain kinds of updates):
Exploring SQL Server Triggers.

Leave a comment

Copy Data from Excel to SQL Server (or any DBMS) Script to generate Insert Statements in Excel

Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)

That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.

Sub GenerateInsertRecords()
'Code thanks to https://anthonystechblog.wordpress.com
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
    Dim i As Integer, ii As Double, iColumn as integer
    Dim str As String, strInsert As String, strComplete As String
    str = ""
    str = "insert into TABLE_NAME_HERE ("
    For i = 1 To 999
        If Len(Cells(1, i)) < 1 Then Exit For
        str = str & Cells(1, i) & ","
    Next i
    str = Left(str, Len(str) - 1)
    str = str & ") values ("
    
    Dim oConvert As Variant
    strInsert = str
    strComplete = ""
    For ii = 2 To 65000
        If Len(Cells(ii, 1)) < 1 Then Exit For
        strComplete = strComplete & strInsert
        str = ""
        For iColumn = 1 To i - 1
            oConvert = getInput(Cells(ii, i))
            str = str & oConvert & ","
        Next iColumn
        str = Left(str, Len(str) - 1)
        str = str & ")"
        strComplete = strComplete & str & vbNewLine
    Next ii
    
    Dim oFS As Scripting.FileSystemObject
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Dim oText As Scripting.TextStream
    Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True)
    oText.Write (strComplete)
    oText.Close
    MsgBox ("complete")
End Sub

Function getInput(theInput As Variant) As Variant
    Dim retval As Variant
    If IsNull(theInput) Then
        retval = "Null"
    ElseIf UCase(theInput) = "NULL" Then
        retval = "Null"
    ElseIf IsNumeric(theInput) Then
        retval = theInput
    Else
        retval = "'" & theInput & "'"
    End If
    getInput = retval
End Function

2 Comments

How to Script Copying a database in SQL Server

This always seems to be an issue that crops up. Copying a database sounds like something that should be simple to do, in practice there are a number of steps, and varying methods to do so. I had a couple co-workers who wanted a nightly refresh of test data, they were manually copying the database which was taking quite a bit of time so I put this together for them.

I’ve broken the copy into two parts since depending on your needs you many find each useful on their own.

Part 1) How to script a backup of a MS SQL Server Database:
There’s lots of examples on how to do this on the internet, I’ve used variables and separated mine into exec statements since many of the backup and restore commands don’t accept variables as input (annoying!). Here’s the script:

--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
use master
go
declare @dbToCopy varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000)
--You will need to point this at your log file directory
set @dbLogsDir = 'c:\temp\'
--You will need to point this at your DB file directory
set @dbDatabaseDir = 'c:\temp\'
set @dbToCopy = 'dbToCopy'
set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)'
exec(@sql)
set @sql = '''' + @dbLogsDir + @dbToCopy + '_LogBAK.LDF'''
set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql
exec(@sql)
set @sql = '''' + @dbDatabaseDir + @dbToCopy + '_Data.BAK'''
set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql
exec(@sql)

If you run that you will have your backup log file and db. To attach it as a different DB name use this:

--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
use master
go
declare @dbToCreate varchar(1000), @filePrefix varchar(1000), @sql varchar(1000), @dbLogsDir varchar(5000), @dbDatabaseDir varchar(5000)
set @dbLogsDir = 'c:\temp\'
set @dbDatabaseDir = 'c:\temp\'
set @filePrefix = 'dbToCopy'
--Assuming your DB's logicalName is 'DATABASE_Data' and your log's name is
--'DATABASE_Log' @filePrefix will need to be 'DATABASE'.  This script doesn't
--support logical names that vary more than this (you would have to extend
--it to get that)
set @dbToCreate = 'dbToCreate'
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
  set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
--needed to kick users out of our to be created db and keep them out.
--(backup restore can create un restore-able files if you don't do this)
  exec(@sql)
end
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
  set @sql = 'drop database ' + @dbToCreate
  exec(@sql)
end
set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF'''
exec(@sql)
set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user'
exec(@sql)

-- These may fail on your server as xp_cmdshell is probably disabled.
--For security reasons it is probably best left disabled,
--but if you do want it on (and have admin privilege) this is how you do it.
set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
--this will delete the log file back up from the earlier step
exec(@sql)
set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
--this will delete the db file back up from the earlier step
exec(@sql)

I decided to take all of this and create an easily run stored procedure from it, this is the stored procedure:

create procedure spCopyDatabaseEx(@dbToCopy varchar(1000), @dbToCreate varchar(1000), @filePrefix varchar(1000), @dbLogsDir varchar(1000), @dbDatabaseDir varchar(1000)) as
--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
declare @sql varchar(1000)
set @sql = 'DBCC SHRINKDATABASE (N''' + @dbToCopy + ''', 0,TRUNCATEONLY)'
exec(@sql)
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
	set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
	exec(@sql)
end
if exists(select * from master.dbo.sysdatabases where name = @dbToCreate)
begin
	set @sql = 'drop database ' + @dbToCreate
	exec(@sql)
end
set @sql = '''' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
set @sql = 'backup log ' + @dbToCopy + ' to disk = ' + @sql 
exec(@sql)
set @sql = '''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = 'backup database ' + @dbToCopy + ' to disk = ' + @sql 
exec(@sql)

set @sql = 'restore database ' + @dbToCreate + ' from disk = ''' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
set @sql = @sql + ' with replace, move ''' + @filePrefix + '_Data'' to ''' + @dbDatabaseDir + @dbToCreate + '_Data.MDF'',move ''' + @filePrefix + '_Log'' to ''' + @dbLogsDir + @dbToCreate + '_Log.LDF'''
exec(@sql)
set @sql = 'ALTER DATABASE ' + @dbToCreate + ' SET multi_user'
exec(@sql)

set @sql = 'xp_cmdshell ''del ' + @dbLogsDir + @dbToCreate + '_LogBAK.LDF'''
exec(@sql)
set @sql = 'xp_cmdshell ''del ' + @dbDatabaseDir + @dbToCreate + '_Data.BAK'''
exec(@sql)
go

To try it run this:

exec spCopyDatabaseEx 'northwind', 'northwindNew', 'Northwind', 'e:\mssql\logs\', 'e:\mssql\data\'

Best of luck! As always feel free to ask questions / make suggestions. I’m happy to answer your questions.

Leave a comment

More on How to tell if a user is using a weak password

So after yesterday’s post, “Password encryption in SQL Server, how to tell if a user is using a weak password“, I was curious so I prodded a bit further. I wanted to know just how secure (or not) passwords in SQL Server are. Now remember that you have to have access to the sysxlogins to be able to do this, so the very first rule is: NO ONE should have access to sysxlogins! The second one is: Protect your administrator id’s and passwords! The third rule is: Consider migrating to integrated authentication if you’re serious about security, that way the passwords aren’t stored in SQL Server in the first place.

If you’ve not heard of it, there are quite a few sites that list the ‘top worst passwords to use‘. Using a list like this you can easily populate a SQL server database, if you really want to get all the bad passwords add all the words in the dictionary as well (though it will significantly slow down the query you’ll run later).

Your table should be one column, call it ‘password’. Once you have this table you can run this sql: (change the bold text to match your table name)

select l.name, bp.password from master.dbo.sysxlogins as l, 
master.dbo.badPasswords as bp
where pwdcompare(bp.password,l.password)=1

Depending on how many passwords there are in your password database will depend on how long this will take to run. Also it could be more efficient. For example this query is running pwdcompare for every column in the password database, even if it’s already found a match. You could have it exit as soon as a match is found to make it much faster. In any case it will likely give you a scary list showing you just how many people are using ‘bad passwords’.

For completeness you can check how many people have passwords the same as their id by running this:

select l.name from master.dbo.sysxlogins as l
where pwdcompare(l.name,l.password)=1 or pwdcompare(lower(l.name),l.password)=1

That one doesn’t require a table be set up, you can run it right now!

So now that I have you sufficiently paranoid, I hope you consider migrating to integrated authentication!

EDIT:
This query is much faster for determining the bad passwords (note: it only saves time for users that are using a bad password, users that can’t be found still have to be checked vs every pwd in the table). On my machine the query above takes 28 seconds, this new one takes 17.

select name, pwd from 
(
select sysx.name, 
(
select top 1 bp.password from master.dbo.badPasswords as bp
where pwdcompare(bp.password,sysx.password)=1 
) as pwd
from master.dbo.sysxlogins as sysx
) as results
where pwd is not null

Leave a comment

Password encryption in SQL Server, how to tell if a user is using a weak password

I’ve made some interesting discoveries recently. I’ve used MS SQL Server for years now, but never gave much thought to how user ids and passwords are stored internally, until today.

Today we had an issue deleting a user from the server, for some reason the delete method was unable to delete the user, but it was able to tell me a vague reason why it failed. This is annoying as I think the delete should be able to handle all situations, but really it’s ultimately no one’s fault but my companies for using SQL Server 2000 for so long.

The error I received was:
“Login ‘TheLoginName’ is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (.Net SqlClient Data Provider)”

Pretty annoying, no? I read a number of articles and wound up cleaning up the issue, I won’t go into that issue in any further detail since this article is about password encryption, perhaps another time. In the process of fixing this issue I started checking out just how SQL server manages user id’s and passwords. If you’ve never seen it there is a table that SQL Server uses that stores all user id’s and passwords, to view it run this:
select * from master.dbo.sysxlogins
apparently this works on newer versions of SQL Server:

SELECT * FROM sys.sql_logins 

In this data you’ll notice there is a ‘password’ column. It will look something like this:
0x0100813F782D66EF15E40B1A3FDF7AB88B322F51401A87D8D3E3A8483C4351A3D96FC38499E6CDD2B6F9C01AD6BD

This password is stored with one way encryption, the method SQL server uses to create this password (marked as deprecated btw) is pwdencrypt. You can view the output of pwdencrypt by running this:

select pwdencrypt('test')

Note that if you run it multiple times the hash is different, it will not give you the same hash for a single password.

Note that if you wish to avoid writing the Hash & Salt code necessary to have strong password storage and are eyeing up pwdencrypt, don’t. Microsoft has provided a better (not deprecated) method to do password hashing with hashbytes.
eg of using hashbytes:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO

So, it seems I’ve been taking a bit of the scenic route getting to the point of this article.. sorry! There were lots of good tidbits I found out on this journey of mine that I wanted to share!

Here’s the guts of how to tell if a user is using a weak password Microsoft SQL Server provides a method for comparing passwords called pwdcompare. This method almost seems like a security issue to me, but here it is. Do you have users that tend to use the word ‘password’ as their password? Run this to see who they are:

SELECT name FROM master.dbo.sysxlogins	
WHERE PWDCOMPARE('password', password) = 1

Use this on newer installations of SQL Server:

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('password', password_hash) = 1 

Now, this method, PWDCOMPARE, seems to me to open up MS SQL Server users to dictionary attacks. It wouldn’t take much to run this method against a 1000 or so passwords to take a bunch of user’s passwords, but I digress. It is pretty useful to tell your users to smarten up and not use weak passwords.

Also interesting on the password migration note. If you need to migrate passwords from Microsoft SQL Server 2005 (or higher I’d imagine) run the script available on this page:http://support.microsoft.com/kb/918992. If you need to migrate passwords from Microsoft SQL Server 2000 replace the sp_help_revlogin procedure with the one below (Thanks Wardy IT).

As always feel free to post your comments / questions below. I always enjoy hearing your thoughts!

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name  'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status  -1)
BEGIN
  IF (@@fetch_status  -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus &amp; 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus &amp; 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus &amp; 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus &amp; 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Leave a comment

Infor ERP – Visual Enterprise

Infor ERP – Visual Enterprise is something of a niche product.  Try searching the internet for it and you’ll see what I mean.  There are very few hits and even fewer sites that offer help documentation on how to develop for it.

Well, finally I found a blog written by a fellow that seems to know quite a bit about Visual, check it out.

One of my favourite articles is where he details how to easily decode the BINARY fields in Visual tables.  He uses views for his solution so it requires you to write a query that joins the view to the table.

I extended his solution somewhat, though mine is not for the faint of heart.  There is a great feature of SQL server called User Defined Fields (UDFs).  To use UDFs to get decoded Binary fields in Visual without having to join to another table follow these steps:

1) Open the Visual database and then open the table you want to ‘fix’ in design mode. (that’s right, we’re changing the design of the Visual tables themselves.  I told you this wasn’t for the faint of heart.)
2) Add a new column to the END of the list.  I called mine ‘BITS_PLAIN’
3) Don’t give it a datatype, instead (using Microsoft SQL Server Management Studio) expand the ‘Computed Column Specification’ in the bottom and enter the following formula:
(convert(varchar(5000),convert(varbinary(5000),[BITS])))
This will convert the first 5000 bits to 5000 chars for reading.  If you are running on a newer version of SQL server you should be able to use this:
(convert(varchar(MAX),convert(varbinary(MAX),[BITS])))
Which won’t truncate the data arbitrarily.
4) Save your changes.

The new SQL won’t slow down queries that don’t include that column in the select statement, but it’s great for standardizing where to get the binary data in your applications.  Unfortunately this doesn’t solve the issue of writing the data back to the database as you can’t write to the UDF.  More on that in a future article.

Leave a comment