SQL Server Notes

Restarting server on remorte desktop

2011-06-27T10:22:00.000-07:00

command prompt:
Shutdown /r /f /m bltrdevm58123 /t 0

Drop and Create Foreign keys

2011-05-11T17:38:00.000-07:00

This query generates the drop and create statements for foreign keys in SQL Server.
DECLARE
@constraint_name sysname,
@parent_schema sysname,
@parent_name sysname,
@referenced_object_schema sysname,
@referenced_object_name sysname,
@column_name sysname,
@referenced_column_name sysname,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint,
@AddLine nvarchar(max),
@DropLine nvarchar(max),
@fkline nvarchar(max),
@pkline nvarchar(max),
@object_id int,
@parent_object_id int

DECLARE
@AddScript TABLE (line nvarchar(max))
DECLARE
@DropScript TABLE (line nvarchar(max))

SET NOCOUNT ON

-- Create cursor for foreign keys system view
DECLARE cFKeys CURSOR READ_ONLY
FOR
SELECT object_id,
parent_object_id,
OBJECT_SCHEMA_NAME(parent_object_id),
object_name (parent_object_id),
[name],
is_not_trusted,
OBJECT_SCHEMA_NAME(referenced_object_id),
object_name(referenced_object_id),
delete_referential_action,
update_referential_action,
is_not_for_replication
FROM sys.foreign_keys

OPEN cFKeys

-- Collect basic data
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Start creating command string. One for add and one for drop constraint
SET @AddLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
SET @DropLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
-- Check if it is enabled or not
IF @is_not_trusted = 1
SET @AddLine = @AddLine + N' WITH NOCHECK'
ELSE
SET @AddLine = @AddLine + N' WITH CHECK'

SET @AddLine = @AddLine + N' ADD CONSTRAINT ' + quotename(@constraint_name) + N' FOREIGN KEY ('
SET @DropLine = @DropLine + N' DROP CONSTRAINT ' + quotename(@constraint_name)

-- Gather all columns for current key from foreign key columns system view
DECLARE cColumns CURSOR READ_ONLY
FOR
SELECT fc.name, pc.name
FROM sys.foreign_key_columns fk
inner join sys.columns fc on fk.parent_object_id = fc.object_id and fk.parent_column_id = fc.column_id
inner join sys.columns pc on fk.referenced_object_id = pc.object_id and fk.referenced_column_id = pc.column_id
WHERE parent_object_id = @parent_object_id and fk.constraint_object_id = @object_id

OPEN cColumns

SET @fkline = N''
SET @pkline = N''

FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- One line for column list and one for referenced columns
SET @fkline = @fkline + @column_name
SET @pkline = @pkline + @referenced_column_name
END
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
IF (@@fetch_status = 0)
BEGIN
SET @fkline = @fkline + ', '
SET @pkline = @pkline + ', '
END
END

CLOSE cColumns
DEALLOCATE cColumns
-- Add column list
SET @AddLine = @AddLine + @fkline + N')' + CHAR(13)
-- Add referenced table and column list
SET @AddLine = @AddLine + 'REFERENCES ' + quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name)
SET @AddLine = @AddLine + N' (' + @pkline + N')'
-- Check the referential action that was declared for this key as well as replication option
SET @AddLine = @AddLine +
' ON DELETE' +
CASE @delete_referential_action
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
' ON UPDATE' +
CASE @UPDATE_REFERENTIAL_ACTION
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
CASE
WHEN @IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
ELSE N''
END
-- Insert command into table for later use
INSERT INTO @DropScript SELECT @DropLine
INSERT INTO @AddScript SELECT @AddLine

FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
END
END

CLOSE cFKeys
DEALLOCATE cFKeys

SET NOCOUNT OFF

SELECT line FROM @DropScript
SELECT line FROM @AddScript

SQL Server query All HEAPS

2011-05-11T16:35:00.000-07:00

This query pulls all the tables in SQL Server that are HEAPS. That is all tables without a cluster.

SELECT

SCHEMA_NAME(o.schema_id) AS [Schema]

,object_name(i.object_id ) AS [Tables]

,p.rows AS ROWS

,user_seeks

,user_scans

