Thursday, September 27, 2007

Screencast: SQL Server Analysis Services what's in it for me?

SQL Server Analysis Services is a very powerful tool, which was introduced with SQL Server 2000. In SQL Server 2005 it is far more powerful, but still underappreciated by a lot of developers. This is a quick and dirty attempt to convey the value of Analysis Services cubes in under ten minutes. Related Resources:

SQL Server Analysis Services Home
Microsoft Business Intelligence Home
SQL Server Analysis Services on MSDN

Watch the screencast(WMV)

Friday, September 21, 2007

SQL Teaser PASS Special: Table Size

What will be the outcome of this script?
First we create a table with a total of 6000 bytes
Next we increase col2 from 1000 to 2000 bytes, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes


First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO

--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO

Now what do you think will happen when you run this?


--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO

Now for bonus points. What book have I been reading.

Hint: the author is at PASS

Wednesday, September 19, 2007

SQL Injection Cheat Sheet

What is SQL Injection? From wikipedia: SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed

Here is a nice SQL injection cheat sheet. Currently only for MySQL and Microsoft SQL Server, some ORACLE and some PostgreSQL

http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/

Table Of Contents
About SQL Injection Cheat Sheet
Syntax Reference, Sample Attacks and Dirty SQL Injection Tricks

Line Comments
SQL Injection Attack Samples

Inline Comments
Classical Inline Comment SQL Injection Attack Samples
MySQL Version Detection Sample Attacks

Stacking Queries
Language / Database Stacked Query Support Table
About MySQL and PHP
Stacked SQL Injection Attack Samples

If Statements
MySQL If Statement
SQL Server If Statement
If Statement SQL Injection Attack Samples

Using Integers

String Operations
String Concatenation

Strings without Quotes
Hex based SQL Injection Samples

String Modification & Related

Union Injections
UNION – Fixing Language Issues

Bypassing Login Screens

Enabling xp_cmdshell in SQL Server 2005
Other parts are not so well formatted but check out by yourself, drafts, notes and stuff, scroll down and see.

Tuesday, September 11, 2007

Cumulative update package 3 for SQL Server 2005 Service Pack 2 is available

Cumulative update package 3 for SQL Server 2005 Service Pack 2 is available.

How to obtain Cumulative Update 3 for SQL Server 2005 Service Pack 2
A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package.

To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the cumulative update package. To submit an online request to obtain the cumulative update package, visit the following Microsoft Web site:
http://go.microsoft.com/?linkid=6294451

