Developing Matt

My Technical Journal

Archive for the ‘T-Sql’ Category

Special Characters

leave a comment »

It might be hard to work with special characters in your data.  For instance, doing a replace on a field that looks like it has a blank (but is actually is carriage return) won’t yield the results you are seeking.  Instead, replace your special characters using the char function:

char(9) : tab

char(10): line feed

char(13) carriage return

If you want to find the others use this query:

SELECT ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char),50))
,ASCII( SUBSTRING(column_char,PATINDEX(‘%[^A-Z]%’,column_char)+1,50))
FROM @tbl

Or if you are looking for a function I found this better approach from here by Christian d’Heureuse:

— Removes special characters from a string value.
— All characters except 0-9, a-z and A-Z are removed and
— the remaining characters are returned.
— Author: Christian d’Heureuse, http://www.source-code.biz
create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ”
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

Thank you Christian for this tremendous help!  (script from http://www.source-code.biz/)

(I only copy the script here in case Christian decides to take down his website as this has happened to me in the past)

Written by matt

October 1, 2012 at 3:09 am

Posted in T-Sql

What’s the umbrella for? (part 2)

leave a comment »

Earlier I wrote a post (What’s the umbrella for?) It seems like I’ve written a couple of posts regarding searching in the database for objects with text in them.  Here is the latest way I’ve done it.  This method seems to return more results.  I should script it all out and make it fancy but I needed to get to the end result instead of wasting company time on the process.

So in order to search all objects in a database for the word FADRIZZLE I first get a list of the databases with

Select name from sys.databases order by name

I paste these names in to Excel, which I think has two L’s but I’m not sure.

Then I use this at the top of my spreadsheet

create table #ttDbList (DatabaseName varchar(50), ObjectName varchar(100))

and add the formula against my database name list

="insert into #ttDbList select ‘" & A2 & "’ as db, o.name  from " & A2 & ".sys.sql_modules m  inner join " & A2 & "..sysobjects o on m.object_id=o.id where [definition] like ‘%FADRIZZLE%’"

If it is a 2000 database I use the following formula instead

="Use " & A2 & "; insert into #ttDbList  SELECT ‘" & A2 & "’ as ‘database’, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%FADRIZZLE%’ "

Copy and paste the results to your query window.

Now you can run it and it will query all the databases on the server for your special word FADRIZZLE.  You’ll get errors for offline databases and it doesn’t discriminate against comments.

Written by matt

January 15, 2012 at 3:28 pm

Posted in T-Sql

Write to a text file without xp_cmdshell

leave a comment »

There are dangers of xp_cmdshell.  If turned on you can execute some pretty nasty commands.  Combine that with a poorly written dynamic sql procedure and you have a hackers delight.  It’s a window to the windows of the machine’s soul.

Many places don’t turn it on, and attempts to use it will give you:

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

I don’t blame them.  Unfortunately it takes out an opportunity to combine it with bcp and write to a file. 

The way you can bypass this is by using a job, adding a step that has a cmdExec step that executes bcp to write to a file. 

bcp "select field1, field2 from server.database.dbo.table"  queryout "c:\test.txt" -T –c

bcp bol

I’m still trying to understand ole automation, which isn’t turned on either.

Written by matt

January 14, 2012 at 5:57 pm

Posted in Sql Server, T-Sql

Database Info in HTML format

leave a comment »

Someone gave this to me back in 2007.  I don’t know the source.  It doesn’t completely work and needs some fixing, but it gives you everything you ever wanted to know about a database in html format – users, tables, views, sprocs, etc.  It will alleviate you having to search the database for everything yourself.   Run it and output results to a file ending in html, then open it.  Even with the errors you can get an idea of what it is doing.  Some rainy day I’ll need this and then I’ll fix it and you will see it’s worth, which is most of the stuff in my garage.

This is akin to me finding a broken something in the attic that I made from my childhood and trying to convince you it’s worth something.    Enjoy!

link

Written by matt

January 10, 2012 at 4:29 pm

Posted in T-Sql

If you can’t obtain dbschema_tables_info

leave a comment »

 

After creation of a new linked server (from 2008 sql box to 2000 sql box). I tested my select query and received

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERNAME". The provider supports the interface, but returns a failure code when it is used.

KB article here suggests an update to the 2k box. 

Pinal Dave suggests something that doesn’t apply to me here

For me, I was unable to update the sql box so I had to depend on openquery (link).  Openquery is pretty picky on it’s syntax, so if you get this

"Deferred prepare could not be completed."

Try applying a few brackets:

SELECT *
FROM OPENQUERY(linkedServerName,’select *   FROM [linkedservername].[database].[dboorwhateverschema].[table]’)

Written by matt

December 29, 2011 at 7:12 am

Posted in Sql Server, T-Sql

Pivot Without Aggregation or Aggravation

leave a comment »

I needed to flip my data horizontally.  I didn’t need to aggregate anything.  I was just working with a user that wanted the data to look different.

Instead of this

Car Tire
Car Window
Car Wipers
Car Engine
Car Air Freshener

 

He wanted

  col1 col2 col3 col4 col5
Car Tire Window Wipers Engine Air Freshener

 

The pivot command requires an aggregate.  It’s the basis of the command. When you are pivoting you are saying ‘hey give me some number crunching for this particular arrangement of data:

PIVOT (calculation(on what) for the following column headings (column A, column B, column C) as pvt

So how can this be done without aggregating?  Well if your group has only one calculation then you can do whatever calculation you want.  For example, the average of 62 will always be 62.  So using the rownumber in conjunction with a max on a string we can cheat and get the pivot command to give us what we want.

I was so close to figuring this out when I found a great post by Arie Stern here.  I used his post to get this concept working.  Thank you Mr. Stern for your assistance.

Set up your table here for this example:

create table #rrr(vehicle varchar(20), part varchar(20))
insert into #rrr values(‘CAR’,’Tire’)
insert into #rrr values(‘CAR’,’Window’)
insert into #rrr values(‘CAR’,’Wiper’)
insert into #rrr values(‘CAR’,’Engine’)
insert into #rrr values(‘CAR’,’Air Freshener’)
Select * from #rrr

The way to pivot this data is as follows:

select vehicle, [1], [2], [3],[4],[5]
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr)
a
pivot (max(part) for rownum in ([1],[2],[3],[4],[5])) as pvt

Note that I am using the rownumber to get my column headings and am using max for the part types.  So this works great, of course, only if you know how many rows there are per vehicle.  Restated: the above works only because I know there are only 5 rows of data. Data is hardly this friendly.  So the catch is: if you don’t know how many rows you will have you will have to build your sql out of dynamic sql.  If you are careful you won’t be writing dynamic sql inside of dynamic sql which will either drive you mad or will drive the person mad behind you that has to maintain your code.  Regardless, here is how you would do it.  :

———-step 1: set up your column names based upon rownumber———————-
declare @rownum varchar(50)
set @rownum = ”
select @rownum = @rownum + ‘[‘ +  cast(rownum as char(1))+ ‘],’
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr)
a
set @rownum = left(@rownum, len(@rownum)-1)
print @rownum
–Note that i’m using brackets around column headings.  You could add a more descriptive
–column name but will have to cast your rownumbers later on (Just wanted to keep it as simple as possible).
–Also, note that you have to strip the last comma from your column list