,user_lookups

,user_updates

,last_user_seek

,last_user_scan

,last_user_lookup

, i.type_desc

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id

WHERE i.type_desc = 'HEAP'

ORDER BY user_updates Desc

Tables without Primary keys

2011-05-05T14:49:00.000-07:00

SELECT SCHEMA_NAME(o.schema_id) AS [Schema]

,object_name(i.object_id ) AS [Tables]
,p.rows AS ROWS
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
, i.type_desc
FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
ORDER BY user_updates Desc

How to Attach (or Reattach) a Database Missing the LDF

2008-01-22T07:11:00.001-08:00

SQL Server 2005: How to Attach (or Reattach) a Database Missing the LDF
Home -> Database
3902 views

From the computer of: shamanstears (592 recipes)
Created: Nov 20, 2006 Updated: Nov 28, 2006

If you need to attach a database that has no log file (.LDF), or have deleted the log file because it was too big and need to reattach the database, SQL Server 2005 does allow you to do this. You can attach the database by following this Tech-Recipe:

1. Open the SQL Server Management Studio and connect to the desired server.

2. Right-click on Databases and select Attach.

3. Click the Add button and select the database file to be attached.

4. Under database details, select the .LDF file and click the Remove button.

5. Click OK.

The database will be attached with a new logfile.

SQL Server Version and SP Serveice Pack Level

2008-01-15T06:24:00.001-08:00

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Standard Production DBA Tasks: DBCC CHECKDB

2007-11-05T10:29:00.000-08:00

Standard Production DBA Tasks:

DBCC CHECKDB (Transact-SQL)
Updated: 1 February 2007
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

New SQL Server 2005 Features: DDL Trigger

2007-11-05T09:45:00.000-08:00

New SQL Server 2005 Features: DDL Trigger

SS05 has a nice feature that allows you to control who is dinking with your tables and what they can do.

“A DDL Trigger can fire after execution of any Transact-SQL event that belongs to a predefined grouping of similar events. For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement. After CREATE TRIGGER is run, the events that are covered by an event group are added to the sys.trigger_events catalog view.” http://msdn2.microsoft.com/en-us/library/ms186406.aspx

New SQL 2005 Features - On-line Restore.

2007-11-05T09:44:00.000-08:00

Favorite SQL 2005 Features On-line Restore.
On-line Restore. This works well with restoring one filegroup while not bringing down the whole system

Ben Link Recruiter

2006-06-07T10:07:00.000-07:00

Ben Link
Recruiter
Insight Global, Inc.
1420 Spring Hill Road
Suite 130
McLean, VA 22102
703.442.7717 phone
703.442.7716 fax
www.insightglobal.net"

Copy file and add time to name

2006-05-31T08:31:00.000-07:00

' Copy File
' Option Explicit

Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Dim sdate
sdate = CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date))

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "\\Server\C$\importexport\File_Name.asc"
sDestinationFile = "\\ Server\d$\DBA\ Archive\File_Name" & sdate &".asc"
'MsgBox( sDestinationFile)

oFSO.CopyFile sSourceFile, sDestinationFile

' Clean Up
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

YYYYMM

2006-05-30T06:49:00.000-07:00

SUBSTRING(CONVERT(varchar(8), [Date], 112), 1, 6) AS YYYYMM,

VBScript Functions

2006-05-18T12:13:00.000-07:00

VBScript Functions: "VBScript Functions

--------------------------------------------------------------------------------

This page contains all the built-in VBScript functions. The page is divided into following sections:

Date/Time functions
Conversion functions
Format functions
Math functions
Array functions
String functions
Other functions
"

VBScript - VB Script in DTS - Else Elseif

2006-05-18T12:06:00.000-07:00

VBScript - VB Script in DTS: "Im trying to use ActiveX within a DTS package. What I'm trying to do is us bit of simply VBScript to transmit data from one data source to another.

Example:

Function Main()
IF

DTSSource('Type') = 1

Then

DTSDestination('Type') = 'P1'

Else

DTSDestination('Type') = 'Other'

End If

Main = DTSTransformStat_OK
End Function

However this doesnt seem to work. Can anyone explain why this might be.

Cheers

