|
|
-
|
|
People use Dynamic SQL for various purposes One of the disadvantages of it is SQL injection that can attack your database by doing the things that you didn't want to do Consider a case where usage of Dynamic SQL in unavoidable You have a table that records the daily activities of the employees Your ......
|
|
-
|
|
Here is the script that takes backup of all databases excluding system databases
The file name would be databasename_yyyymmdd where yyyymmdd is the formatted value of current date
create procedure Backup_all_databases
as
Declare @sql varchar(8000)
set @sql=''
select @sql=@sql+
'
Backup dat......
|
|
-
|
|
In this post Extract only numbers from a String, I described a method of extrating numbers from a string using split and concatenate technique
In this post Regular expression, I described a method to extract whole numbers if a specific digit is found Consider that you have a situation where you ne......
|
|
-
|
|
Can we use the temporary table created in one procedure from another procedure?
The answer depends on how you call the procedure Take this example Create a temporary table in a procedure
create procedure proc_1
as
create table #t(i int)
insert into #t select 10
select i from #t
Now create......
|
|
-
|
|
BULK INSERT is used to insert data to a table from a text file. Only problem with it is that it is not possible to specify the columns that you want data to be imported. For example the table has 4 columns whereas there are data for 2 columns in the text file. Consider the following dataSource data ......
|
|
-
|
|
Often I see people asking questions in the forums about finding the database name where the particular procedure exists You can use many methods to get a solution (cursor, undocumented procedure,dynamic sql,etc) The simplest are 1 Use Undocumented procedure
EXEC sp_msforeachdb
'if exists(sele......
|
|
-
|
|
When one of posters asked a question about this, someone posted the following reply
UPDATE msdb.dbo.sysjobs SET enabled = 0
As sysjobs is a system and updating it is generally not advised, the following can be used instead
declare @sql varchar(max)
set @sql=''
select
@sql=@sql+' ......
|
|
-
|
|
Pinal Dave in his post asked about the simple ways to convert hexadecimal values to decimal values.
Here are some methods:
87235 is an integer value whose hexadecimal is 0x000154C3.
In this case make use of Implicit conversion.
declare @hexa varbinary(10)
set @hexa=0x000154C3
select
@hex......
|
|
-
|
|
Some people suggest usage of information_schema.routines View to view the definition of the procedure The problem is the datatype of that column is nvarchar(4000) and there is a chance that the definition gets truncated. So, the following wont give you full text of the definition
select routine_......
|
|
-
|
|
My co-worker had a task of finding minimum alternate week day as of today for a given date and day. Consider these data
Date : 2009-12-22
day : 5 (it denotes Thursday)
It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting ......
|
|