|
|
-
|
|
Here are the queries that findout the cursors based on their statuses
1 Find out the cursors that are allocated but not opened or closed
--Method 1
select name from sys.dm_exec_cursors(0) where is_open =0
--Method 2
select
cur.cursor_name
from
sys.syscursorref......
|
|
-
|
|
Generating the Fibonnaci series generally involves in writing the code which has while loop or a
recursive function
Peso posted the code using the Common table expression
Here are my methods that involve no loop or no recursion
1 Use Quirky update technique
declare @fib table(fib_id bi......
|
|
-
|
|
One of the new features available in SQL Server 2008 is scripting the tables along with data (in
the form of INSERT statements). Here are the useful links that can help you generating script
with data
SQL Server 2008
http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-......
|
|
-
|
|
One of my friends asked me if it possible to generate Detailed data for each Header data without
using a Cursor or while loop. (The output is something like the one usually seen via reporting
tool). He wanted the result to be output to text file
Consider the jobs and employee tables from pubs......
|
|
-
|
|
<pre>One of the posters asked a question in the forums about finding a number in an expression
consists of alphabets and number if a particular digit is provided
Consider the following set of data
this is test place located at no 123456 test
foo 35234
bar 126831
72348707 foo
foo 987654 ......
|
|
-
|
|
Here are the Command Prompt shortcuts to open Enterprise Manager/Management Studio
SQL Server 2000
isqlw
SQL Server 2005
sqlwb
SQL Server 2008
ssms
......
|
|
-
|
|
It is possible to use Common Table Expression in a View.
But newbies wonder why the following is not possible
create view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n
Which when created results to the error
Msg 156, Level 15, State 1, Procedure......
|
|
-
|
|
If you use Data Transaformation Services to send data to EXCEL, you may get error about the size of the cell if length of data exceeds 255 characters. This KB from microsoft explains how to solve it
http://support.microsoft.com/default.aspx?scid=kb;en-us;281517......
|
|
-
|
|
When you use variables in update statement and update the columns with the values of variableswhich keep on changing for each row, you can simulate many things that are done using Loop or a Cursor
declare @temp table
(
product_name varchar(100),
Value int,
sequence int
)
insert into @temp......
|
|
-
|
|
Sometimes when you import data from other system, you may want to clean up data by removing unwanted characters
You can use Replace function. Suppose you want to remove the character ^ from the string
declare @str varchar(100)
set @str='test^ string'
select @str=replace(@str,'^','')
Sele......
|
|