SQL Server
Scenario Have some perl code that wants to access Microsoft SQL Server 2005. The SQL Server is configured to only allow SSL connections for security reasons. Solution Here are the parts you will need: Perl on Linux (given) DBI ... http://dbi.perl.org/
Read More...
Scenario You have a normalized table that has values in rows. Let's take an example that has a history table with an approved flag: Date Approved DollarValue Customer 1/1/2008 Y 13.5 A 2/1/2008 N 12 A 3/1/2008 Y 14 A 1/1/2008 Y 11.5 B 2/1/2008 Y 9 B 3/1/2008
Read More...
Scenario Recently I ran into a security issue that has a simple fix. Here is the situation: SSIS Package that contains a Script Task Run SSIS package as a SQL Job Use a special credential/proxy for the step of executing the SSIS package Results In the
Read More...
I was working on a common scenario and found an interesting side effect. Applications typically have a listing page that get search criteria from a UI and passes to a stored procedure. In the procedure you often find statements like: SELECT a , b , c
Read More...
Scenario A table that contains several columns that you need to take and change into rows in order to normalize the data. With SQL Server 2005, a new T-SQL command UNPIVOT can help. An example table: **Table called MyTable FieldID FieldOne FieldTwo FieldThree
Read More...
Working with a friend at work, we were discussing how to create some SQL to go after records in a table and process in a farm of computers (processing cluster). After few minutes of thinking I came up with the following. BEGIN TRAN MyTran UPDATE TOP 1
Read More...
Some things to consider ... Make sure you can recover your server and database prior to issuing these commands. Since I am generically calling a rountine you will receive errors on the system databases. Your database and log files must follow the convention
Read More...
Have you ever run across this error before when trying to delete or disable a SQL server job (or edit one): Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server . This sometimes can happen if you rename a
Read More...
This was a strange occurance I found recently. If you encounter the error SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ you might want to look at the permissions on every file
Read More...
Symptom The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@parameter"): Data type 0xE7 has an invalid data length or metadata length. Solution Installing SP2 my help. http://msdn2.microsoft.com/en-us/sql/bb426877.aspx
Read More...
This particular issue was interesting. I was troubleshooting an issue where an individual had rights to run their SSIS Step in a SQL Job. They could also change variables being passed, add/remove steps in job, almost everything except modify the Data
Read More...
My friend Alfredo helped with some SQL code and I thought it would be interesting to share. I was trying to select the most current record based on a unique id and a date within a single table. I was familar with the SQL 2000 way of doing things (a sub
Read More...
Here is the scenario: SQL Agent account (NETWORK SERVICE or some DOMAIN ACCOUNT) does not have access to database activities in a SSIS package. You want to run a job but need to "proxy" a security account when a SSIS package runs. The following script
Read More...
Here is one way I have found in order to get all tables space used. Effectively it queries the sp_tables and sp_spacedused in a loop to return the data. Declare @Table_Name varchar ( 255 ), @SQL varchar ( max ) ---------------------------------------------------------------------------
Read More...
Periodically, I have seen issues with connecting to a remote SQL Server from SQL Server Management Studio, Asp.Net applications, ODBC connections, etc. Often times this is specific to a machine and not a complete on/off issue with SQL Server security.
Read More...
