ramlicious Blogs by Tina & Prabhu

September 24, 2010

Finding gaps in SQL numeric columns

Filed under: SQL Server — Prabhuram @ 6:39 am

Here is a cool way to find gaps in SQL numeric column. At times you would have wondered how to find if there are any numbers missing in a identity column field. This should help you.

DECLARE @db as VARCHAR(100)
DECLARE @TableName as VARCHAR(100)
DECLARE @ColumnName as VARCHAR(100)
DECLARE @command NVARCHAR(max)
SET @db = 'Northwind'
SET @TableName = 'Orders'
SET @ColumnName = 'OrderId'
SET @command = '
Select
  LastSeqNumber [Last Sequence Number],
  NextSeqNumber [Next Sequence Number],
  [First Number in Gap] = LastSeqNumber + 1,
  [Last Number in Gap] = NextSeqNumber – 1,
  [Total numbers in Gap] = NextSeqNumber – (LastSeqNumber + 1)
from (
  Select LastSeqNumber = (Select isnull(Max(Seq2.' + @ColumnName + '),0) as SeqNumber
  from [' + @db + ']..[' + @TableName + '] Seq2
  where Seq2.' + @ColumnName + ' < Seq1.' + @ColumnName + ')
  , NextSeqNumber = ' + @ColumnName + '
from [' + @db + ']..[' + @TableName + '] Seq1
) as A
  where
    NextSeqNumber – LastSeqNumber > 1
  order by
    LastSeqNumber
'
exec (@command)

September 14, 2010

ROLLUP and CUBE in SQL Server

Filed under: SQL Server — Prabhuram @ 5:18 am

Have you ever thought of these simple clauses called ROLLUP and CUBE when generating reports?

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
Order By
      Title, City 

The above query is a commonly used GROUP BY. For the presentation and reporting purpose the same can be presented as


Using

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH ROLLUP
Order By
      Title, City  

Note the WITH ROLLUP from the above query. If you wanted to distribute even the total for Title distributed by City use the WITH CUBE like:

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH CUBE
Order By
      Title, City 


To remove the annoying NULL and make it meaningful use the GROUPING

SELECT
      CASE WHEN GROUPING(Title) =1 THEN 'All Titles' Else Title End [Title],
      CASE WHEN GROUPING(City) =1 THEN 'All Cities' Else City End [City],
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH CUBE
Order By

      Title, City 

August 12, 2010

Hiding/Masking Credentials (User name and Password) in Batch Files

Filed under: Scripting,SQL Server — Prabhuram @ 9:22 am

I had few ISQL and BCP jobs that are scheduled to run from SQL Servers. Hmm, yes there is a problem here when you have to use the login credentials like this:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql\osql.exe" ^
    -U%username% -P%password% -S%server%

I was thinking of a way to encrypt the user name and password. The fact is that there is no easy way to do this. But there is a work around.

The solution is:

  • Create a configuration file in a remote folder to which only a particular user account has access to. Eventually since the job will be executed as a scheduled job, the user account that runs the original batch task should have access to this remove folder and not all the user.
  • List the user crendentials that needs to be securely kept. Say for example, create an INI file Credentials.INI (53.00 bytes) with required settings:
    • server=mySQLServer
    • username=sa
    • password=P@@ssw0rd
  • Read the parameters from your batch file ReadINI.bat (163.00 bytes) or any file that wishes to use the credentials. Note that the Crendentials.INI can refer to a
    • for /f "tokens=1,2 delims==" %%a in (Credentials.INI) do (
      set %%a=%%b
      )

Now you can simple access the parameters in your INI as %username%, %server%, etc.

Querying Excel Recordset using OPENROWSET

Filed under: SQL Server — Prabhuram @ 6:22 am

We saw how to query a recordset and update to an Excel file in Exporting Recordsets to an Excel using OPENROWSET, and now we will see the simplest example to read a recordset from Excel using OPENROWSET. All you need is this:

SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\NWind.xls;', 'SELECT * FROM [Sheet1$]')
WHERE Country = 'USA'

Exporting Recordsets to an Excel using OPENROWSET

Filed under: SQL Server — Prabhuram @ 2:46 am

To me one easiest way to export data from SQL Server to an excel spreadsheet is by using the OPENROWSET command. All you do is:

  • Create an excel file(Nwind.xls (13.50 kb)), that has the column names of the query result
  • And run the OPENROWSET query to import data

Here is a sample SQL Query that uses Northwind DB, which uses the excel file in E:\:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\NWind.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM [Customers]