Tek-Tips Forums is Member Supported. Click Here to donate.
mrmovie (TechnicalUser)14 Dec 05 6:22
can you confirm that
DTSSource('Type') actually returns something you can convert to a string?
this DTSSource('Type') statement doesnt make any sense to me.

if doesnt look like you pass anything to your function and your function appears to return something which isnt defined within the function, again i am not sure i understand this

NotSQL (TechnicalUser)14 Dec 05 6:28
Its ok i've got it to work... Thanks Though.

IF DTSSource('Type') = 1 Then

DTSDestination('Type') = 'P1'

Elseif DTSSource('Type') = 2 Then

DTSDestination('Type') = 'P2'

Elseif DTSSource('Type') = 3 Then

DTSDestination('Type') = 'P3'

Elseif DTSSource('Type') = 4 Then

DTSDestination('Type') = 'P4'

Elseif DTSSource('Type') = 5 Then

DTSDestination('Type')"

DTS: ActiveX rename File with Time and Date and Move

2006-04-12T08:21:00.000-07:00

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

' Copy File
' Option Explicit

Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Dim sdate
sdate = CStr(Year(Date)) & CStr(Month(Date)) & CStr(Day(Date) & CStr(Hour(Time)) & CStr(Minute(Time))& CStr(Second(Time)))
Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "\\svrdev\D$\MSSQL\Log_Ship_Test\VSTLog.trn"
sDestinationFile = "\\svrTest\D$\DBA\LogShipping_Test\VSTLog" & sdate &".trn"
'MsgBox( sdate)

oFSO.CopyFile sSourceFile, sDestinationFile

' Clean Up
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

VBScript delete files in a folder that are certain days old

2006-03-16T14:58:00.000-08:00

Using VBScript to Automate Tasks: "VBScript to delete files in a folder that are certain days old
As part of a disaster recovery plan, you may need to transfer backup files from one server to another. However, you probably don't want to keep accumulating backup files that you run out of space on your backup server. In this case, you want to delete files that are certain days (or weeks) old. The following VBScript can handle this task nicely. You can customize this code, such as changing the value of iDaysOld, to fit your needs. Most of the code should be self-explanatory. For more on disk space management, see one of my articles published a couple of weeks ago.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'Customize values here to fit your needs
iDaysOld = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files

'Walk through each file in this folder collection.
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing"

SQL Server 2000 Security - Part 11 - DTS Security

2006-03-16T07:27:00.000-08:00

SQL Server 2000 Security - Part 11 - DTS Security: "You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs."

Performing date comparisons in VB6

2006-02-24T07:39:00.000-08:00

Performing date comparisons in VB6: "Performing date comparisons in VB6

by Peter Aitken | More from Peter Aitken | Published: 8/1/05
The representation of dates in VB6 makes it really easy to compare dates, yet some programmers still go to unnecessary lengths to perform a comparison. Peter Aitken demonstrates that it's a breeze to perform date comparisons in VB6.

One of the ways that VB6 really shines is in how it handles dates using the Date data type. You can display a date in any one of several standard date formats, but internally, it's represented as a serial number specifying the number of days since December 31, 1899, with negative values used for dates prior to then. You can also use the decimal portion of the value to represent a time of day, but that's beyond the scope of this tip.

The representation of dates makes it really easy to compare dates, yet some VB programmers still go to unnecessary complexity to perform a comparison. There's no need to extract the year, month, and day individually for comparison-you can compare dates directly using the standard comparison operators.

For example, the date June 30, 2004, is represented by the serial number 38168. If you want to compare it with another date, you can just compare the serial numbers to see if one date is smaller, the same, or larger than the other.

Here's an example that executes one block of statements if the date stored in the type Date variable MyDate is earlier than January 1, 2000, and another block if it's the same or later than that date.

If MyDate < #1/1/2000# Then
' Statements here are executed if MyDate is earlier.
Else
' Statements here are executed if MyDate is the same or later.
End If

This is just one example of the flexibility of VB's Date data type."

Use Stars in SQL Server

2006-02-10T07:57:00.000-08:00

Create a histograph in SQL Server: "Create a histograph in SQL Server

