General

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey'

11/10/2008

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' I was getting the following error when I attempted to create a new job in SQL Server Agent: Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo) Nothing I did improved the situation. It then occured to me that it may not be an issue if I Terminal Severed onto the actual SQL Server. Presto! It worked and my job was created. What this complicated error message meant in my case was... "Log onto the box and do it" I hope you manage to create your job.

Backup ALL your SQL Server 2005 databases using ONE script

20/11/2007

Backup ALL your SQL Server 2005 databases using ONE script I wanted to backup all my databases... I had loads, creating a step for each db was getting tedious, so I wrote this script. Enjoy

XMLHTTPRequest in Stored Procedure

16/5/2007

XMLHTTPRequest in Stored Procedure I recently had a requirement to call a webservice from a stored procedure. I looked into doing this by writing a CLR assembly in SQL 2005. There were a few articles online describing this but working in a large organisation I knew that getting the thing working on my development was only 20% of the battle. The XML serialisation of the web service request/response cannot be done natively, a separate assembly needs to written to achieve this, using a tool such as SGEN.exe. Basic objects such as HTTPRequest are not available in the SQL CLR so you can't just avoid the serialisation. Even once I had all that working, the idea of our database team deploying assemblies to their databases just seemed like a dream. So I looked for a simpler method and found that I could create an XMLHTTPRequest within a stored procedure much like I would in client-side code for an ajax solution. The stored procedure is below... You also need to enable Ole Automation for this database using... So basically your T_SQL can now make http requests, but not web service requests. The page I am requesting will be written in C# and will make the web service request itself, in an environment that is not so restricted as the SQL CLR. The added benefit is that this does not require 2005 to work, it should work on any SQL Server database v7 onwards Please beware: Your application login will need access to run the sp_OAxxx extended procedures, so you will need to ensure that this isn't something that just anyone can login as. I have read that teh user needs to in the sysadmin role - I have found that just GRANTing EXECUTE permissions for your user on each xp is enough. Also, do not make http calls from within a trigger, this can potentially cause really bad performance, especially where the web server is not reachable or responding for whatever reason.

T-SQL script automatically adds auditing to a table

18/9/2006

T-SQL script automatically adds auditing to a table The script below looks at the structure of the table you specify and creates a new table called xxx_Audit with the same structure plus a timestamp, less any constraints/unique keys etc. It then creates a new trigger on the original table to populate the xxx_Audit table. It doesn't cope with DELETE statements, but then I don't normally allow application users to actually delete data (I have a deleted 'bit' column and whatever purge criteria is appropriate to the application). You could extend the TRIGGER to handle deletes also without much difficulty. The SQL file is attached, feel free to use it...

Select Column Information using SQL Server

18/9/2006

Select Column Information using SQL Server Use this T-SQL to select infomation about a specific table/columns, Useful for automated scripts. By the way, I worked this out using SQL Profiler. I switched on a trace and then clicked on a table to show columns. The trace then showed some SQL similar to this (more complex) and I just rendered down what I needed. The point I am making is that SQL Profiler turned out to be a handy resource on the sys schema. Then again it has been pointed out to me that the following is also valid:

Bug in VS2005? Using Typed DataSets with SQL 2005 and stored procedures

14/9/2006