The entire script can be automated for daily tasks using a batch file by reusing the same excel file.

August 11, 2010

Linked Server and Data connection string for Sybase

Filed under: SQL Server — Prabhuram @ 8:59 am

Here is the SQL Server Linked Server Properties for adding Sybase Data source

Provider: Select Sybase ASE OLE DB Provider from the list
Product Name: ASEOLEDB
Data source: Leave this blank
Provider string: Server Name=[server name],[port number];User Id=[user id];Password=[password]
Location: Leave this blank
Catalog: [database name]

Similarly here is a sample for adding Sybase Data Connection in Visual Studio using Server Explorer (using ODBC):

Driver={SYBASE ASE ODBC Driver};uid=[user id];pwd=[password];srvr=[server name];na=[server name],[port number];db=[database name]

Click Build button to update the Login information after typing in the connection string.

August 10, 2010

Character-separated List to Row Values

Filed under: SQL Server — Prabhuram @ 2:08 am

We will create CSV to Rows, just as we did Rows to CSV.

CREATE FUNCTION dbo.fn_ConvertCSVToTable ( @Param VARCHAR(8000) )
RETURNS @result TABLE ( Item VARCHAR(100) )
AS
BEGIN
– Declare @Param VARCHAR(8000)
– Declare @result TABLE ( Item VARCHAR(10) )
– Set @Param = '1,2,33,4,5'
Declare @Position integer
Declare @Item varchar(8000)
Set @Position = CharIndex( ',', @Param )
While @Param <> ''
Begin
  If @Position > 0
  Begin
    Set @Item = Substring( @Param, 1, @Position - 1 )
    Set @Param = Substring( @Param, @Position + 1, 8000 )
  End
  Else
  Begin
    Set @Item = @Param
    Set @Param = ''
  End
Insert @result Values ( @Item )
Set @Position = CharIndex( ',', @Param )
End
– Select * From @Result
Return
END
go

Note that this returns a table and is not a scalar-valued function. So this has to be called as:

select * from dbo.fn_ConvertCSVToTable ('1,2,3,4,5,6,7,8,9,0') Numerals

and not:

select dbo.fn_ConvertCSVToTable ('1,2,3,4,5,6,7,8,9,0') Numerals 

and here is an implementation:

declare @db VARCHAR(20)
declare @tables VARCHAR(max)
declare @qry VARCHAR(max)
SET @db='Northwind'
SET @tables='Categories, Customers, Employees, [Order Details]'
SET @qry = 'set nocount on;'+ char(13) + char(10)
select @qry = @qry + 'Print ''Count of rows in ' + Item + ''';' + 'select count(*) Number from ' + @db + '.dbo.' + Item + ';' + char(13) + char(10) from Northwind.dbo.fn_ConvertCSVToTable(@tables)
SET @qry = @qry + 'set nocount off;'+ char(13) + char(10)
print @qry
exec (@qry)

August 9, 2010

Row Values as Character-separated List

Filed under: SQL Server — Prabhuram @ 7:02 am

Ever searched for an easiest way to create a CSV from a row of values. Here it is:

DECLARE @Categories varchar(max)
DECLARE @Character char(1)
Select @Character = ',' –separator
Select @Categories = Coalesce(@Categories + @Character, '') + CategoryName from Northwind.dbo.Categories
SELECT @Categories

August 6, 2010

Running Integration Packages from Command Prompt

Filed under: SQL Server — Prabhuram @ 8:38 am

It is simple to run a complex SQL task from command prompt. All you have to do is create a Integration Services Project using SQL Service Business Intelligence Development Studio and create a package. Say for example an Import/Export Package.

And then schedule or run the dtexec.exe from the command prompt.

DTEXEC.EXE /F "[path to file]\Import UnProc Act.dtsx" >> "Execute Package.log"

 

May 25, 2010

ExecSQL.bat that uses osql utility

Filed under: SQL Server — Prabhuram @ 4:10 am

Here is another easy batch file, which makes use of osql utility to execute a SQL file and to generate an output log file.

Syntax:

\>ExecSQL.bat [input sql file]

Sample:

The above command generates an output file called input.sql.log in the patch where the input file resides. Change the contents of the batch file (ExecSQL.bat) when required:

@echo off
osql    -S (local)    -E     -d master    -n    -i %1    -o %1.log
ECHO Process Completed
pause

I'm writing this just for me so that I can refer to this whenever necessary.

« Newer PostsOlder Posts »

Powered by WordPress