Tag: SQL 2005

Categories
Geeky/Programming

Server Move: Hosting My Own Site(s)

Well, I have been on HostMySite for a few years now, and I just wasn’t liking it. I didn’t have control, and couldn’t do everything I wanted to. Last week I signed up with ServerBeach and I have my own dedicated server. I am running Windows 2003, IIS, SQL, MySQL, PHP, etc.

I changed for now my DNS to GoDaddy from Active-Domain, since GoDaddy has SPF records (for Google talk federation, etc). I might change that up here in the future as well.

Transferring a domain takes way to long! Took like 4 days overall. Also, I first went with EasyCGI , a VPS solution, but the box couldn’t get to the Internet! I put in tickets, etc, they just wouldn’t fix it, so I canceled and went to ServerBeach. I am liking them so far, they had the server provisioned in about 4 hours.

One thing I can do now as well, is write a site for myself and import all the log files (Firewall, SMTP, All the Sites, FTP, etc) and have a nice little reporting solution.

ServerBeach is good, very professional. If you want to sign up, use referrer code BW27Q37B6D  (http://www.serverbeach.com/)

To get PHP/MySQL/WordPress, I loosely followed these three tutorials

How to Install PHP on IIS 6.0
How to Install MySQL On IIS 6.0
How to Install WordPress on IIS 6.0

For my ASP.NET Site, it was just copying over files and configuring IIS a little. For my blog, I used this dbbackup plugin (http://www.ilfilosofo.com/blog/wp-db-backup/) and then once I had my WordPress files over, and MySQL/PHP running, I connected to the instance of a DB I created and ran the SQL backup script, which created everything the way I needed it for WordPress.

For Mail, I use the built in SMTP in Windows 2003. I just had to turn on relaying, but yeah, spammers, so I just allowed the local server to send mail, and it works. Tested it using this (http://support.microsoft.com/kb/323350) and it worked.

Other than that, its pretty much basic server admin stuff. Firewall, FTP, etc. Everything is running smooth… for now 🙂

I will just have to make sure I have backups!

 

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Server 2005 Performance Dashboard Reports

Check these out. http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

If you are a DBA, these are pretty much money. I have seen some many custom implementations and DBA’s trying to get reports like this, and if you have SQL Server 2005 SP2, you can download that and run a SQL script and then you have these reports. They give you insight into pretty much everything going on with your server. Why pay $1000’s for a product that will give you the same info when it is built right into SQL Server!!

Technorati Tags: ,,,,
Categories
Geeky/Programming SQLServerPedia Syndication

SQL 2005: Using Data Management Views (DMV's) to View Status of Database and Log Restores

SQL 2005 introduced some cool "data management views" (DMV’s) that let a DBA see what is happening on their database. Previously you had to use undocumented system tables and information schemas to get the info, and a lot of the info wasn’t even available. Well recently I was doing some backup/restore stuff on a database and wanted to know when it would finish. The first question I ask is – why isn’t this built into the SSMS view of a db, when it says "DatabaseName (Restoring)" why couldn’t they just add the % done at the end of something? Anyway’s – if you want to see what is restoring on your server and where it is at, go ahead and use this query:

use master
go

SELECT
    percent_complete AS ‘PctComplete’,
    start_time AS ‘StartTime’,
    command AS ‘Command’,
    b.name AS ‘DatabaseName’,
    DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
    (estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
    FROM sys.dm_exec_requests a
    INNER JOIN sys.databases b ON a.database_id = b.database_id
    WHERE command like ‘%restore%’
    AND estimated_completion_time > 0

I did notice that it says 100% complete but then the restore still takes about 20-30 seconds to finish, so be warned there. Also, if you are restoring a DB, the database name will be master (from what I have seen, it was a fresh restore). If you are just restoring logs, it will show you the database name of the database where the logs are restoring.

Take this and create a quick SSRS (Reporting Services) report. Or even better schedule this to run every 2 minutes or something and maybe once it sees some records, kick off a report, or make a cool little desktop app that has balloon popups and statuses, so as  DBA you can see what is going on with your restores. Maybe I will create that app one Saturday if am bored or something 🙂

Technorati Tags: ,,,,,,,,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005 SSAS Deployment Wizard – Where Did My Roles Go?

Ok.. if you are using SSAS 2005 and using the deployment wizard (not using BIDS) – probably because you want to retain your partitions or something. But anyways, if you use it and choose the option to retain your roles, you will deploy, and no one will be able to get to your SSAS DB or Cubes. What gives?

See this link on Microsoft. Doesn’t make sense to me but I guess in Microsoft speak Retain roles and members really equals Screw up roles and members and leave you scratching your head.

Technorati Tags: ,,,,
Categories
Geeky/Programming SQLServerPedia Syndication

SQL Server 2005 Books Online Scoped Search

Stumbled upon this today

http://search.live.com/macros/sql_server_user_education/booksonline/

Pretty sweet, using search macros, you can search books online..well, ONLINE 🙂

Here is a search for DATEDIFF

http://search.live.com/results.aspx?q=DATEDIFF&go=Search&form=QBJK&q1=macro%3Asql_server_user_education.booksonline

Handy little macro if you don’t have SQL BOL installed. You can always search Google or MSDN to find the info, but this seems “scoped” for just what you need for books online, pretty cool if you ask me..

Technorati tags: , , , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005: OpenRowset, Dynamic MDX and Variable Scope

So, the other day I had to create something in T-SQL that called a MDX query using OpenRowset – this is pretty easy to do, you can query around the openrowset and get the values back you need in a T-SQL Query. This was fine when the MDX query was a static string.

The format of the query would be like this:

SELECT * FROM OpenRowset(‘MSOLAP’, ‘DATA SOURCE=MySSASServer; Initial Catalog=MySSASDB;’,’MyMDXQuery HERE’) as a

Now, the * will give you the columns from your MDX query, in the example above MyMDXQuery would be replaced with your actual MDX query.

The problem comes in, if you want your MDX query (which is a string), to contain some variable, so that you can pass something into the OpenRowset (say a date, or some other variable)…

The problem is, you need to execute the whole query (not just the OpenRowset) as a string, and the scope of variables is lost. You cannot declare a variable outside the TSQL string you want to EXEC, then set it inside the TSQL statement, then use it after. This makes it tough to get data out of the OpenRowset execution. Now if you just are executing the TSQL and getting a result set back for a report or something, it will work without doing what I am doing here, but if you need a scalar value back or something to use in a query later in your proc, then you need to do this. I tried different solutions and this was the only one I could get to work. Like I said, declaring a var before and trying to use in the TSQL exec wont work. Also, a RETURN wont work, it will give you an error saying it doesn’t work in the scope or something similar, here is an example of what does work – using a temp table.

 

DECLARE @TSQL varchar(max)

CREATE TABLE #results
(
  mytempresult DECIMAL(10, 3)
)

SET @TSQL = ‘
DECLARE @myVar AS DECIMAL(10,3)

SELECT @myVar =
SELECT [Measures].[MyMeasure] FROM
OpenRowset(
”MSOLAP”,
”DATA SOURCE=MySSASServer; Initial Catalog=MySSASDB;”,
”WITH
   MEMBER Measures.[MyMeasure]
  AS (‘ + @SomeDynamicString + ‘)
SELECT
{[Measures].[MyMeasure]} ON COLUMNS
FROM [MyCube]
”)
as a

INSERT INTO #results VALUES (@myVar)
‘

EXEC ( @TSQL )

DECLARE @myVarForReal AS DECIMAL(10, 3)
SELECT  @myVarForReal = mytempresult
FROM    #results

DROP TABLE #results

as you can see, I CREATE the temp table outside the TSQL var, then I actually declare a var inside the TSQL statement, set it in my OpenRowset call, which I pass in some other var (@SomeDynamicString) and then insert that value into my temp table.

I then EXEC that TSQL statement, and then grab my variable for real from the temp table, and drop the temp table. You would think that I could just reference @myVar after the EXEC, but it doesn’t exist, and if I declare it outside the TSQL var, it will be empty after, and it won’t get set when I EXEC the TSQL.

Just a “gotcha” if you ever run into executing dynamic MDX from TSQL and you need to get a scalar value back from the MDX.. whew 🙂

Technorati tags: , , , , ,
Categories
Geeky/Programming SQLServerPedia Syndication

Visual Studio 2008 and SQL 2005 Careful…

Since Visual Studio 2008 came out yesterday, I installed it. One gotcha – if you already have SQL 2005 installed, check custom on the install steps, because VS2008 will install SQL Express 2005 and start up an instance of that, I had to then uninstall SQL 2005 Express. PITA 🙂

Technorati tags: , , , ,
Categories
Geeky/Programming SQLServerPedia Syndication

SQL Server 2005 – UNPIVOT

The other day, working on something crazy, I came across a use for the new UNPIVOT keyword in SQL Server 2005. Now, I figured I would use PIVOT before UNPIVOT, but so it goes.

Basically I had a temp table I populated that had some columns, then columns numbered 1-10 with different values. But I needed them to be row based, not column based. I could write some crazy union’s or something, but I figured, it was set up like a pivot table, so why not try UNPIVOT.

SELECT  
    MyId,
    MyName AS ‘Name’,
    tblPivot.Property AS ‘MyProperty’,   
    tblPivot.Value AS ‘MyValue’
INTO #tmp_values
FROM  
  (SELECT MyId, MyName,
CONVERT(sql_variant,[1]) AS [1],
CONVERT(sql_variant,[2]) AS [2],
CONVERT(sql_variant,[3]) AS [3],
CONVERT(sql_variant,[4]) AS [4],
CONVERT(sql_variant,[5]) AS [5],
CONVERT(sql_variant,[6]) AS [6],
CONVERT(sql_variant,[7]) AS [7],
CONVERT(sql_variant,[8]) AS [8],
CONVERT(sql_variant,[9]) AS [9],
CONVERT(sql_variant,[10]) AS [10]
   FROM dbo.MyTable
  UNPIVOT (
    Value For Property In (
    [1],[2],[3],[4],[5],
    [6],[7],[8],[9],[10])
    ) as tblPivot

you can see, you have to make sure all of the fields are the same data type, basically you can get them from being columns to being back to rows with columns Property (would be like 1-10) and then the Value would be the value in the field. Pretty nice and slick!

Technorati tags: , ,
Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Server 2005 – Reporting Services – Windows Vista

I just got my new laptop, and I am installing Visual Studio 2005 and SQL Server 2005. Ran into a snag, that took me a little bit to find the answer.

First, SQL Server 2005 setup is just ghetto. There are two setups, “server” and “tools” and sometimes it just doesn’t work right. Anyways, only SQL Server 2005 with SP2 is supported on Vista. You would think that there would be a slipstream download with the RTM and SP2? Nope. Not that I could find at least.

Other thing I found. By default, you cannot install Reporting Services (SSRS). Reporting Services depends on IIS to run the report web server. Even though I had IIS installed on Vista, it still wasn’t finding it. Searching Google for “SQL Server 2005 Reporting Services Vista” didn’t really give me much luck (easily).

Eventually I found this KB article http://support.microsoft.com/default.aspx/kb/920201

Basically you need to turn more things on in IIS. I turned pretty much everything on but classic ASP and CGI, reinstalled SQL 2005 and no warnings – great! 🙂

So, I go to connect to my instance, and no AdventureWorks DB. I installed everything I thought? WTF? Well, I found this article on reinstalling AdventureWorks sample OLTP and DW databases

http://msdn2.microsoft.com/en-us/library/ms143758.aspx

Because you know, once you install SP2 on SQL Server 2005, you can’t really run the RTM setup without it complaining, and then, it says you already have a newer version installed – bah!

So, in the end, SQL Server 2005 with all the fixings is doable on Vista. Just make sure you have IIS configured correctly!

Technorati tags: , , , , , , , , , , , , ,
Categories
Geeky/Programming

SQL Server 2005 – DDL Triggers – Setting Up Auditing

New with SQL Server 2005, is the ability to set up triggers on DDL statements. This has got to be a DBA’s dream come true. You can set up audit logs on creates/alters, etc. Here is how you do it (this is just a trigger, you will need to create the table, you should be able to get the table definitiion from looking at the trigger)

CREATE TRIGGER [DDL_Auditing] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @raisedEventData XML /* type date-time spid name name name name name name type command */ -- Capture the event data that is created SET @raisedEventData = eventdata() INSERT INTO dbo.AuditDDL (EventTime,EventType,ServerName,CommandText,LoginName,UserName,ObjectType,ObjectName) SELECT @raisedEventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)') AS 'EventTime', @raisedEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') AS 'EventType', @raisedEventData.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)') AS 'ServerName', @raisedEventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)') AS 'CommandText', @raisedEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') AS 'LoginName', @raisedEventData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') AS 'UserName', @raisedEventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') AS 'ObjectType', @raisedEventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') AS 'ObjectName'