by Arthur Fuller | More from Arthur Fuller | Published: 8/15/05

Takeaway:
Arthur Fuller worked on a project recently that assigned star ratings to each problem. This system led him to create code that demonstrates how you would present this data via SQL Server. Check out his non database-dependent example code.

Star ratings are often used in film, hotel, and restaurant ratings. However, I worked on a project recently that used the star ratings scale to identify how critical a problem is; for instance, the more stars, the more urgent it was that the problem be addressed. This led me to wonder how you would present this data via SQL Server.

For starters, you can store the number of stars as a smallint, or even derive it using a calculation, as the statement below illustrates. Then comes the issue of presentation—how to turn a rating of 5 into five asterisks, as they might appear in a newspaper's film or restaurant review.

Now you must decide how you want to deliver the data (which in this case are star ratings). You have two choices:

You can leave it up to the client. This is easy for you, but hard for the developers of each client because the more clients, the more redundant code.
You can do it in the database. There's no downside to this option, and every client can simply extract the column without having to calculate.
In a case like this, I choose to deliver the result from the back-end and not the front-end. My reasoning is simple: For every additional front-end, new application-specific code is required. When you deliver the result from the back-end, new applications can reuse code.

A non database-dependent example
Let's suppose a table contains a column called NumberOfStars (varchar(5)), as well as all of the other required details. We can create a virtual column of type varchar that presents as many stars as the NumberOfStars column wants.

To begin, use this code (you may substitute any valid column reference for the second parameter):

SELECT REPLICATE( '*', 4)Assuming a column called Stars is in a table called Reviews, you could write the following:

SELECT REPLICATE( '*', Reviews.Stars)
FROM ReviewsTry this command against the Northwind database:

SELECT Quantity, REPLICATE( '*', Quantity/5)
FROM [Order Details]
WHERE Discount IS NOT NULLThis will result in a list like this (the list is truncated to save space):

12 **
10 **
5 *
9 *
40 ********
10 **
35 *******
15 ***
6 *
15 ***In the code above, I use the most generic alphabet. Depending on the fonts available, you could make it more interesting by substituting a graphic character for the asterisk. For example, take a look through the three Wingdings fonts. If you see something you like, substitute that character for the asterisk. You won't see the results in Query Analyzer, which is typographically agnostic, but you should see them in another client.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!"

www.washingtonpost.com - Two-Candidate Marriage

2006-02-04T05:56:00.000-08:00

Baker Appears Undaunted By Fundraising Disparity: "Two-Candidate Marriage

Speaking of the Iveys, Glenn's wife, Jolene , is planning to run for the House of Delegates.

Jolene Ivey had been pondering for weeks whether to become a candidate for delegate or take on Britt, who has been mentioned as a possible running mate for Duncan. The legislators for the 47th District -- Britt and Democratic delegates Victor R. Ramirez , Rosetta C. Parker and Doyle L. Niemann -- are all serving their first terms.

"I figured I don't have enough to do," Ivey said with a chuckle, later rattling off a list of activities that keep the former stay-at-home mom's days jampacked. "But the bottom line is, when you want to get something done, you ask a busy person."

Ivey, the mother of five young boys who works part time doing public relations for Community Teachers Institute, a think tank in the District, said that the county needs to "get back on track" and that these are not the times for "wussy politicians" to be in office.

She has never run for elected office before -- unless you count student office in high school. "You would have thought I was running for president, though," she said."

Definition of UNC

2006-02-03T08:30:00.000-08:00

Definition of UNC: "UNC

Stands for 'Universal Naming Convention,' not just the home of the North Carolina Tar Heels. UNC is a filename format that is used to specify the location of files, folders, and resources on a local-area network (LAN). The UNC address of a file may look something like this:

\\server-name\directory\filename

UNC can also be used to identify peripheral devices shared on the network, including scanners and printers. It provides each shared resource with a unique address. This allows operating systems that support UNC (such as Windows) to access specific resources quickly and efficiently."

Using Parameters with Stored Procedures

2006-02-03T05:21:00.000-08:00

Using Parameters with Stored Procedures: "