Here is what is fixed:
50001581
938243 (http://support.microsoft.com/kb/938243/)
FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: “The execution of a full-text query failed. The content index is corrupt."

50001585
938712 (http://support.microsoft.com/kb/938712/)
FIX: Some records in the fact table may not be processed when you process a dimension that contains many attributes or many members in SQL Server 2005


50001499
938962 (http://support.microsoft.com/kb/938962/)
FIX: You may receive an access violation or error 942 when you drop a database in SQL Server 2005


50001524
939563 (http://support.microsoft.com/kb/939563/)
FIX: Error message when you synchronize a merge replication in Microsoft SQL Server 2005: "MSmerge_del_, Line 42 String or binary data would be truncated"

50001582
939942 (http://support.microsoft.com/kb/939942/)
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode

50001583
940128 (http://support.microsoft.com/kb/940128/)
FIX: You receive error 8623 when you run a complex query in SQL Server 2005


50001586
940129 (http://support.microsoft.com/kb/940129/)
FIX: An MDX query does not return results when you execute the query against a cube that contains an unnatural hierarchy in a dimension in SQL Server 2005 Analysis Services


50001517
940160 (http://support.microsoft.com/kb/940160/)
FIX: Error message when you deploy a SQL Server 2005 Analysis Service project that contains many cubes, and the cubes contain linked measure groups: "Unexpected internal error"


50001449
940210 (http://support.microsoft.com/kb/940210/)
FIX: Error message when you try to insert more than 3 megabytes of data into a distributed partitioned view in SQL Server 2005: "A system assertion check has failed"


50001447
940220 (http://support.microsoft.com/kb/940220/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains an FTP task: “An error occurred in the requested FTP operation"


50001448
940221 (http://support.microsoft.com/kb/940221/)
FIX: Error message when you try to create an Oracle publication by using the New Publication Wizard in Microsoft SQL Server 2005 Service Pack 2: “OLE DB Provider "OraOLEDB.ORACLE" for Linked server returned message "ORA-02074: Cannot RO


50001451
940223 (http://support.microsoft.com/kb/940223/)
FIX: Error message when you synchronize a subscription by using Windows Synchronization Manager in SQL Server 2005: “The merge process failed to get correct information about the Interactive Resolver component from the Registry"


50001416
940260 (http://support.microsoft.com/kb/940260/)
FIX: Error message when you use Service Broker in SQL Server 2005: "An error occurred while receiving data: '64(The specified network name is no longer available.)'"


50001435
940269 (http://support.microsoft.com/kb/940269/)
FIX: Error message when you try to edit a SQL Server Agent job or a maintenance plan by using SQL Server Management Studio in SQL Server 2005: "String or binary data would be truncated"


50001567
940281 (http://support.microsoft.com/kb/940281/)
FIX: An access violation may occur, and you may receive an error message, when you query the sys.dm_exe_sessions dynamic management view in SQL Server 2005


50001351
940370 (http://support.microsoft.com/kb/940370/)
FIX: The "User:" prefix is lost for the event information that is generated by a child package in SQL Server 2005 Integration Services after you install SQL Server 2005 Service Pack 2


50001382
940373 (http://support.microsoft.com/kb/940373/)
FIX: The performance of a Multidimensional Expressions query in SQL Server 2005 Analysis Services Service Pack 2 is much slower than the performance in earlier builds of SQL Server 2005 Analysis Services


50001433
940378 (http://support.microsoft.com/kb/940378/)
Fix: Unable to Change Transaction Isolation Level After Cursor Declaration


50001479
940384 (http://support.microsoft.com/kb/940384/)
FIX: You receive a System.InvalidCastException exception when you run an application that calls the Server.JobServer.Jobs.Contains method on a computer that has SQL Server 2005 Service Pack 2 installed


50001494
940386 (http://support.microsoft.com/kb/940386/)
FIX: You cannot install SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies on a beta version of Windows Server 2008-based computer


50001602
940545 (http://support.microsoft.com/kb/940545/)
FIX: The performance of insert operations against a table that contains an identity column may be slow in SQL Server 2005


50001589
940935 (http://support.microsoft.com/kb/940935/)
FIX: Error message when you run a parallel query in SQL Server 2005 on a computer that has multiple processors: “SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'"


50001599
940937 (http://support.microsoft.com/kb/940937/)
FIX: Error message when you try to update the index key columns of a non-unique clustered index in SQL Server 2005: " Cannot insert duplicate key row in object 'ObjectName' with unique index 'IndexName'"


50001609
940939 (http://support.microsoft.com/kb/940939/)
FIX: Data is not rolled back after you roll back a transaction that contains a writeback operation in SQL Server 2005 Analysis Services


50001629
940942 (http://support.microsoft.com/kb/940942/)
FIX: Error message when you run a stored procedure that references a table that is upgraded from SQL Server 2000 to SQL Server 2005: “A time-out occurred while waiting for buffer latch"


50001573
940949 (http://support.microsoft.com/kb/940949/)
FIX: You receive an error message when you run an UPDATE CUBE statement to update a cube in SQL Server 2005 Analysis Services


50001576
940962 (http://support.microsoft.com/kb/940962/)
FIX: When processing a dimension on SQL Server 2005 Analysis Services an error is raised with the following format: "The following file is corrupted: Physical file: \\?\\MSMDBuildLevelStores_avl_672_29775_njzs2.tmp. Logical file ."

50001511
940126 (http://support.microsoft.com/kb/940126/)
FIX: Error 9003 is logged in the SQL Server error log file when you use log shipping in SQL Server 2005


50001436
940379 (http://support.microsoft.com/kb/940379/)
FIX: Error message when you use the UNLOAD and REWIND options to back up a database to a tape device in SQL Server 2005: "Operation on device '' exceeded retry count"

50001412
940375 (http://support.microsoft.com/kb/940375/)
FIX: Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005


50001522
939562 (http://support.microsoft.com/kb/939562/)
FIX: Error message when you run a query that fires an INSTEAD OF trigger in SQL Server 2005 Service Pack 2: "Internal Query Processor Error The query processor could not produce a query plan"


50001224
937100 (http://support.microsoft.com/kb/937100/)
FIX: Error message when you run a SQL Server 2005 Integration Services package that contains a Script Component transformation: "Insufficient memory to continue the execution of the program"


50001415
940377 (http://support.microsoft.com/kb/940377/)
FIX: Error message when you process cubes for one of the named instances of SQL Server 2005 Analysis Services: "Error opening file"


50001523
938363 (http://support.microsoft.com/kb/938363/)
FIX: Data is not replicated to a subscriber in a different partition by using parameterized row filters in SQL Server 2005


50001529
940945 (http://support.microsoft.com/kb/940945/)
FIX: Performance is very slow when the same stored procedure is executed at the same time in many connections on a multiple-processor computer that is running SQL Server 2005

50001578
939285 (http://support.microsoft.com/kb/939285/)
FIX: Error message when you run a stored procedure that starts a transaction that contains a Transact-SQL statement in SQL Server 2005: "New request is not allowed to start because it should come with valid transaction descriptor"


50001525
938086 (http://support.microsoft.com/kb/938086/)
FIX: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005


50000872
936252 (http://support.microsoft.com/kb/936252/)
The file name of Cumulative Update 3 for SQL Server 2005 Service Pack 2 is incorrectly associated with Microsoft Knowledge Base article 936252


50000872
The MDX query performance is slow in SQL Server 2005 Analysis Services because SQL Server 2005 Analysis Services does not reuse the data cache
50001109
The dta utility stops unexpectedly and an exception occurs in SQL Server 2005
50001224
When you run a SQL Server 2005 Integration Services package that uses the VariableDispenser class, the package fails and you receive an error message
50001365
After you install SQL Server 2005 Service Pack 2, you receive error 8624 if the result set of a fast forward cursor contains a certain number of columns
50001368
After you install SQL Server 2005 Service Pack 2, the performance of a MDX query is 10 times slower than the performance on SQL Server 2005 Analysis Services build 1555
50001396
When you open a SQL Server 2005 Reporting Services report after you install SQL Server 2005 Service pack 2, the parameter toolbar and the report toolbar does not appear correctly if you specify the SP_Full value for the rc:StyleSheet URL access parameter on a report URL
50001412
Error message when you use the Copy Database Wizard to move a database from SQL Server 2000 to SQL Server 2005: "Cannot drop database "Database_Name" because it is currently in use.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
50001414
Error message when you use the bcp utility together with the queryout option to bulk copy data from SQL Server 2005 to a file: "SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native Client]BCP host-files must contain at least onecolumn"
50001415
After you install SQL Server 2005 Service Pack 2, the Msmdredir.ini file is frequently updated by each instance of SQL Server 2005
50001436
Error message when you use the BACKUP DATABASE statement together with the UNLOAD option and with the REWIND option to back up a tape device in SQL Server 2005: "Operation on device 'TAPE0(<\\.\Tape0>)' exceeded retry count"
50001461
When some MDX queries are executed at the same for the same role or for the same user in SQL Server 2005 Analysis Services, the CPU usage is very high
50001475
When you open a report that contains a date and time picker (DTP) control in SQL Server 2005 Reporting Services, the format of the DTP control appears incorrectly
50001511
Error 9003 occurs when you restore a transaction log backup in SQL Server 2005
50001520
The query syntax of a report is changed when you run the report in SQL Server 2005 Reporting Services Service Pack 2 on SAP BW 3.5
50001522
Error 8624 occurs when you run a query in SQL Server 2005 Service Pack 2 (SP2) or later versions. However, you can successfully run the query in pre-SP2 version of SQL Server 2005
50001523
After you install SQL Server 2005 Service Pack 2, publications that use precomputed partitions can cause the non-convergence of data
50001525
Error message when you use SQL Server Agent to run jobs by using a proxy account: " SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)"
50001526
Using SQL Server Agent to run jobs in the context of a proxy account may fail with error "SQLServer Error: 22046, Encryption error using CryptProtectData, CryptUnprotectData failed (1723)"
50001529
The performance of SQL Server 2005 decreases because SQL Server 2005 is waiting for access to memory objects which is indicated by the CMEMTHREAD waittype
50001578
An exception occurs in SQL Native Client: "New request is not allowed to start because it should come with valid transaction descriptor"
50001579
When you use SQL Native Client to retrieve a value in a column of the TEXT data type, you obtain incorrect result if the value contains more than 1024 characters
50001580
Memory leak of the TokenAndPermAccessCheckResult entries occurs in SQL Server 2005
50001595
When you use SQL Native Client for a connection, the connection switches to use the auto-commit mode from the manually-commit mode
50001598
SQL Native Client overwrite error codes which causes that you do not receive informative error messages when some operations fails
50001639
The performance of an INSERT statement or an UPDATE statement that uses the result from a query is much slower in SQL Server 2005 Service Pack 2 than in SQL Server 2005 Service Pack 1 or earlier versions if the query uses the nodes method
50001164
FIX: Error message when you connect to an instance of SQL Server 2008 Analysis Services by using the AMO library that is included with SQL Server 2005 Service Pack 2 Analysis Services: "Cannot connect to Analysis Services version '10.0.1019.17'"

Monday, September 10, 2007

SQL Gotcha: Do you know what data type is used when running ad-hoc queries?

This is for SQL Server 2000 only, SQL Server 2005 is a lot smarter which is another reason to upgrade.
When running the following query you probably already know that 2 is converted to an int datatype


SELECT *
FROM Table
WHERE ID =2

What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.

CREATE TABLE TestAdHoc (id bigint primary key)

INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT
2433253453453466666 UNION
SELECT
2 UNION
SELECT
3 UNION
SELECT
4 UNION
SELECT
5 UNION
SELECT
6


Now let's run these 2 queries which return the same data

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666



SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

Now run the following SET statement and run the 2 queries again

SET SHOWPLAN_TEXT ON

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666


SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

And what do we see?

First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)

Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)


The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.

So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.

SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'

So the output is this numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.


SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))


So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.

Wednesday, September 05, 2007

Microsoft SQL Server 2008 CTP 4 Released

Microsoft SQL Server 2008 CTP 4 has been released 20070831 (yes that is ISO format)
So I have been sleeping for the last couple of days and missed this. Connect didn't email me either ;-(

This download comes as a pre-configured VHD. This allows you to trial SQL Server 2008 CTP 4 in a virtual environment.

Get it here: http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en&tm