Running DTS without SQL Server?

Seems like unreasonable request.Well, we had a client who wanted by click on icon on his Desktop to launch a DTS Package which runs on another computer.I'd like to share with you the script that friend of mine created on a workstation that did not have SQL Server installed.


MD "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"

COPY sqlresld.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"

COPY dtsffile.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"
COPY dtsffile.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"
regsvr32.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsffile.dll"

COPY dtspkg.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"
COPY dtspkg.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"
regsvr32.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll"

COPY dtspump.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"
COPY dtspump.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"
regsvr32.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspump.dll"

COPY axscphst.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"
COPY axscphst.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"
regsvr32.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn\axscphst.dll"

COPY dtsrun.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn"

COPY dtsrun.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"

COPY custtask.dll "\Program Files\Microsoft SQL Server\80\Tools\Binn"
COPY custtask.rll "\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033"
regsvr32.exe "\Program Files\Microsoft SQL Server\80\Tools\Binn\custtask.dll"

COPY sqlunirl.dll %SYSTEMROOT%\system32

PAUSE
--------------
"\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" /FProcessAppSalesCube.dts

PAUSE

How to render the Report to PDF/EXCEL format

I had a client who wanted to get his report directly to PDF file. Previously, he used to use a Report Viewer component in application and then exporting it to the appropriate format.
In this example I render my report to the PDF file that will be located on disk C:\.
This report also accepts one parameter.The key of the module is ReportExecutionService class which contains some methods that we need to use.

Please refer to the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rswsref9/html/d6ce295b-25f4-4ce3-8d1a-765d7e7d9815.htm


--VB.NET

Imports Microsoft.SqlServer.ReportingServices2005

Private Sub CreatePdfFile()
Dim Res As New Execution.ReportExecutionService
Dim params(0) As Execution.ParameterValue
Res.Credentials = System.Net.CredentialCache.DefaultCredentials
Res.Url = "http://servername/ReportServer/ReportExecution2005.asmx?wsdl"
params(0) = New Execution.ParameterValue
params(0).Name = "ParamName"
params(0).Value = Value

Dim ReportPath As String = "/Reports/ReportName"

Res.LoadReport(ReportPath, Nothing)
Dim Format As String = "PDF"
Dim devInfo As String = Nothing
Dim extension As String = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim warnings() As Execution.Warning = Nothing
Dim stremIds() As String = Nothing
Dim result() As Byte = Nothing
Res.SetExecutionParameters(params, "en-us") --ParameterLanguage


result = Res.Render(Format, devInfo, extension, mimeType, encoding, warnings, stremIds)
IO.File.WriteAllBytes("C:\Report.pdf", result)
End Sub

Executing multiple script files via SQLCMD

I'm sure most people either DBA or developers faced at least once a requirement to execute bunch of script files. As you know we can do it by using Script Task in SSIS, however I would like to share with you a infrequent used :r parameter in SQLCMD utilty. Let say you have three files (.sql) that do an UPDATE,INSERT and finally SELECT statements. All you need is to have a single batch file which will execute those .sql files.
Create a batch file named DML.sql and add the below code
:r "c:\myUpdate.sql"

:r "c:\myInsert.sql"

:r "c:\MySelect.sql"

Make sure that you leave free space between commands.
--Usage
EXEC master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -i"c:\DML.sql"'

I recommend you to read this article with more examples about how using :r parameter.
http://blogs.msdn.com/patrickgallucci/archive/2007/09/03/sqlcmd-and-the-power-of-the-little-r.aspx

Happy New Year

I think that is my first blog which does not relate to SQL Server,though why not?
I wish to SQL Server Consultants lots of contracts to help out businesses in 2009, I wish to SQL Server DBAs lots of interesting work but that their databases will run smoothly,I wish to SQL Server Developers to write efficient queries and resolve challenges and to every body and their families in 2009 I wish you health ,happiness,wealth and if you look forward to a good year ahead, spread happiness with these wonderful New Year wishes.