Bug in VS2005? Using Typed DataSets with SQL 2005 and stored procedures I have been a fan of typed datasets since I started using them in VS2005. Using the feature with SQL Express/stored procedures, Express/SQL Statements, SQL Server/SQL Statements all generate my DAL just fine. However, I have found a couple of problems with them when using them with full SQL Server 2005 and stored procedures. '''Problem 1:''' The first problem is basically that the wizard generates stored procedures in the Schema of the user in the connection string. Regardless of whether SQL user or Windows Authentication are used. You probably wil not have created this schema in the database and so the wizard works fine until the finish step where you see the following message... You might change the default Schema to dbo for the user, the wizard ignores this and performs 'CREATE PROCEDURE [user].[spName]...', very annoying. So, you create a schema in your database with the name of the user (and the user access to it) and this problem is resolved. '''Problem 2:''' The next problem you will see is the message: The reason for this is that the stored procedures are created in the user schema whilst the GetData/Fill/insert/update/delete methods still use the dbo Schema. I couldn't find a way to stop this other than changing the properties of the table adapter manually after configuring. However you may note that GenerateDBDirect methods is still set to true in the table adapter properties and the methods are infact generated. This results in ... '''Problem 3:''' When you run your code you see a run time error "Could not find stored procedure 'dbo.yourProcName' " What I ended up doing was to run a script (http://www.geekzilla.co.uk/View4384ACEC-8C9D-452A-83F6-230FFE43C17B.htm) to move my user.spXXX to dbo.spXXX , not ideal but the Dataset works fine after this. '''All sorted...''' Once you aware of what the wizard is doing - creating in user schema and referring to dbo schema - it becomes quite easy to cope with (if not agree with). For a while its behaviour seemed quite random, especially as I had some existing procs in the dbo schema and so some tables worked fine as they created new procs in the user schema and pointed at existing ones in the dbo schema. My final thought is - I get the impression Typed Datasets wasn't tested with SQL Server and stored procedures. But this won't stop me from using them. I hope this is fixed in a service pack soon.

Enabling Agent XPs on a new SQL Server 2005 install

8/9/2006

Enabling Agent XPs on a new SQL Server 2005 install I had to do this prior to creating a maintainence plan for a database on my development box. When i first tried to create the plan I was given a message that Agent XPs are not enabled and that I should run sp_reconfigure. Run this statement then you can do get on with it.

Exporting large amounts of Data from SQL

31/8/2006

Exporting Large amounts of Data from SQL If you have ever had an issue transfering large amount of data from SQL you can use the command line tool to solve this problem. From the command line ||''''''|| is an optional parameter specifying the name of the database. It can only be omitted if the desired database is the default database. || ||''''''|| the name of the table owner. || ||''''''|| the name of the table. || ||''''''|| The name of the comma separated file to generate. || ||''''''|| The SQL Server username to use. This can be left blank if NT authentication is used. || ||''''''|| The password to use for the account. || ||''''''|| The name of the server on which the database resides. This can be omitted if the database is on the local machine (i.e. the same machine. as the bcp command is issued from). || For example This is less bulking and a lot faster than using the built in function within Enterprise Manager.

Full Text Search in SQL 2005

3/8/2006

Full Text Search in SQL 2005 Full Text Search was installed when SQL Server was installed and a service was running, so I expected it to just be sitting there waiting. This is unlike SQL 2000, where I lost count of how many times I had to install it on different servers. Was I wrong in expecting it to just work? Absolutely! (WRONG!!) It seems that there is a fault in the installer that defaults the service to run under the LocalSystem account. Fix here :- http://support.microsoft.com/?kbid=907256 Having fixed this, I went on to use Full Text Search and still no luck. The option was disabled in the management console. ''UNTIL'' I issued the commands "At this point, the Full Text Search was available in the Management Console" From here in TSQL :- #s#

Extending Strongly Typed Datasets

23/7/2006

Extending Strongly Typed Datasets Strongly Typed Datasets are a rather nice addition to VS2005 (they don't lend themselves well to acronyms however) You still need to create a database and tables and realationships but using STDs saves a writing huge amount of code. If this code were generated by hand or by a utility such as CodeSmith then the classes could just be edited to add function as required (though most of the benefits of code generation are lost once the generated code is edited). The STD source is of course auto-generated from the xsd file, a convienient way to view the C# source is to type the STD name and use the context menu to 'Go To Definition', or press F12. This will open a c# source file with a guid name. You can edit this file but it will be overwritten each time the xsd is compiled. You can add new methods and properties to the STD classes by using partial classes. First create a new cs (or vb if you swing that way) file with the filename same as your xsd - for example 'ObjectSTD.cs'. Edit the class definition adding the partial keyword as below. You can add a new method to the ObjectRow class within the ObjectSTD class with the following code... Note that private properties and methods are available and intellisense supports these.

Change Database Owner Following Restore

19/7/2006

Change Database Owner Following Restore I upgraded a SQL 2000 database to SQL 2005 by backing up and restoring. However when this was done, the database does not have a valid owner and you will not be able to add diagrams etc. To resolve this you will need to run the following SQL #s#go

Intellisense For SQL Server Query Analyser

18/7/2006

Intellisense For SQL Server Query Analyser Query Analyser has always been missing something and that something was [intellisense]. Just think about the time this would save you not having to have to look up the column names all the time or how to use a T-SQL function you have not used for some time. Well now you can have it all (for free until September) Sql Prompt by Red-Gate it provides the following: * Intellisense for SQL Server, plus other features * Code completion for fast, accurate script building * Discoverability in SQL Server query creation * Keyword formatting, code smippet integration and other extended featuers Just go get it http://www.red-gate.com/products/SQL_Prompt/index.htm

Change the Owner for Stored Procedures

14/7/2006

Change the Owner for Stored Procedures This one comes from Tim Page, if you ever need to change the owner of several Store Procedures at the same time then this is the T-SQL for you. And this even supports SQL Sever 2005. #s#DEALLOCATE CURS

Strongly Typed DataSets and GridViews

12/7/2006

Strongly Typed DataSets and GridViews If you are anything like me you don't enjoy writing 5 or 6 stored procedures for each business object in your solution. If you do, then click here to read more about T-SQL! http://www.geekzilla.co.uk/Browse.aspx?CategoryID=32 For the rest of you I will add some notes about my recent experience with DataSets. Within VS2005 (or the express versions) you can drag a database table onto a DataSet object (an .xsd file generate using the 'Add Item' menu). This generates a DataTable and DataTableAdapter with the four standard SQL statements (Select, Insert, Update, Delete). You can then easily link a GridView to this TableAdapter via an ObjectDataSource. You then run the page it all looks fine, you click the Edit button and make some changes and then click Update. '''ObjectDataSource 'ObjectDataSource3' could not find a non-generic method 'Update' that has parameters: TestString, original_TestId, Original_TestString.''' Then you become less impressed, you have also clicked the Delete button first and noticed that it just didn't work. At this point you might look at the SQL that is automatically generated.... Hmm, I would have been happy with: If you right-click and configure the tableadapter (from within the xsd file) accepting defaults all the way through then some SQL rather closer to my expectations is generated. You save the DataSet file and run again but still get the same error. However, if you search for this error on google (thanks Barry) you will find that setting the Gridview column for the primary key to be '''readOnly=false''' this resolves the error. At this point we have a TableAdapter working with a GridView. Its a shame that I couldn't leave out the steps above - but then I guess there would be no point to this article :-) To summarise, the TableAdapters work well but are let down by the default SQL generation. For all the discussion about SQL above I didn't have to hand craft any SQL statements to get this to work. But I did have to run through the configure wizard to generate useful SQL. The ReadOnly parameter is a default of the GridView, but still you would expect the two to work together better than they do.

GRANT Permissions on all of your Stored Procedures using a query

16/6/2006

GRANT Permissions on all of your Stored Procedures using a query Permissions are an essential security feature, and you'll often create Roles and Users when you copy your Database live. Rather than using the permissions dialog boxes in Enterprise Manager you can write some T-SQL to perform the GRANTS in Query Analyzer. This is relatively simple process. Use dbo.sysobjects to get a list of your Stored Procedures In the WHERE clause setting type to 'P' will only return the Stored Procedures from dbo.sysobjects. Of these records those with a category value of 0 are user stored procedures, you'll notice that I have added this to the WHERE clause. Don't be tempted to use a CURSOR to get to all your stored procs CURSORs utilise loads of resource, they're slow, and this only increases as table size increases. Try and use a simple loop to iterate rows in a table, I normally drive this using a MIN() on Unique field, selecting the next lowest value in the table, this is much faster than a cursor. See below. Once you've got your loop working, you'll want to grab the details of the stored procedure and perform the GRANT. On each iteration of the loop you can build a SQL string then execute it using '''xp_execresultset''' And that's it. Loop round the dbo.sysobjects table, get each stored procedure in turn, build a SQL string and execute it. Using a standard naming convention for your stored procs will allow you to extend this further by picking up specific groups of procedures in the initial query so you can GRANT functionality to the correct user or role.

Problem With SQL Server 2005 on Vista Beta2

15/6/2006

Problem With SQL Server 2005 on Vista Beta2 So you've been through the throws of building a Vista Beta 2 installation and boy it looks good. Maybe a couple of missing drivers to hunt down but otherwise all that Vista goodness. So now you want to install some developer tools and start to play with those new foundation services. The good news is that Microsoft have done a really good job and Visual Studio 2005 Standard and Team System will both install pretty clean. The only problem comes when installing SQL Server 2005 (any version). The solution is to install SQL Server 2005 Service Pack 1 which you can get from those helpfull people here: http://www.microsoft.com/sql/sp1.mspx.

Fixing broken users after Database Restore

14/6/2006

Fixing broken users after Database Restore Ever restored a Database which was created on a different server then not been able to use the same logon you had previously set up? This command fixes the problem with the account. #s#sp_change_users_login 'Auto_Fix','username'