Optional parameters in SQL Server stored procedure

You can declare it like this: CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR (100) = ‘StringValue’, @Parameter3 VARCHAR (100) = NULL AS /* Check for the NULL / default value (indicating nothing was passed) */ if (@Parameter3 IS NULL) BEGIN /* Whatever code you desire for a missing parameter */ INSERT INTO …….. … Read more

How to deploy SQL Server Compact Edition 4.0?

i’ve created the solution. SQL Server Compact Edition is comprised of 7 dlls: sqlceme40.dll The undocumented, native, flat API library (The .net System.Data.SqlServerCe.dll assembly is a wrapper around this dll) sqlceca40.dll A COM dll that implements Engine, Replication, Error and a few other COM objects sqlceoledb40.dll A COM dll that implements an OLEdb provider for … Read more

How to implement polymorphic associations in an existing database

You could use Option 1 but without an additional surrogate Alternate Key. Instead, extend the existing Primary Key (of each entity), with an EntityType column (say CHAR(1), that would be E for Events, P for Persons, D for Products). The compound (EntityId, EntityType) will become then the Primary Key of table Entity and the corresponding … Read more

SQL Server: Split operation

if you can’t use table value parameters, see: “Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters” by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method: “Arrays and Lists in SQL Server 2005 and Beyond, When … Read more

Reporting Services permissions on SQL Server R2 SSRS

This did the trick for me. http://thecodeattic.wordpress.com/category/ssrs/. Go down to step 35 to see the error you are getting. Paraphrasing: Once you’re able to log in to YourServer/Reports as an administrator, click Home in the top-right corner, then Folder Settings and New Role Assignment. Enter your user name and check a box for each role … Read more

Why is SQL Server 2008 Management Studio Intellisense not working?

I understand this post is old but if anybody is still searching and has not found a solution to the intellisense issue even after re-installing, applying the cumulative updates, or other methods, then I hope I may be of assistance. I have Applied SQL 2008 R2 Service Pack 1 which you can download here http://www.microsoft.com/download/en/details.aspx?id=26727 … Read more

How to restore to a different database in SQL Server?

You can create a new db then use the “Restore Wizard” enabling the Overwrite option or: View the contents of the backup file: RESTORE FILELISTONLY FROM DISK=’c:\your.bak’ note the logical names of the .mdf & .ldf from the results, then: RESTORE DATABASE MyTempCopy FROM DISK=’c:\your.bak’ WITH MOVE ‘LogicalNameForTheMDF’ TO ‘c:\MyTempCopy.mdf’, MOVE ‘LogicalNameForTheLDF’ TO ‘c:\MyTempCopy_log.ldf’ This … Read more