Do you always write parameterized query?

As you know SQL Server creates an execution plan for the query/stored procedure and stores it in procedure cache in order to reuse it when you run the query next time.
But more and more execution plans in memory will also hurt the performance as we do not have a control the procedure cache's size so what would you do? Lets see the below scenario I've ran on the DEV(DO NOT run on Production) machine. First of all I used DBCC FREEPROCCACHE which clears the procedure cache and causes ad hoc queries to
be recompiled. Next I ran actually the same query with different parameters.

DBCC FREEPROCCACHE

SELECT * FROM Production.WorkOrder
WHERE ProductID=522

SELECT * FROM Production.WorkOrder
WHERE ProductID=737

DECLARE @i INT
set @i = 518
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO
--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
1 49152 DECLARE @i INT set @i = 518 SELECT * FROM Production.WorkOrder WHERE ProductID = @i
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=737
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=522
*/
As you see SQL Server created THREE execution plans.
Now let's wrap the query within a stored procedure and see what will be happened.

CREATE PROCEDURE spTest
@i INT
AS
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO

EXEC spTest 522
EXEC spTest 737
EXEC spTest 518

--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
3 40960 CREATE PROCEDURE spTest @i INT AS SELECT * FROM Production.WorkOrder WHERE ProductID = @i
*/
Wow, you we get single execution plan that SQL Server used three times.
I also recommend you to read Tony's blog about the subject
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/07/procedure-cache-tuning-sizing-from-1gbyte-to-768kbytes-increase-the-size-of-usable-data-cache.aspx

Tony writes in his blog and I'm completely agree with him.
"Surprised? It's one of the reasons DBA's keep bleating on about using stored procedures, it forces the mindset to use the procedure cache more effectively. By parameterising, but preferably using stored procedures we get plan reuse which means a) less compiles thereby reducing CPU load, b) more available pages for the data cache thereby reducing physical disk IO and c) DBA’s are always right ;)."


PS.Besides using stored procedures we can use sp_executesql with parameters as below

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 722

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 522

.....

GO

When constraint remains in system table even the table is no longer exists

Ok, I'm talking about temporary tables and yes,it could happen to you even you think that the temporary table is no longer exists. Consider the below script where stored procedure inserts the data with wrong type which caused the error(245conversion error). As you propably know we would never name the constraints for local temporary tables (as opposite to permanent tables), because if we run the following statement from two connections we get something like that.
/*
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_tmp1' in the database
*/


USE master
GO
ALTER PROCEDURE spSample
AS
CREATE TABLE #tmp(
col int --PRIMARY KEY NONCLUSTERED--,
CONSTRAINT PK_tmp1 PRIMARY KEY NONCLUSTERED
(
col
)
)
BEGIN TRY
INSERT INTO #tmp SELECT 'A'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
END CATCH

--Usage
USE master
EXEC spSample


USE tempdb
SELECT * FROM sys.objects
WHERE OBJECT_NAME(OBJECT_ID)like '%#tmp%'

Try add DROP TABLE #tmp in BEGIN CATCH ..block and then SQL Server deletes the constraint as well.

Can I hide undesirable database in SSMS?

We have discussed the issue many times and there is an opened connection to Microsoft to add this feature in the next release. Recently I was visiting our client and we tried to do something for the subject.

1) CREATE new SQL login 'John'
2) CREATE a user named 'John in master database
3) GRANT CREATE DATABASE to John
4) While impersonating John, create a database called 'demo'
5) REVOKE CREATE DATABASE permission from John
6) REVOKE VIEW ANY DATABASE permission from PUBLIC
7) Register this server as John
8) From the 'John' session, expand database tree. Now, you should see only master, tempdb, dbtest
9) GRANT VIEW ANY DATABASE to PUBLIC
10) From the 'John' session, you should see all the databases.

However, this works perfectly if the user is the owner of the database.