When creating a SQL Server report and the layout is larger than it fits on an A4 or A3 page, then printing to PDF is the only option to make printing successful.

Adobe PDF format is scalable, i.e. it does not get restricted by the A4 or A3 size as long things are proportional. To explain that:

You set the report size in the report properties to A3 size is 42cm x 29.7 (for large reports) and then check the report body size, if that for example is 43.5 cm, then

the new report size to be set is (43.5 (body size) x 42 (report size)) / (report size - left margin - right margin). Then when you print this report in PDF, you will not get an extra page with only extra column.

The good thing about this way is now you can print in any paper size without any problem.

Let me know if that works for you.

---Calculates the first day of the previous month

1) AdventureWorks

2) Northwind and pubs databases

When we have the following ADO.NET code:

which calls a SQL Server stored procedure that includes this statement:

When deleting all records from a SQL Server table, you may need to reset the primary key to start from

zero again when inserting new rows. Just execute the following script:

DBCC CHECKIDENT('TableName', RESEED, 0)

When attaching a SQL Server 2000 database to a 2005 SQL Server, the 2005 IDE would not support opening the 2000 database diagrams. But there are two ways to make this happens: 1) In SQL Server 2005 Management Studio do the following:

1. Right Click on your database, choose properties
2. Goto the Options Page
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)" [more]

2) Or by script:

      EXEC sp_dbcmptlevel 'EnterYourDatabaseNameHere', '90';

Also, if you get this error when trying to add a new Diagram:

Error: Database diagram support objects cannot be installed because this database does not have a valid owner

Execute the following script:

EXEC sp_dbcmptlevel 'YourDB, '90';

go

ALTER AUTHORIZATION ON DATABASE::YourDB TO "sa"

go

We can retrieve a row number or Identity of a SQL Server table row using the script below:

select identity(int) col INTO #Temp from dbo.table

select * from #Temp

Idenity is retrieved when INTO is used to insert the record into a user or temporary table  

To get details about an SQL Server database using the following stored procedure:       

       sp_helpdb 'DatabaseName'

to get help about an SQL Server table, use the following stored procedure:        

Also, to list all tables that contain a given column name (Source: http://www.cryer.co.uk/brian/sqlserver/sqlsvrhowto.htm)

To drop all triggers from any database, execute this script:

declare @trigger varchar(1000)

declare @sql nvarchar(1000)

select @trigger = min([name]) from sysobjects where [name] like 'trig_prefix%'

while @trigger is not null [more]

begin

      set @sql = 'drop trigger ' + @trigger

      exec sp_executesql @sql

      select @trigger = min([name]) from sysobjects where [name] like 'trig_mi%' and [Name] > @trigger
end