http://www.geekzilla.co.uk/Innovation Team's dumping grounden-usTue, 10 Jun 2003 04:00:00 GMTTue, 10 Jun 2003 09:41:01 GMTGeekZilla.co.ukeditor@GeekZilla.co.ukwebmaster@GeekZilla.co.uk http://www.geekzilla.co.uk/view0EBC0EBE-0701-4038-BA9B-15A67A18321B.htm 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. 11/10/2008 http://www.geekzilla.co.uk/view0EBC0EBE-0701-4038-BA9B-15A67A18321B.htm http://www.geekzilla.co.uk/view2872FF14-315E-4C3C-BB73-4950B8241B8E.htm Setting default collation on a column The following will set the collation on a column in a table back to Database Default without dropping and recreating the table (unlike Enterprise Manager) 30/7/2007 http://www.geekzilla.co.uk/view2872FF14-315E-4C3C-BB73-4950B8241B8E.htm http://www.geekzilla.co.uk/view24CF9B2B-4099-4ED7-B360-89B14C11DAE8.htm 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. 16/5/2007 http://www.geekzilla.co.uk/view24CF9B2B-4099-4ED7-B360-89B14C11DAE8.htm http://www.geekzilla.co.uk/viewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm 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... 18/9/2006 http://www.geekzilla.co.uk/viewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm http://www.geekzilla.co.uk/viewF3E9658C-7B7A-472C-BE4A-5C25CD26C0E8.htm 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: 18/9/2006 http://www.geekzilla.co.uk/viewF3E9658C-7B7A-472C-BE4A-5C25CD26C0E8.htm http://www.geekzilla.co.uk/view4384ACEC-8C9D-452A-83F6-230FFE43C17B.htm Changing Schema for a group of objects This script moves all of you stored procedures from one Schema to another. This was really handy when using a Typed Dataset with SQL 2005 and stored procedures. The wizard creates stored procedures in the users Schema (and complains if it does not exist!) There is a default From and To schema so you can just pass null in each time. You could easily change this to apply the change to any type of object, such as tables. 14/9/2006 http://www.geekzilla.co.uk/view4384ACEC-8C9D-452A-83F6-230FFE43C17B.htm http://www.geekzilla.co.uk/view36A44633-8ECA-4587-A1A1-10BC48871DFB.htm 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. 8/9/2006 http://www.geekzilla.co.uk/view36A44633-8ECA-4587-A1A1-10BC48871DFB.htm http://www.geekzilla.co.uk/view0E316ED1-7A99-42FD-B80D-272C15C98027.htm 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# 3/8/2006 http://www.geekzilla.co.uk/view0E316ED1-7A99-42FD-B80D-272C15C98027.htm http://www.geekzilla.co.uk/view8F3CFE18-774B-4ECB-9CBF-A6326EE8A86B.htm Enabling Service Broker The following T-Sql enables or disabled service broker on SqlServer 2005. The Service Broker is required by .net for SqlCacheDependency support Is Service Broker enabled? To determine whether or not Service Broker is enabled for a particular database, execute the following T-SQL '''is_broker_enabled''' will be '''1''' if Service Broker is enabled for the given database, otherwise it'll be '''0'''. 1/8/2006 http://www.geekzilla.co.uk/view8F3CFE18-774B-4ECB-9CBF-A6326EE8A86B.htm http://www.geekzilla.co.uk/viewF2737CEE-4FCE-4215-B8CA-74D77EF64A5F.htm Return new identity from Strongly Typed Dataset DataTable.Insert method Datasets are pretty good at auto generating stored procedures and wrapping c# code around them for you. However with the insert method you often want to do something with the object that you have just inserted. Fortunately there is an easy way to get the Sproc and the c# method to return a reference to the object. Firstly edit your insert stored procedure adding a @return parameter and a line after the insert to set this to a suitable value. The exmaple below assumes that you are using bigint identity fields. Note that SCOPE_IDENTITY() is similar to @@IDENTITY except its scope is limited to the current command and so improves scalability. Now when you save the Dataset you see that the insert method takes two parameters, the second being a nullable long. My first thought was that Datasets should be the end of editing stored procedures but I am still impressed that the c# method declaration changes accordingly. You can call the updated method in the following way. the long? just means nullable long. After filling the table you can now use the FindByxxxID functions of the DataTable to return the DataRow object. 27/7/2006 http://www.geekzilla.co.uk/viewF2737CEE-4FCE-4215-B8CA-74D77EF64A5F.htm http://www.geekzilla.co.uk/view96264E4E-52EE-42BD-9494-2F33947A9063.htm Handling hierachical structures in SQL Dealing with tree structures in SQL can be tricky. A common solution is to have a self referencing table with a primary key and parent key column. This allows for any depth of tree and allows each 'node' to have zero or more child nodes. Below is a stored proc that is a good base for selecting a sub-tree from any sort of single table hierachy. The example comes from a tree of 'targets'. Hence the table contains TargetID as athe primary key and ParentTargetId as the parent id. Note that the node path is the limiting factor as regards to depth -= but even using a varchar(1000) allows for a considerable depth. 22/7/2006 http://www.geekzilla.co.uk/view96264E4E-52EE-42BD-9494-2F33947A9063.htm http://www.geekzilla.co.uk/viewC1D86C71-13E7-477B-94EC-FD01B524B6C3.htm 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 14/7/2006 http://www.geekzilla.co.uk/viewC1D86C71-13E7-477B-94EC-FD01B524B6C3.htm http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm Passing CSV to Stored Procedure There are many occasions when it would be easier to pass an array of values to a Stored Procedure instead of looping. This can be done and is very simple to use once implemented, all it needs is a new UDF (User Defined Function). Then to use this you only need to do the following: 14/7/2006 http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm http://www.geekzilla.co.uk/view98A0F268-80AF-4346-8C5A-CBE3C24EC514.htm SQL Server TABLE Variable When you need a temporary table in SQL Server 2000 (upwards) many people will implement a standard temporary table i.e. There is nothing wrong with this but there is generally a better way, using the T-SQL TABLE variable. There are several benefits to using this and they include:- * They only exist for the scope of the Stored Procedure and therefore do not explicity have to be dropped. * They reduce the locking and logging resource/time when used as part of a Transaction as they are not a physical table that will be affected by any Rollback * They reduce the number of re-compilations required for the Stored Procedure. So the above query can be changed to More details can be found here http://support.microsoft.com/default.aspx?scid=kb;en-us;305977 14/7/2006 http://www.geekzilla.co.uk/view98A0F268-80AF-4346-8C5A-CBE3C24EC514.htm http://www.geekzilla.co.uk/view2356DC3A-9F53-4A95-9849-C4518B8E1F92.htm 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. 16/6/2006 http://www.geekzilla.co.uk/view2356DC3A-9F53-4A95-9849-C4518B8E1F92.htm