Q. Execute an Operating System Command From Within SQL Server (Completed 36)
A. The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server.
Example:
EXEC Master..xp_cmdshell ‘Dir c:\’
www.careerenclave.com
Q. How can I create a plain-text flat file from SQL Server as input to another application?
A. One of the purposes of Extensible Markup Language (XML) is to solve challenges like this, but until all
applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility.
This utility can do more than just dump a table; bcp also can take its input from a view instead of from a table. After you specify a view as the input source, you can limit the output to a subset of columns or to a subset of rows by selecting appropriate filtering (WHERE and HAVING) clauses.
More important, by using a view, you can export data from multiple joined tables. The only thing you cannot do is specify the sequence in which the rows are written to the flat file, because a view does not let you include an ORDER BY clause in it unless you also use the TOP keyword.
If you want to generate the data in a particular sequence or if you cannot predict the content of the data you want to export, be aware that in addition to a view, bcp also supports using an actual query. The only “gotcha” about using a query instead of a table or view is that you must specify queryout in place of out in the bcp command line.
For example, you can use bcp to generate from the pubs database a list of authors who reside in California by writing the following code:
bcp “SELECT * FROM pubs..authors WHERE state = ‘CA’” queryout c:\CAauthors.txt -c -T -S
Q. How can I programmatically detect whether a given connection is blocked?
A. A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server process ID (SPID) to filter the information that sp_lock returns. To determine whether a given process is waiting for the release of a locked resource, you can execute the sp_GetBlockInfo procedure that follows.
Note: You must execute the procedure before the timeout.
USE master
GO
CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)
SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, Cool As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database],
OBJECT_NAME(sli1.rsc_objid) AS ,
sli1.rsc_Text AS
[Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = ‘L’
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE
SELECT CAST(1 as int) AS SPID,
SUBSTRING (”, 1, Cool AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS ,
CAST(NULL AS nchar(32)) AS
[Resource]
WHERE 1=2
GO
The sp_GetBlockInfo procedure tells you the lock mode, the database and object names of the locked resource, and in the case of a blocking chain, which SPID is the root blocker. If the process is not blocked,
sp_GetBlockInfo returns an empty recordset.
You can also detect blocks by checking for error 1222, “Lock request time out period exceeded.” The LOCK_TIMEOUT setting controls how long a process will wait for locks to be released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the application. In SQL Server 7.0, this error aborts the statement but does not cause the batch to roll back, so you can look for the Transact-SQL system variable @@ERROR and determine where locks exist
Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server 2000?
A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns,
you can create UNIQUE and PRIMARY KEY constraints on computed columns.
Defining a UNIQUE constraint on a computed column is a straightforward process, as the following example shows:
CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)
However, if you define a PRIMARY KEY on a computed column, such as:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)
you receive the following error:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘T2′.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Because of the primary key constraint, SQL Server requires you to guarantee that your computation’s
result will not be NULL. The computation in the computed column can overflow
(for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from the smallest integer), and other computations can result in a divide-by-zero error. However, if the ARITHABORT
(which determines whether a query has ended when an overflow or a divide-by-zero error occurs)
and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error conditions)
session settings are off, instead of ending the query, the computation can have a NULL result.
In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot create an index on a computed column or insert values into a table that has an index on a computed column because SQL Server detects such an attempt and returns an error. But SQL Server still requires you to guarantee that the computation will not result in NULL values. The trick is to wrap the computed column’s computation with the ISNULL() function and supply an alternative value if the computation results in NULL:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)
Q. Why does my inline or embedded SQL run faster than my stored procedures?
A. Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:
SELECT * FROM dbo.mytable
This technique helps you reuse plans and prevent cache misses.
Q. Why doesn’t SQL Server permit an ORDER BY clause in the definition of a view?
A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can’t fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround:
USE pubs
GO
CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO
The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword.
Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.
Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?
A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you’re dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward. A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.
Q, The Difference between ‘Count’ and ‘Count(*)’
A. ‘Count’: Counts the number of non-null values.
‘Count(*)’: Counts the number of rows in the table, including null values and duplicates.
Q. I have two tables t1 and t2 both with the columns a1, a2. I want to find the difference of (the set of t1) – (the set of t2) without using the keyword EXCEPT because MSSQL 2000 does not recognize that word. I have tried this query but it does not give me what I want: SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.* FROM t1 INNER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)
A.SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)
This is the solution. The difference of (the set of t1) – (the set of t2) is
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2 WHERE t2.a1 IS NULL AND t2.a2 IS NULL
According to SQL Query Analyzer, this is slightly more efficient than the left join (possibly only because
of the tables I tested it with):
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.a1=t2.a1 AND t1.a2=t2.a2)
Q, Leading Zero’s in Stored Procedure
I have a form and I am passing a value to a stored procedure. The value has leading zeros. When the values are passed the leading zeros are dropped, thus causing my stored procedure to blow up. Is there a way to maintain those zeros in passing or pick them up again in the procedure?
Try to pass value as string.
If you were passing a string value then the LZ should not be dropped, so I suspect that you are passing a
numeric value then converting it back to a varchar which will drop any LZ. If you don’t want to change the interface then you can always restore the LZ in the stored procedure by using the following.
declare @lz varchar(10)
declare @numeric numeric(10,2)
declare @result varchar(20)
select @lz = ‘0000000000′
select @numeric = 0000123.45
– the LZ will be lost upon assignment
select @result = substring(@lz, 1, datalength(@lz)-datalength(convert(varchar
Q, Method to Perform Case Sensitive Searches in SQL Server
A, By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive mode. For most applications this may work great, but there are certain situations were case sensitive searches are required. For instance, if a web site needs to have passwords that are case sensitive a method needs to be devised to perform case-sensitive queries. The following script performs a case-sensitive search. First let’s create a table:
CREATE TABLE test
(
id INT NOT NULL,
msg VARCHAR(100) NOT NULL
)
Now let’s insert some case sensitive data into it:
INSERT INTO test (id, msg) VALUES (1, ‘bUSY’);
INSERT INTO test (id, msg) VALUES (2, ‘BUSY’);
INSERT INTO test (id, msg) VALUES (3, ‘busy’);
In our test we are searching for a ‘bUSY’ value in the msg column of the test table. So the syntax of the same query, if the SQL Server was set to be case sensitive, would be:
SELECT * FROM test where msg = ‘bUSY’;
This query will return all rows in the test table. Now, here is the script that will perform the case sensitive search.
DECLARE @table VARCHAR( 30 )
DECLARE @col VARCHAR( 30 )
DECLARE @searchVal VARCHAR( 195 )
SET @table = ‘test’
SET @col = ‘msg’
SET @searchVal = ‘bUSY’
DECLARE @sql VARCHAR( 255 )
DECLARE @colLength VARCHAR( 3 )
SELECT @colLength = CONVERT( varchar(3), DATALENGTH( @searchVal ) )
SELECT @sql = ‘SELECT * ‘ +
‘ FROM ‘ + @table +
‘ WHERE’ +
‘ CONVERT( varbinary( ‘ + @colLength + ‘), ‘ + @col + ‘) = ‘ +
‘ CONVERT( varbinary( ‘ + @colLength + ‘), “‘ + @searchVal + ‘”)’ +
‘ AND ‘ + @col + ‘=”‘ + @searchVal + ‘”‘
EXEC( @sql )
Listing Available Tables
Q, How do I list the available tables in a database I’m querying?
A, You can get a list of tables in a database by calling the INFORMATION_SCHEMA.Tables view like this:
SELECT * FROM information_schema.tables
This returns the name of the current database, the owner, the table name, and the table type for each table in the database. It’s possible to query the system tables directly, but if this gives the information you need,
it’s better to use the existing views that come with SQL Server.
Question: How do I count the number of duplicate items in a table?
Answer: Lets break down your question into several steps. First, let’s create a sample table using the following code:
create table dups
(
i int
)
go
declare @i int
@i = 0
while (@i < 35)
begin
insert into dups(i) values (cast (rand() * 50 as int))
select @i = @i + 1
end
Now, let’s find rows that are duplicates. For that we can use a simple group by statement:
select i,count(*) as num_records from dups group by i having count(*) > 1
My sample data produced the following result set:
i num_records
———– ———–
0 2
5 2
18 2
22 2
27 2
31 2
34 2
44 2
49 2
This identifies the rows that have duplicates. But it does not return the total number of duplicates in the table. The first change we must make is to recognize the above rows that show 2 contain only one duplicate.
So we want a query that basically sums up the duplicates from the above query. To do so, we take the previous query and can put that in the from statement as a derived table. We then can use the sum function to create the total for us:
select sum(num_dups)
from (select i,count(*)-1 as num_dups
from dups
group by i
having count(*)-1 > 0) as mydups
Using Distributed Queries on SQL Server 7.0
SQL Sever 7 allows developers to execute commands against OLE DB data sources on different servers. In order to execute commands on remote servers, the SQL Server instance where the commands will be issued must be setup properly. This entails adding the remote server to SQL Server’s linked server list. Do this by using the sp_addlinkedserver command.
For example, to link a remote SQL Server database that resides on the RemoteDBServer server, you would usethe following syntax:
EXEC master. . sp_addlinkedserver
@server = ‘RemoteDBServer’,
@provider = ‘SQLOLEDB’,
@srvproduct = ‘SQL Server’,
@datasrc = ‘RemoteDBServer’
Note that only members of the sysadmin role can set this server option. Once the remote database has been linked, queries can be executed against it as long as the remote tables are prefaced using Server.Database.Table Owner.Table Name.
For example, the following query would return all rows in the authors table of our RemoteDBServer SQL Server database:
SELECT * FROM RemoteDBServer.pubs.dbo.authors
Dynamic Query with …. in (@var)
Question: Can I use a variable in a query with the IN clause (a,b,c..z), without getting quotes or conversion errors?
Answer: You can use a variable as your IN clause, but this requires that you use the EXEC function to run the statement.
Delete Files from SQL Server Local Machine
Ever wanted to delete files from the local machine that your SQL Server database is running?
You can do it using the extended stored procedure xp_cmdshell like this:
EXEC master..xp_cmdshell ‘del C:\file.txt’
But this requires the sysadmin option on the SQL Server and Admin role from NT server. In most instances it is not preferable to give these privileges. So to delete files without requiring this access use the built-in SQL Server Automation APIs and the FileSystemObject:
DECLARE @hr int
DECLARE @ole_FileSystem int
EXEC @hr = sp_OACreate ‘Scripting.FileSystemObject’, @ole_FileSystem OUT
EXEC @hr = sp_OAMethod @ole_FileSystem, ‘DeleteFile’, NULL, ‘C:\file.txt’
EXEC @hr = sp_OADestroy @ole_FileSystem
Large Text Fields
Question: How do I create a text field of greater than 8,000 characters (in v7.0)? I attempted to use the “text” data type, but my code returned an error saying the maximum size was 8,000. Here’s the code:
CREATE TABLE X ( X_ID int IDENTITY(1,1), X_DESC text (60000) NOT NULL ) GO
Answer: SQL Server is returning a bogus error message. The real error has to do with your syntax.
When specifying text you don’t specify a size.
You can see the real error message if you reduce the number 60000 to 5. Then you will get this message:
Server: Msg 2716, Level 16, State 1, Line 1
Column or parameter #2: Cannot specify a column width on data type text. Instead, simply specify it as text without the parentheses and the number. The actual size of the storage used for the text field will depend on how much data you actually put in the column
Importing Excel into SQL without Using Wizard
Question: How do I give a user the option of importing Excel and a delimited text file into a SQL Server Database without manually using SQL DTS?
Answer: You can use the DTS object model to programmatically create, modify and run DTS packages.
You can do this many ways, but essentially this object model has objects for anything you can do in DTS.
If you already have the package created and saved as a file, add a reference to the DTS Library.
Then you can call it like this:
Dim Package As New DTS.Package
Package.LoadFromStorageFile App.Path “\DTSPackage.dts”
Package.Execute
Connecting to SQL Server 7 via MS-DOS 6.2
Question: I have some complex engine control software that has to run under MS-DOS 6.2, but it needs to transfer data to SQL Server 7. What’s the best way to connect to SQL Server from DOS?
Answer: You can use OSQL or ISQL to connect to SQL Server from the command line. Both of these utilities are Win32 command-line utilities that allow you to log in to a server and issue any SQL command. The OSQL utility supports the most functionality and uses ODBC to connect to SQL Server.
The syntax “osql/?” provides a listing of all OSQL flags.
This syntax connects to a server and issues a query:
osql -S “servername” -U “sa” -d “pubs” -q “SELECT * FROM Authors”
To insert data into a database, you could put OSQL commands in a batch file or provide an input file of
SQL INSERT commands. See the SQL Server BOL for details and syntax of OSQL. If you have a straight DOS box, you need to install the 16-bit client that comes with SQL Server 6.5. Both the client and the 6.5 version of ISQL use DB-Lib, which doesn’t support some SQL Server 7 features like unicode columns, varchar columns larger than 255, etc.
Passing a CSV List Within an IN Statement
Question: I have a CSV list I am passing through as a parameter in a stored procedure.
Example of Region list: 1,2,3,4,5,6,7,8,9,10
CREATE PROCEDURE sp_getReport
@Regionlist varchar(100) AS
Select * from tblRegion where Region_ID IN (@regionlist)
The varchar treats the list as one block of data. How can I change my code/syntax to read each value of the list separately within the IN statement?
Answer: When you pass variables that are part of the statement in a stored procedure, you need to use
the EXEC(UTE) statement. Here’s the syntax to call the stored procedure and modify the current stored procedure to take the regions list as a parameter (the only difference is I called the column RegionID instead of Region_ID):
sp_getreport ‘1,2,3,4,5,6,7,8,9,10′
CREATE PROCEDURE sp_getReport
@regionlist varchar(100)
AS
EXEC (‘Select * from tblRegion where RegionID IN (‘ + @regionlist +’)')
Creating Tables with Variable Names
Question: Is it possible to create tables in stored procedures using a variable for the table name?
For instance:
declare @MyName
set @MyName = ‘dbo.Unique_Name’
create table @MyName ….
This obviously doesn’t work, but does this ability exist?
Answer: Yes, you can issue DLL statements with variables; you just need to use the EXECUTE statement.
For example, this statement uses a variable for the table name, as in your example:
DECLARE @TableName varchar(255)
SET @TableName = ‘[dbo].[TestTable]‘
EXEC (‘CREATE TABLE ‘ + @TableName + ‘ (
[ID] [int] NULL ,
[ItemDesc] [char] (50) NULL
)’)
Use Sysobjects in SQL Server to Find Useful Database Information
SQL Server sysobjects Table contains one row for each object created within a database. In other words,
it has a row for every constraint, default, log, rule, stored procedure, and so on in the database.
Therefore, this table can be used to retrieve information about the database. We can use xtype column in
sysobjects table to get useful database information. This column specifies the type for the row entry in sysobjects.
For example, you can find all the user tables in a database by using this query:
select * from sysobjects where xtype=’U’
Similarly, you can find all the stored procedures in a database by using this query:
select * from sysobjects where xtype=’P’
This is the list of all possible values for this column (xtype):
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Query to a Text File
Question: I would like to create a stored procedure that runs a query and outputs the results to a text file and allows me to add extra delimeters and static field info.
Answer: SQL Server has no native command for outputting query results to a file. You can use the extended
stored procedure xp_cmdshell and call isql (command-line SQL) with your query and output the results to a file. Any delimiters would need to be part of the SELECT string:
DECLARE @isqlString varchar(255)
SELECT @isqlString = ‘isql -Q “SELECT DateCol FROM NorthwindTest.dbo.Test” -E -o C:\Results.txt’
EXEC master..xp_cmdshell @isqlString
Dynamic Variables and Stored Procedures
Question: I need to pass the @TheVar variable to the stored procedure’s IN statement but I keep getting zero records. If I type this statement into the SQL… WHERE Name IN (‘John’, ‘Frank’, ‘Tom’) the statement works fine; 20 records are returned.
EXECUTE the_proc “‘John’, ‘Frank’, ‘Tom’”
CREATE PROCEDURE the_proc
@TheVar nvarchar(40)
AS SELECT COUNT(FieldName) FROM Clients WHERE Name IN (@TheVar)
Do you have any ideas? I use MS SQL Server 7.0 SP2.
Answer: If you want to use variables as part of your SQL statement you need to modify it to call the EXECUTE statement. I created a Clients table with a single Name field of varchar(255) with records for John, Frank, and Tom. Then I created a stored procedure (similar to yours) that uses the input variable to build the IN clause like this:
CREATE PROCEDURE GetClients
@TheVar varchar(255)
AS
EXEC (‘SELECT COUNT(Name)
FROM Clients
WHERE Name IN (‘ + @TheVar + ‘)’)
As you can see, it builds the SELECT statement using the variable and then the EXEC statement runs it.
The syntax to call this procedure is:
GetClients “‘John’, ‘Tom’, ‘Frank’”
—
3
Question: If my SQL Server has the following specs: 6.05.02 SQL-DMO 6.50.252 DB-Library Which version do I have? And which service pack version do I have?
Answer: If you want to query the version of SQL Server that’s currently running you can use the @@version variable:
SELECT @@version
This returns the version, processor, build and service pack information for the currently installed SQL Server. This information is stored in the system tables, and you can retrieve more details by calling the extended stored procedure xp_msver. Be sure to call it from the master database.
Data Type
Question: How do I compare two values when one value(data type) is char and the other is an integer?
Answer: For this you can use the CONVERT function. Since you can’t convert character data to numbers,
you’ll have to convert the integer column to character. Assuming you have a table called TestTable with two columns:
IntCol int
CharCol char(10)
with these values
IntCol CharCol
—— ——-
1 1
2 2
3 xyz
4 5
You can use this query to SELECT rows based on a comparison:
SELECT * FROM TestTable WHERE CONVERT(char(10), IntCol) = CharCol
IntCol CharCol
———– ———-
1 1
2 2
E-mail in a Query
www.careerenclave.com
Question: How do I send an e-mail to myself if a field reads “SEND_MAIL?”
For example, a user wants more info on a product, so he clicks on the Send Mail button, which in turn throws an entry into the database. This entry is SEND_MAIL. Now at the end of the day I would like the server to scan the database for the word SEND_MAIL and, when it finds one, send me an email with the name and email address of the person.
Answer: To answer this question I took the scenario of a Web site that logs user registrations and puts a
visitor’s first name, last name and e-mail address in a table. I then created a stored procedure that selects
this data and e-mails it to an e-mail address provided as an input parameter. If the SendMail column in the table is Y, then the details will be e-mailed. The stored procedure is called like this:
usp_CheckForMail ‘name@emailaddress.com’
The script to create the table is as follows:
if exists (select * from sysobjects where id = object_id(N’[dbo].[TestTable]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Fname] [varchar] (50) NULL ,
[Lname] [varchar] (50) NULL ,
[EmailAddress] [varchar] (100) NULL ,
[SendMail] [char] (1) NULL
) ON [PRIMARY]
GO
The script to create the stored procedure to send the e-mails is:
if exists (select * from sysobjects where id = object_id(N’[dbo].[usp_CheckForMail]‘) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[usp_CheckForMail]
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_CheckForMail
@SendToEmail varchar(255)
AS
DECLARE @ID int, @Fname varchar(50), @Lname varchar(50), @EmailAddress varchar(100), @MsgText varchar(255)
DECLARE WebVisitors CURSOR FOR
SELECT ID, Lname, Fname, EmailAddress FROM TestTable
WHERE SendMail = ‘Y’
ORDER BY Lname, Fname
OPEN WebVisitors
FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress
EXEC master.dbo.xp_startmail /* typically mail will be started on your server */
– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
– PRINT ‘Visitor: ‘ + @Fname + ‘ ‘ + @Lname + ‘ at ‘ + @EmailAddress + ‘ [ID=' + CAST(@ID AS varchar) + ']‘
SET @MsgText = ‘Visitor: ‘ + @Fname + ‘ ‘ + @Lname + ‘ at ‘ + @EmailAddress + ‘ [ID=' + CAST(@ID AS varchar) + ']‘
EXEC master.dbo.xp_sendmail @recipients = @SendToEmail, @message =@MsgText, @subject = ‘New Web Visitor’
FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress
END
CLOSE WebVisitors
DEALLOCATE WebVisitors
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
Pros and Cons of Extended Stored Procedures
Question: What are the pros and cons of creating extended stored procedures?
Answer: The pros are that you can implement additional functionality and access data from DLLs from within SQL Server. If you need to do something that can be done only in C or C++, or if you have data that can be accessed only outside of SQL Server, you can still provide a link to it. The biggest con to extended stored procedures is that they run in the same process space as SQL Server. So an errant DLL could overwrite memory and cause SQL Server to crash or even corrupt data. The biggest safeguard against these problems is thorough testing of the procedure
Percent Sign in SQL
Question: I have a table in which the key field has a value stored with a percent sign, like ‘1234%’. Using this value, I want to select from another table that can have values like ‘1234567′, ‘1234678′ and ‘1234098′. How do I go about it?
Answer: The percent sign (%) is a wildcard in SQL Server. It can be used at the beginning or end of a string. So the following syntax will return all of the records you mentioned:
SELECT * FROM TestTable WHERE Col LIKE ‘1234%’
Col
——-
1234567
1234678
1234098
If you want to do an exact match for ‘1234′ without the percent sign, then you’ll have to trim off the last character, like this:
SELECT * FROM TestTable WHERE Col LIKE LEFT(‘1234%’, (LEN(‘1234%’)-1))
