Sunday, May 27, 2007

Scripting T-SQL

Sql Server displays the creation date for db objects, but does not show the last modified date. That makes it hard to tell if and when an object like a view, sp, or UDF was last changed.

For that reason, I always write my script and save it to a disk file, using the following general structure:

DROP PROC dbo.MyProcedure
GO

CREATE PROC dbo.MyProcedure
AS
-- actual code goes here
GO
GRANT EXECUTE ON dbo.MyProcedure TO [MyUserName]
GO

This has two benefits:
1. I can run the script on the dev system to verify it works, and make any needed changes
2. When I run the final script on the production system, the creation date will actually represent the date last modified

IMPORTANT: You cannot use this method for tables, since you will lose all the data therein!

Saturday, May 12, 2007

Create SQL Server database query with MS Excel

1. Start a new XL workbook
2. Click Data ~ Import External Data ~ New Database Query
3. At the "Choose Data Source" dialog, either select an existing DSN or create a new one, then click OK.
4. At the "SQL Server Login" dialog, enter your credentials and click OK
5. At the "Query Wizard" dialog, click Cancel
6. At the "Do you want to continue editing this query in Microsoft Query?" click Yes.
7. At the "Add Tables" dialog, click Close
8. On the MS Query toolbar, click the SQL button
9. Enter your SQL statement in the "SQL" box and click OK
10. If your SQL statement is a stored procedure, it will say "SQL Query can't be represented graphically. Continue anyway?" - click OK
11. You should now see your data displayed in MS Query
12. Click the Return Data button (looks like an exit door) and MS Query will close
13. Now you're back in XL. At the "Import Data" dialog, select the location for the top left corner of where you want the data. It defaults to cell A1 which is usually where you do want it to start; click OK
14. Excel will now retrieve the data and place it starting from the chosen location