Ever been frustrated when working with the way that SQL Server stores run dates and times from jobs scheduled and run using the SQL Server agent in the sysjobs, sysjobhistory, etc tables?  SQL Server stores the date and the time in separate columns in these tables and stores them as data type int, which can be a real pain to deal with if you need to compare them to an actual date.  In SQL Server 2005, there is a little known and sparsely documented function called msdb.dbo.agent_datetime that you can use to create a real date out of these columns.  To use it is pretty simple.  Say you want to see if the run_date and run_time from the sysjobhistory table is greater than yesterday’s date.  The syntax for that would be:
 
select a.name, (msdb.dbo.agent_datetime(b.run_date, b.run_time)) as RunTime
from sysjobs a inner join sysjobhistory b
on a.job_id = b.job_id
where (msdb.dbo.agent_datetime(b.run_date, b.run_time) > getdate()-1)

It is pretty simple to use, but an extremely powerful tool.  Unfortunately, SQL Server 2000 stores the agent dates the same way, but it doesn’t come with this function.  No worries, below is the code should you ever need to create this function on a SQL Server 2000 instance:
CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN

RETURN
(
CONVERT(DATETIME,
CONVERT(NVARCHAR(4),@date / 10000) + N’-’ +
CONVERT(NVARCHAR(2),(@date % 10000)/100) + N’-’ +
CONVERT(NVARCHAR(2),@date % 100) + N’ ‘ +
CONVERT(NVARCHAR(2),@time / 10000) + N’:’ +
CONVERT(NVARCHAR(2),(@time % 10000)/100) + N’:’ +
CONVERT(NVARCHAR(2),@time % 100),
120)
)
END

I have run into issues several times when I have had Remote Desktop Sessions (RDP) open to servers and my computer has had to reboot unexpectedly (either because of WSUS updates or because it has hung) and RDP won’t let me get to my existing session, it just creates a new one when I go to login.  This uses both available administrative RDP sessions on the server (see picture below) and makes it inaccessible to anyone else trying to access it through RDP, as well as keeping me from getting to the work I had in process in the previous session and any windows that I had open.

rdp_img1

So, in the case of this example, I already had an RDP connection established to CORPI at the time my computer rebooted (ID #1).  When my computer came back up, I went to login via RDP to CORPI and it created another session (ID #2).

After much research, I found a way that Windows will let you access an RDP session that you have already established.  It is a command run from the command line on the server, so you would go ahead and login as Session ID #2 and once logged in, do the following:

  1.  Click Start, Run and type in “cmd” to get to a command prompt

 rdp_img2

  1. At the command prompt, type in the following command, tscon 1 /v
    1. The tscon command is the Terminal Services connection command.
    2. The argument, 1, is the Session ID.  This should be changed to whatever Session ID that you want to connect to.  In this case, it was Session ID #1.
    3. The /v parameter tells the tscon command to display information about the commands that are being performed.

Once you hit enter, your current connection to the server will be disconnected (in the case of my example, above, that would be Session ID #2) and you are immediately reconnected to the Session ID# that you specified in the tscon command.

Once you hit enter, your current connection to the server will be disconnected (in the case of my example, above, that would be Session ID #2) and you are immediately reconnected to the Session ID# that you specified in the tscon command.

 

I was also going to add that if you want to RDP to a server and both connections are in use, you connect by typing the server name followed by a space /admin and connect to the console session.

Replication tidbit

17 September 2009

I had set up replication a few times with SQL Server 2000 and there were times that I didn’t want to use a snapshot or a backup – had the schema and data already available and I just wanted to push new data over there.  Setting up the subscription was pretty easy.  In the Initialize Subscription dialog box, I just checked ‘No, the Subscriber already has the schema and data’.  Simple, eh?

initialize_2000

Recently I ran into the same situation – couldn’t use a snapshot or a backup (don’t ask).  The subscription database was already in place and I just wanted to push any new transactions from the publication over there.  When I went through the handy dandy replication wizard, though, I didn’t see the option that I had remembered from SQL Server 2000.  After an initial panic attack, I did some research.  The option is still there, it just looks different.

initialize_2005

All that you need to do now is uncheck the Initialize checkbox in the Initalize Subscription dialog box and away you go.  

Live, learn and replicate.

You can query everything…

16 September 2009
The possibilities are endless

The possibilities are endless

Well, probably not everything, but there are some times that you might want to execute a query against a server other than the one that you’re currently on. Let’s go further and say that you don’t want to use the servername.dbname.schema.table model.

EXECUTE is used to pass commands to linked servers (http://msdn.microsoft.com/en-us/library/ms188332.aspx ). When executing a query, the syntax is:

EXEC(’some random query’) at servername – note that the query within the parenthesis needs to be quoted.

Now here is the part that I found cool (okay, I said it. It’s geeky and yet still cool). The server that you’re querying against can be passed as a variable. For example, I want to know how many databases are on MyServer.

declare @server varchar(30), @cmd varchar(100)
set @server = ‘MyServer’
set @cmd = ‘EXEC (”SELECT count(name) from sys.databases”) AT ‘+@server+
exec(@cmd)

Because I’m easily amused, finding that out pretty much made my day. In my thirst for knowledge, though, I thought I’d dig deeper. Maybe I have a table (server_inv – id identity, name varchar) that holds all of my servers and I want to figure how how many tables I have on each of them AND I want the server names to be returned as well. I’m never satisfied. So here’s how I’d do that:

SET NOCOUNT ON
declare @next_one int,
@this_one int,
@counter int,
@server varchar(20)

SELECT @counter = 1

SELECT @next_one = MIN(id)
FROM server_inv

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one

WHILE @counter = 1
BEGIN
declare @sqlcmd varchar(1000)
set @sqlcmd = ‘EXEC (”SELECT ””’+@server+””’, count(name) from sys.databases”) AT ‘+@server+
exec(@sqlcmd)

SELECT @next_one = NULL

SELECT @next_one = MIN(id)
FROM server_inv
WHERE id > @this_one

IF ISNULL(@next_one,0) = 0
BEGIN
BREAK
END

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one
END
RETURN

Now, I understand that this is a pretty simplistic example, but hopefully it’s an example of what can be done. I’ll admit that I haven’t fully played with this EXECUTE command and there may be limitations to its functionality.

It’s probably far easier to put together a Central Management Server if you’re on SQL Server 2008, but that’s a post for another day.

 | Posted by tledwards | Categories: SQLServerPedia, T-SQL | Tagged: , , |