How to view sql servers on my network
There are lots of ways to skin a network. Here are a few of them. The first three are in order of preference. After that it’s a crapshoot. But I give you a hint on which one you should use below.
isql –L (uses DB-Library and acts like a 6.5 client and will expire)
osql –L (uses odbc and will expire)
sqlcmd –L ******* (this will give you the instances and fully supports 2005. I also found a nice post about this utility here)
sqlPing (I know nothing about this approach)
SQL-DMO (for 2000)
SQL-DMO object reference can be found in the binn directory of your sql server installation.
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application
Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
List1.AddItem oNames.Item(i)
Next i
I’m not going to tell you the best one to use, but I’ll give you a hint. Use the one with the stars behind it.
An aside, if you are running within sql server and not the command line you might need to turn on xp_mdshell. But don’t do it on my watch. The security implications are nasty:
–Turn on Advanced features
EXEC sp_configure ’show advanced options’, 1
reconfigure
go
–Turn on xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
reconfigure
go–example: EXEC master..xp_CMDShell ‘OSQL -L’
Default value on a column
What is the default value on my column? (Take out the criterion for a broader result set)
FROM col.name as col, defaultvalue = c.text
FROM syscomments c
JOIN
(SELECT name, cdefault
FROM syscolumns
WHERE id = object_id(‘MYTABLE’)
and name = ‘MYCOLUMN’
AND cdefault > 0
) col on c.id = col.cdefault
How do you modify the default value on my column? I didn’t know, but it is actually a constraint. So it is a drop and a re-create for a constraint.
ALTER TABLE [dbo].[MYTABLE] drop CONSTRAINT [MYCONSTRAINT]
ALTER TABLE [dbo].[MYTABLE] ADD CONSTRAINT [CONSTRAINT] DEFAULT (‘MYDEFAULTVALUE’) FOR [MYCOLUMN]‘
I would rather be in-sensitive
Somehow I missed the fine print and downloaded the AdventureWorks Case Sensitive Database. Specifically, my collation is set to Latin1_General_CS_AS. The CS apparently stands for Case Sensitive. (To see the collation of your database right click, select properties, and go to the general tab…it’s at the bottom). Unfortunately, being AdventurWorks I have put up with it…until now. I’m tired of re-writing queries in the RiGht CaSe.
According to this article you can run this command (in single user mode)
ALTER DATABASE AdventureWorks COLLATE Latin1_General_CI_AS
But Sql server will bleed all over your screen with all kinds of dependency errors. Unfortunately collation can be set at the database, column, and table level. So to fix it you would have to change the collation at every level.
Or you can just download a new one from here. Make sure you pick one that has CI at the end of it.
FIXED!
Breaking up is hard to do
There are a lot of great posts about splitting a table into a list or splitting a list into a table. Do a search and you will be a little overwhelmed by the good and the bad advice. Let me turn you on to a couple of excellent articles. Anith wrote an excellent must read article titled Faking Arrays where he explores the various sundry ways to do this. Itzik Ben-Gan (my performance hero) has spent a lot of time determining the most efficient way to split data in a whitepaper titled Arrays and Lists in Sql Server 2005 (it still applies in 2008).
After gleaning over these articles and countless others I’ve surmised the following four methods.
1.
The first is splitting your table to a character list. It’s pretty simple, really. Just declare your variable and set it to an empty string. Setting it to an empty string is required unless you want to mess with the ‘SET CONCAT_NULL_YIELDS_NULL’ setting which I don’t recommend…some settings actually modify the database and cause your stats to restart…not sure about all this particular setting though and I’m not really up on this little fact. Why is it required? Well, you can’t add a string to a null value, can you. 1 + null just doesn’t make any sense and will always return you a null (unless you mess with the setting above of course. Anyway, this is how you do it.
declare @d varchar(100)=”
select @d = @d + ‘ your delimeter ‘ + field from yourtable
print @d
2.
The second method is splitting your table into a character list utilizing the new 2005 ‘for xml’ command. From what I understand this performs better than the first, but it’s not as readable:
select yourfield= STUFF(
(SELECT ‘ ‘ + yourfield AS [text()]
from yourtable
for xml PATH(”)),1,1,’,')
3.
The third method we broach the discussion of splitting a list into a table. The most efficient method of doing this is using a custom clr. Text-parsing isn’t sql server’s forte, but it is for iterative-based languages like vb, c#, etc. But this isn’t the way I do it because it requires modifying the database to allow clr operations and this means getting permission and jumping through hoops, etc. So the next best method is using a numbers table with a table-valued function that joins to this table. This is Itzik-tested because it comes from Itzik. As far as creating a creating a numbers table, don’t waste your time searching, just use this:
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL
)
declare @fromNumber int =0, @toNumber int=100,
@byStep int=1;
WITH CTE_NumbersTable AS (
SELECT @fromNumber AS i
UNION ALL
SELECT i + @byStep
FROM CTE_NumbersTable
WHERE
(i + @byStep) <= @toNumber
)
insert into numbers SELECT * FROM CTE_NumbersTable
Now that this is done here is the function that can be used to split a list into a table (which utilizes the numbers table):
CREATE FUNCTION [dbo].[split_to_table](@array AS VARCHAR(1000), @delimiter as varchar(6)) RETURNS TABLE
ASRETURN
SELECT
ltrim(rtrim(SUBSTRING(@array, number,
CHARINDEX(@delimiter, @array + @delimiter, number) – number))) AS element
FROM dbo.Numbers
WHERE number <= LEN(@array) AND SUBSTRING(@delimiter + @array, number, 1) = @delimiter
4.
Method number 4 applies when you don’t have access to create a numbers table. Lots of ways to do this and this is one of them. This was put together with the first article I mentioned by Anith. I couldn’t get the max recursion option to work because I was in sql 2000 but I left it in.
CREATE FUNCTION [dbo].[Split]
(
@p VARCHAR(500), @delimeter varchar(3)
)
–select * from split(”,’|')
RETURNS @tblSplit TABLE (value varchar(100))
AS
BEGIN
IF ISNULL(@P,”) <> ”
BEGIN
WITH CTE ( pos, pos_begin, pos_end ) AS (
SELECT 0, 1, CHARINDEX( @delimeter, @p + ‘,’ )
UNION ALL
SELECT pos + 1, pos_end + 1, CHARINDEX( @delimeter, @p + @delimeter, pos_end + 1 )
FROM CTE
WHERE CHARINDEX( @delimeter, @p + @delimeter, pos_end + 1 ) > 0 )
insert @tblSplit SELECT SUBSTRING( @p, pos_begin , pos_end – pos_begin ) AS "value"
FROM CTE
–OPTION ( MAXRECURSION 0 ) ;
END
RETURN
END
Lots of ways to do this….these are my top four. If you have a better way let me know.
Server Objects…Owned!
I wanted to find out what objects I owned before I deleted myself as a user. Lucky for me I found RBarry Young who showed me that I didn’t own anything. He showed me with a post to SqlServerCentral.com. Thank you Mr.Young! Excellent stuff!!!!
Is there an Identity seed on this table?
A couple of things I’ve discovered lately while working with the identity property of a column.
1. How to check to see if an identity seed exists on a table and what the column is:
SELECT object_name(id) as table_name, name
FROM syscolumns
WHERE columnproperty(id, name, ‘IsIdentity’) = 1
and object_name(id) = ‘mytable’ –table name (leave this line off just to show them all)
2. How to check to see what the identity values are for that column (what is the base seed and what is the increment value):
select table_name, ident_seed(table_name) as seed, ident_incr(table_name) as incrment
from information_schema.tables where ident_seed(table_name) IS NOT NULL and table_name = ‘mytable’
3. You can only have one identity column on a table.
4. You can reseed it at any time with any new seed.
DBCC CHECKIDENT (tablename, reseed, 0)
5. You can set your own number on an insert with Identity_Insert turned on (without the mess in #6).
6. You can just ‘turn off’ and ‘turn on’ that property in SSMS. Easy. Pie. Simple. Even FUN! But what goes on behind the scene? A lot of ugliness. Don’t do it unless you really need to. It creates a new table. inserts into your new table. drops your original, renames, re-creates indexes, etc. Basically, it just throws up all over your server and data:
CREATE TABLE dbo.Tmp_seedtest
(
…
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_seedtest SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest ON
GO
IF EXISTS(SELECT * FROM dbo.seedtest)
EXEC(‘INSERT INTO dbo.Tmp_seedtest (year, id, projectname)
SELECT year, id, projectname FROM dbo.seedtest WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_seedtest OFF
GO
DROP TABLE dbo.seedtest
GO
EXECUTE sp_rename N’dbo.Tmp_seedtest’, N’seedtest’, ‘OBJECT’
GO
CREATE UNIQUE CLUSTERED INDEX ix_ucl_yrid ON dbo.seedtest
(
…
)
Padding your numbers…just a little
I know padding your numbers in this economic crisis is a touchy issue. But let’s put our morals to the side for a moment and discuss how to do it. OK. Actually by ‘padding’ I’m really just meaning adding something extra flair to your number. Example
boring example: changing your number from 36 to 00036
exciting example: changing your number from 36 to WOW_LOOK_I_AM_36
Formatting in t-sql is limited. It doesn’t really care about how you want your data to look. What you wear to work isn’t the datacenter’s job. That’s your mother’s job.
The new function I discovered is STR. Not sure why I never used it before. The second argument for STR is how many characters you want your number to be…and it fills it with spaces. So STR(3,2) gives us the number 3 represented as a 2 character string like so: ‘ 3’. So throw in a replace (change spaces to 0s) and you have your pad. I placed mine in a computed column using an id column:
replace(str(id,3),’ ‘,0)
if you want to copy and paste the code below you can test it out
declare @myage int
set @myage = 36 –enter your age
select replace(str(@myage,3),’ ‘,’WOW_LOOK_I_AM_’)
Access data within a linked server within a stored procedure within a job on Tuesdays from space
Setting up a linked server and accessing that server within a certain security context is easy. Just add the server to the linked server collection (under server objects) and set your user/pass in the security section of the friendly GUI. (Here’s the t-sql version:)
sp_addlinkedserver [ @server = ] ’server’
[ , [ @srvproduct = ] ‘product_name’ ]
[ , [ @provider = ] ‘provider_name’ ]
[ , [ @datasrc = ] ‘data_source’ ]
[ , [ @location = ] ‘location’ ]
[ , [ @provstr = ] ‘provider_string’ ]
[ , [ @catalog = ] ‘catalog’ ]
For the sake of our discussion I set the security on this linked server to MY_REMOTEUSER_NAME.
That works. I can now execute select top 188 * from SERVERNAME.DATABASE.DBO.TABLE all day long (full qualification of server is necessary). I can even run it from within a stored procedure. The problem I had was when I wanted to run the procedure from a job. When I did I received the error:
The job failed. Unable to determine if the owner (DOMAIN\MCLINGAN) of job JOBNAME has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\MCLINGAN’, error code 0×5. [SQLSTATE 42000] (Error 15404)).
This is obviously a security problem. I tried Execute AS in my stored proc…..didn’t work. Recognizing that jobs run under the server agent login I tried adding it to my linked server, but my linked server security already stated that for a login not defined in the list connections will be made using MY_REMOTEUSER_NAME. Adding any of the mssql defined logins to my linked server proved pointless.
The following also didn’t work. It seemed like a reasonable solution to me…enabling you to run a query specifying a linked server
select * from OPENQUERY(LINKEDSERVERNAME, ’select top 188 * SERVERNAME.DATABASE.DBO.TABLE’ )
The solution seemed strange to me:
DID YOU KNOW that the owner of the package was more than just informational? it has direct security implications to the steps therein. My solution:
1. Add MY_REMOTEUSER_NAME to the database server and the database with the correct permissions of course.
2. Modify the job owner to MY_REMOTEUSER_NAME that I added in step one.
BONUS: When you use a linked server and operate under your database to connect to it you can use all the bells and whistles of sql serer 2008 on a 2000 database. I have a linked server to a 2000 box and I can use my 2008 instance to query it with merge, rank, etc.
Middle Click to end your day
If you use your middle click button to close tabs, don’t do it in ssms for the tab you aren’t on. It will close the tab you are on.
Explanation: I use the middle click to close tabs. It works in ie, firefox, chrome, ssms, visual studio, and coworkers. Anytime you want to end a conversation or a window just middle-click with the mouse. It quickly solves all your juggling-in-memory-tired-of-talking-to-this-person tasks. It works brilliantly on active windows that you are currently conversing in…. as well as the in-active, behind the scene tabs. (the ones that are still slowing down your performance but you might need in two days but you don’t want to add to your ever expanding favorites that you never look at)
The exception to this middle click wonderfulness? SQL Server Management Studio.
Follow me: Open a new query window and write something like ‘leave me.close me’. Now open another one and write ‘don’t leave me. leave me open.’ And leave that one up staring back at you. With your mouse middle clicker button wheelie thing punch it on the inactive window (the one you can’t see that says ‘leave me.close me’). What happens? What is looking back at you? The one that says ‘leave me.close me.’ See? It closes the one you are on.
It only takes one time to learn this silly little exception…if this happens on a query that you have put your soul into. Yes. You are one middle wheelie click thingie away from having a BAD DAY.
(this applies to ssms 2005 and 2008)
