|
|
-
|
|
Sometimes when you want to migrate from MS Accesss to SQL Server, you need to rewrite the queries compatible to SQL Server. As you know the basic systax is same but the functions used in Access should be replaced with their equivalent in SQL Server.
Here are some Access functions and their equival......
|
|
-
|
|
A Leap year contains an extra day so there are 366 days. There are many ways to find out whether the year is a Leap year. If the value of is_leap_year is 1 it is a Leap year otherwise it is not a Leap year.
Method 1 : General method
declare @year int
set @year=2000
select
case
when @y......
|
|
-
|
|
In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code
set nocount on
select
space(17-len(replicate(char(37),no)))+ replicate(char(37),no*2-1)
from
(
select top 10 row_number() over (order by name) as no from sysobjec......
|
|
-
|
|
Pinal Dave in his blog posted this about using a column name and column number in the Order by clause. He made some valid points on why the second method should not be used. However, there is a case in which second method can be useful
I had already blogged about Dynamic PIVOT in which you can pas......
|
|
-
|
|
In the SQL Server forums, I see questions complaining that BULK INSERT is not taking care of check constraints. There are also some other questions related to BULK INSERT. Here I summarise them and explain
1 Enabling Check contraint - Use the option check_constraints
create table test
(id int,
......
|
|
-
|
|
I have already blogged about the usage of BULK INSERT in this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
You can use BULK INSERT command to import data from a file to a table. However if the field seperator is a comma and ......
|
|
-
|
|
OPENROWSET function can be used to query on the database exists in different server or query on the stored procedure. Consider that the system procedure sp_who2 gives informations about the current users, sessions, etc.
EXEC sp_who2
But note that the resultset has two columns with the same name......
|
|
-
|
|
I have already posted a blogpost on how to query directly on a EXCEL file using a query. Here is the link http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx But for EXCEL, verions starting from 2007, different Provider should be used. Here is the provider whi......
|
|
-
|
|
I have already posted blog posts regarding the proper usage of the alias name
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/14/should-alias-names-be-preceded-by-as.aspx http://beyondrelational.com/blogs/madhivanan/archive/2008/09/09/should-alias-names-be-preceded-by-as-part-2.aspx T......
|
|
-
|
|
Suppose you have a set of codes and when a particular condition is true, you want to terminate or switch to another set of code, you can use Break commnad. I see sometimes people use return commnad for this. But there is a significant difference between these two. Consider this example
Usage of BR......
|
|