Using Parameters with Stored Procedures
Regular Columnist : Kathi Kellenberger
Posted: 02/03/2006
(Original Publication Date: 8/8/2005)
More Articles From This Columnist
19,495 Reads

Join the discussion and read comments on this article.

Add to Your Virtual Briefcase
(What is this?)

Article Rating Total number of votes [49]

Summary:
It seems that SQL Server developers avoid stored procedures whenever possible, especially if they are new to the product. Kathi Kellenberger brings us a basic article that you can give to developers that explains the basics of how you use parameters with ADO.NET, especially output parameters.

--------------------------------------------------------------------------------
This Content Sponsored by:
Introduction
The prefered way for applications to access SQL Server data is by the use of stored procedures. The benefits include increased performance, security, and code reuse. Often, beginning SQL Server developers find the use of parameters, especially output parameters, with stored procedures confusing. This article will demonstrate how to use parameters with stored procedures, including some sample code using VB.Net. The examples shown are based on SQL 2000, but the concepts apply to SQL 2005 as well.

Creating a stored procedure
From Books Online, here is the syntax used to create a stored procedure:

CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

You can also use the alter procedure statement to modify an existing stored procedure. A stored procedure can have up to 2,100 parameters. It is probably safe to assume that the typical stored procedure has no more than ten.

Here is a very simple create procedure statement that takes two input parameters and one output parameter. Normally, a stored procedure will perform some kind of data manipulation or retrieval, so this is not the average example. It will, however, serve as a simple way to illustrate the use of parameters.

CREATE PROC usp_AddTwoIntegers @FirstNumber int = 5,
@SecondNumber int, @Answer varchar(30) OUTPUT as

Declare @sum int
Set @sum = @FirstNumber + @SecondNumber
Set @Answer = 'The answer is ' + convert(varchar,@sum)
Return @sum

How to define a paramter
The first thing to remember is that the parameter name must start with an @ symbol. The name can’t have spaces, and there are some characters that are not allowed. If you just stick with alpha-numeric characters and underscores in your parameter names you won’t have any problems naming parameters.

Second, the data type of the parameter and possibly the size must be stated. For example, the size of a parameter of type VARCHAR must be specified. Third, if you wish to access a return value from the parameter, the OUTPUT keyword must be used. In the example create proc statement, three parameters are defined, two are integers. One of the parameters, @Answer, is an OUTPUT parameter and will be used to pass a value back to the caller.

Parameters can be given a default value. The @FirstNumber parameter has a default value of 5.

How to Call a Stored Procedure from Query Analyzer
A stored procedure can be called from query analyzer, another stored procedure or any other client that can talk to SQL Server. Here is a sample script that can be pasted into Query Analyzer to demonstrate the use of our proc:

Declare @a int, @b int, @c varchar(30)
Select @a = 1, @b = 2
Exec usp_AddTwoIntegers @a, @b, @c OUTPUT
Select @c

---------------
The answer is 3

Notice that the keyword OUTPUT is used in the calling statement as well as the proc's definition. All of the arguments were defined as variables but given names that don’t match the original parameter names. This works because SQL resolves the parameters based on the position.

Only the output parameter must be defined up front. The next example passes literal values for the first two arguments.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers 1,2,@Answer OUTPUT
Select @Answer

---------------
The answer is 3

It is also possible to mix up the order of the arguments if the parameters are named in the calling statement.

The @FirstNumber parameter has a default that the proc will use since the argument is not specified.

Declare @Answer varchar(30)
Exec usp_AddTwoIntegers @Answer = @Answer OUTPUT, @SecondNumber = 5
Select @Answer

----------------
The answer is 10

Hopefully many of you have noticed that the actual numeric sum is also returned from the procedure. This example shows how to access the return value.

Declare @sum int, @Answer varchar(30)
Exec @sum = usp_AddTwoIntegers 5,5,@Answer
Select @sum

-----------
10

Normally return values are used to report the success or failure of the procedure. So, even though you can return any integer value from the proc, usually a 0 for success or 1 for failure is returned.

Stored procedures are usually used to retrieve or manipulate data. Here is a typical stored procedure using the Northwind sample database that ships with SQL 2000. The parameter is used in the where clause to filter the results.