———-step 2: create and call your dynamic sql using your new rownumber variable———–
declare @sql varchar(1000)
set @sql =
‘SELECT vehicle, ‘ + @rownum + ‘
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr
) a
pivot (max(part) for rownum in (‘ + @rownum + ‘)) as pvt   

print @sql
execute (@sql)

Watercolors 1

Written by matt

November 2, 2011 at 4:09 pm

Posted in T-Sql

Tagged with

Error converting data type numeric to decimal

leave a comment »

Simple one.  So simple, I shouldn’t have spent so much time trying to figure it out. So simple, I should probably quit anything that boasts of ‘information’ and ‘technology’.’  But alas, my pride is more stubborn than this.

I have an application that is calling a procedure and it has a parameter with a decimal datatype.  This error almost always has to do with your parameter setup in the stored procedure.  I had my parameter sitting at decimal(2,2) and I was passing in a single number: 5.  Now, should this work?  You’d think so.  Precision (the number of digits the object can hold) says, ‘two digits is fine with me’ and scale (number of digits to the right of the decimal point) states ‘hey, two digits after the decimal is GOOD!.’  But you are wrong.

For clarity let’s say my declaration stated decimal(3,2).  This would mean only 3 numbers are allowed in the entire number (precision), and only 2 allowed after the decimal (scale).  This would mean that only a single digit would be allowed prior to the decimal.  So for 3,2 the number 1.55 would work.  12.55 would not.  1.556 would not.  Palomino, Texas would not.  Nor would an orange or a tomato or my older brother who still picks on me for that matter.  So 1.55 would work for the declaration of (3,2).  So what does that say about my declaration of (2,2)?  For 2,2 I could only pass in fractions.  Two numbers total are accepted and of those two numbers, two of them have to be to the right of the decimal.  I was attempting to pass in a single number (5). 

I bumped my precision to 5 so that my parameter stored a parameter of decimal (5,2) and everything returned back to normal.

Written by matt

October 25, 2011 at 1:54 pm

Posted in Asp.net, T-Sql