Create proc usp_GetCustomerName @CustomerID varchar(5) as
Select CompanyName from Customers where CustomerID = @CustomerID

go

exec usp_GetCustomerName 'ANTON'

CompanyName
-----------------------
Antonio Moreno Taquería

Calling a stored procedure from VB.Net
As promised, here is a code snippet showing how to call a stored procedure using VB.Net. Again, no one would ever call a stored procedure to add two numbers together. This example is given to illustrate the use of parameters.

Create a new VB.Net Windows Application project. Add a button to the form. Add this statement to top of the form’s code window:

Imports System.Data.SqlClient

Paste this code into the form’s code window right above the End Class statement. I added the sample stored procedure to the Northwind database. Modify the connection string so it will work in your environment.

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

'You may need to modify the connection string
Dim conn As New SqlConnection("Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=Northwind;" _
& "Data Source=(local);Packet Size=4096;")
conn.Open()

'set up the command object
Dim myCommand As New SqlCommand("usp_AddTwoIntegers", conn)
myCommand.CommandType = CommandType.StoredProcedure

'add the first two parameters
myCommand.Parameters.Add("@FirstNumber", 5) 'this one is optional
myCommand.Parameters.Add("@SecondNumber", 5)

'The output and return parameters must be created as objects
Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar, 30)
myAnswer.Direction = ParameterDirection.Output

Dim mySum As New SqlParameter()
mySum.Direction = ParameterDirection.ReturnValue

'add them to the parameter collection
myCommand.Parameters.Add(myAnswer)
myCommand.Parameters.Add(mySum)

'execute the query
myCommand.ExecuteNonQuery()

'display the values
MsgBox(myAnswer.Value)
MsgBox(mySum.Value)
End Sub

Run the form and click the button to see the output parameter and return values.

Conclusion
Using stored procedures is the best way for applications to access SQL data. This article explained, using a very simple example, how to use parameters with stored procedures."

MSDN Replication Security Best Practices

2005-12-02T11:40:00.000-08:00

Replication Security Best Practices

Replication moves data in distributed environments ranging from intranets on a single domain to applications that access data between untrusted domains and over the Internet. It is important to understand the best approach for securing replication connections under these different circumstances.

The following information is relevant to replication in all environments:

Encrypt the connections between computers in a replication topology using an industry standard method, such as Virtual Private Networks (VPN), Secure Sockets Layer (SSL), or IP Security (IPSEC). We recommend using one of these encryption methods for the. For more information, see Encrypting Connections to SQL Server. For information about using VPN and SSL for replicating data over the Internet, see Securing Replication Over the Internet.
If you use SSL to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified. Agent parameters can be specified in agent profiles and on the command line. For more information, see:

How to: Work with Replication Agent Profiles (SQL Server Management Studio)

How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio)

How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)

Programming Replication Agent Executables

Run each replication agent under a different Windows account, and use Windows Authentication for all replication agent connections. For more information about specifying accounts, see Managing Logins and Passwords in Replication.

Grant only the required permissions to each agent. For more information, see the "Permissions Required by Agents" section of Replication Agent Security Model.

Ensure all Merge Agent and Distribution Agent accounts are in the publication access list (PAL). For more information, see Securing the Publisher.

Follow the principle of least privilege by allowing accounts in the PAL only the permissions they need to perform replication tasks. Do not add the logins to any fixed server roles that are not required for replication.

Configure the snapshot share to allow read access by all Merge Agents and Distribution Agents. In the case of snapshots for publications with parameterized filters, ensure that each folder is configured to allow access only to the appropriate Merge Agent accounts.

Configure the snapshot share to allow write access by the Snapshot Agent.

If you use pull subscriptions, use a network share rather than a local path for the snapshot folder.

If your replication topology includes computers that are not in the same domain or are in domains that do not have trust relationships with each other, you can use Windows Authentication or SQL Server Authentication for the connections made by agents (For more information about domains, see the Windows documentation). It is recommended as a security best practice that you use Windows Authentication.

To use Windows Authentication:

Add a local Windows account (not a domain account) for each agent at the appropriate nodes (use the same name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The Windows account for the Distribution Agent should be added to the Distributor and Subscriber.

Ensure that a given agent (for example the Distribution Agent for a subscription) runs under the same account at each computer.

To use SQL Server Authentication:

Add a SQL Server account for each agent at the appropriate nodes (use the same account name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The SQL Server account for the Distribution Agent should be added to the Distributor and Subscriber.

Ensure that a given agent (for example the Distribution Agent for a subscription) makes connections under the same account at each computer.

In situations that require SQL Server Authentication, access to UNC snapshot shares is often not available (for example access might be blocked by a firewall). In this case, you can transfer the snapshot to Subscribers through file transfer protocol (FTP). For more information, see Transferring Snapshots Through FTP."

Data Points: Inline Views Versus Temp Tables

2005-11-22T07:35:00.000-08:00

Data Points: Five Ways to Rev up Your SQL Performance "Inline Views Versus Temp Tables
Queries sometimes need to join data to other data that may only be gathered by performing a GROUP BY and then a standard query. For example, if you want to return the information about the five most recently placed orders, you would first need to know which orders they are. This can be retrieved by using a SQL query that returns the orders' IDs. This data could be stored in a temporary table, a common technique, and then joined to the Product table to return the quantity of products sold on those orders:

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
OrderDate DATETIME NOT NULL)

INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC

SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName

DROP TABLE #Temp1

This batch of SQL creates a temporary table, inserts the data into it, joins other data to it, and drops the temporary table. This is a lot of I/O for this query, which could be rewritten to use an inline view instead of a temporary table. An inline view is simply a query that can be joined to in the FROM clause. So instead of spending a lot of I/O and disk access in tempdb on a temporary table, you could instead use an inline view to get the same result:

SELECT p.ProductName,
SUM(od.Quantity) AS ProductQuantity
FROM (
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o
ORDER BY o.OrderDate DESC
) t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
p.ProductName
ORDER BY
p.ProductName

This query is not only more efficient than the previous one, it's shorter. Temporary tables consume a lot of resources. If you only need the data to join to other queries, you might want to try using an inline view to conserve resources."

Global Variables and SQL statements in DTS

2005-11-21T07:41:00.000-08:00

Global Variables and SQL statements in DTS: "Global Variables and SQL statements in DTS
By Darren Green
Version 7.0+/2000
Level Advanced

For those fortunate enough to have SQL 2000, using global variables with an Execute SQL Task, or as part of the source statement in the DataPump task is now a standard feature. For those still using SQL Server 7.0 this tedious process, but here are some examples of how it can be done. Even if you are using SQL Server 2000, you may still have a need for this due to certain syntax limitations imposed when using parameters.

In SQL Server 7.0 the only way to integrate a global variable into your SQL is to actually code the value as a literal in the SQL statement. The simplest solution is to just build a new statement with the value embedded in it at run-time. You can do this from an ActiveX Script Task as illustrated below.

This example amends the SELECT statement for a DataPump task, to filter based on the value of the global variable HireDate:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement
sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
DTSGlobalVariables("HireDate").Value & "'"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function"

SQL Server Best Practices Analyser Tool

2005-11-14T07:34:00.000-08:00

SQL Server Best Practices Analyser Tool: "SQL Server Best Practices Analyser Tool

Posted by poonam on Thursday, June 10, 2004

Microsoft's SQL Best Practice Analyser is here - and its a great little aid for DBA's
Microsoft released its ‘Best Practices Analyser Tool’ end of May. Though this tool has been in beta since late last year, it has entered the market softly, without any hype or fuss. This is certainly not the ‘Microsoft way’ – and it makes me wonder why, because this is a great little tool and its FREE!!!. You can download it from Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0

For those of you who didn’t know about it – this is a graphical tool which scans through your database and verifies it against a set of pre-determined rules. For the most part the rules relate to administration functions and its going to be invaluable aid to a DBA’s arsenal.

Just to see what the BPA can do, I have run this against a few production databases and a few development databases I have access to and the results were definitely worth the time.

I discovered for instance, that a crucial table at a client site did not have either a primary key or a unique key constraint on it. I’m pretty sure this table at some point did have a unique constraint, but somewhere over the months and many schema changes, somehow it got dropped – and no one noticed because everyone was so sure it existed. This is just the sort of thing the BPA does so well – nothing earth shattering in terms of knowledge – we all know tables should have primary keys, but the BPA provides us a way to monitor databases effortlessly.

Some database maintenance checks are nice to have – for instance the Index fragmentation, I currently have a stored procedure on a monthly schedule to do this – BPA I guess just makes it easier. Log file growth, virtual log file count – nothing we can’t do ourselves but this just makes it so easy to monitor my databases.

Many of the rules in BPA pertain to Backup and Recovery options, this can be an eye opener sometimes. There are always backup plans in place for production databases – but I was embarrassed to find out when our development databases were last backed up.

I also appreciate the SQL Server 2005 Readiness and depreciation check – it’s nice to know much ahead of time what wont work.

As always, I have a wish list (don’t we all).

I would love to be able to create my own rules.
The ability to setup and schedule the scripts to run at pre-determined times and frequencies would be nice. I have mixed feelings about this however – The best practice analyzer does have a command line interface, and with a bit of work, I can script this out for myself without having Microsoft have a cookie cutter solution for me. I’m probably in the minority when I say this, but I think it’s important that software engineers and DBA’s have the capability to code for themselves, without always looking to Microsoft for a point and click interface.
Can we have a ‘SQL Standard Edition’ compatibility check? So many of us develop on SQL developer edition and deploy to SQL standard edition. I would love to know, what is not supported in standard edition.
I would love to be run this as a background service, keeping a track of database objects being accessed. I currently need to run a profiler trace for this. This not only has a considerable performance impact but profiler traces are not fun to read. It would be helpful to know which objects are now archaic
Again if it can run as a background service, I want to know what indexes / statistics the optimizer is currently using. I want to know if I have unused indexes, ones which were scripted for a different time and now with application changes are not being used.
All things considered – I love the best practice analyzer – it’s a great little tool, and has already pointed out a number of little problems which if I had to do manually would take me a long long time."

Joe Sack: Importing files in SQL Server 2005 using OPENROWSET

2005-11-09T08:38:00.000-08:00

From http://www.sqljunkies.com/WebLog/joesack/default.aspx

Importing files in SQL Server 2005 using OPENROWSET
OPENROWSET has new BULK features which I'll definitely be taking advantage of in the future. With OPENROWSET you'll be able to return a result set from a file based on options in a format file (similar to bcp or BULK INSERT), or also import a file in its entirety as a single varbinary(max), varchar(max), or nvarchar(max) data type value.

It is the file import functionality that I'm really looking forward to. This is built-in functionality, so you don't have to depend on external applications to import external files into your SQL Server tables. You use the SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB to tell SQL Server what kind of single-row, single-column data is being read.

For example, in this query I create a new table that will hold product images and their associated descriptions. The ProductIMG column is using the new SQL Server 2005 varbinary(max) data type to hold gif files:

CREATE TABLE dbo.ProductImage

(ProductImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

ProductDESC varchar(255) NOT NULL,

ProductIMG varbinary(max) NOT NULL)

GO

Next, I use OPENROWSET to import a gif file directly into the table from the file system (the BulkColumn is the actual exposed varbinary(max) data being returned):

INSERT dbo.ProductImage

(ProductDESC, ProductIMG)

SELECT 'Used to represent two databases.', -- ProductDESC

BulkColumn -- ProductIMG

FROM OPENROWSET( BULK 'C:\Program Files\Microsoft SQL Server\80\Tools\HTML\dbicon.gif',

SINGLE_BLOB) as ExternalFile

That is all it takes. The file is now stored in the table in a single column of a single row. You could then use SSIS's Export Column task to export the data from these rows into external files again, assuming you added a file path and name column for the transformation to use.

In this example I imported a gif file - but definitely not a fan of serving image files from a database (storing maybe - but not serving). But this functionality definitely opens the door for those of us who don't or can't use third party applications to draw in external files. We can now use Transact-SQL instead.

One uses I may consider is in archiving critical, original legacy data files along with the normalized data. Used within reason of course, this would allow you to maintain the original file alongside the shredded/normalized data in the database so you can troubleshoot data lineage questions and issues.