Set NoCount On
Declare @CurrID int,@ExistValue int, @MaxID int, @SQL nvarchar(1000)
Declare @TCPPorts Table (PortType nvarchar(180), Port int)
Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,
InstName nvarchar(180),
Folder nvarchar(50),
StaticPort int null,
DynamicPort int null,
Platform int null);
Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))
Declare @Platform varchar(100)
Insert into @Plat exec xp_msver platform
select @Platform = (select 1 from @plat where charactervalue like '%86%')
If @Platform is NULL
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances set Platform=64
End
else
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform=32
End
Declare @Keyexist Table (Keyexist int)
Insert into @Keyexist
Exec xp_regread'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform =32 where Platform is NULL
Select @MaxID = MAX(InstanceID), @CurrID = 1
From @SQLInstances
While @CurrID <= @MaxID
Begin
Delete From @TCPPorts
Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @SQL
Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @SQL
Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @SQL
Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @SQL
Update SI
Set StaticPort = P.Port,
DynamicPort = DP.Port
From @SQLInstances SI
Inner Join @TCPPorts DP On DP.PortType = 'TCPDynamicPorts'
Inner Join @TCPPorts P On P.PortType = 'TCPPort'
Where InstanceID = @CurrID;
Set @CurrID = @CurrID + 1
End
Select serverproperty('ComputerNamePhysicalNetBIOS') as ServerName, InstName, StaticPort, DynamicPort,Platform
From @SQLInstances
Set NoCount Off
Thursday, December 4, 2014
How to find all instances on SQL SERVER
Friday, November 21, 2014
Monday, November 17, 2014
System Admin but no rights to log into Sql server
After sql server 2008, MS removed system Admin to the access list of sql server automatically. If you did not add to the installation step, you will have trouble. Blog below will help you on this issue
http://element533.blogspot.ca/2010/01/breaking-into-sql-server-using-local.html
Tuesday, November 4, 2014
Exception handling and nested transactions
This template below is very useful if you decide to add Transactions into your Store Procedure. Your SP could be called by other scripts and rollback may be issued inside the script. You need to use Safepoint if in this case. However, if there is no transaction exists, using savepoint will cause errors.
Another good article abuot Transaction Savepoints; http://www.blackwasp.co.uk/SQLSavepoints.aspx
Template for error handling and nested transactions in Store Procedure
Original URL: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
go@@Trancount and XACT_STATE()
Recently, I’ve encountered code that includes both @@Trancount and XACT_STATE().
XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.
XACT_STATE returns the following three values
-
1: The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
-
0: There is no active user transaction for the current request.
-
-1: The current request has an active user transaction, but an error has occurred that has
caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.
So before commit or rollback always test XACT_STATE for 0, 1, or -1.
- If 1, the transaction is committable.
- If -1, the transaction is uncommittable and should be rolled back.
- If 0, there is no transaction and a commit or rollback operation will generate an error.
Below is the difference between them
both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.
@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.
XACT_STATE cannot be used to determine whether there are nested transactions.
URL: http://www.advancesharp.com/blog/1017/sql-transaction-status-and-xact-state
Monday, November 3, 2014
Wednesday, April 30, 2014
Generate table to stored Procedure cross references
By Marcus Dallasandro, 2014/04/25
Lists Tables and Store Procedure references. Runs a bit slow but appears to be accurate. Just paste this script into SSMS for the desired database.
WITH TableList_CTE (TableName)
AS
(
SELECT TABLE_NAME + CHAR(32) as TableName
FROM INFORMATION_SCHEMA.TABLES T
WHERE t.TABLE_TYPE='BASE TABLE'
)
SELECT TableName,OBJECT_NAME(object_id) as StoredProcedure
FROM sys.sql_modules S
Join TableList_CTE on 1=1
WHERE objectproperty(object_id,'IsProcedure') = 1
AND CHARINDEX(TableName,Definition,0)<>0
Order by TableName
Friday, March 28, 2014
Set up OlapQueryLog for aggregaton
following this log http://technet.microsoft.com/en-us/library/cc917676.aspx
but one key point , when set up the connection string, the connection has to be sql OLE DB instead the defaul sql native client..
that’s the key!
Thursday, February 20, 2014
SSIS tips: run packages on BIDS ok, but failed on SQL Jobs
This is a rather tricky problem.
1. I’ve opened a package via BIDS on production SSIS server, run it over there and data did get populated
2. I tried to run it through a SQL job but it failed with error message meta needs to be examined.
I’ve checked it thoroughly and found out the column I’ve added in the dev env is not the same on production.
On dev. it’s thirdpartyAppID while on production it’s thirdpartyappid. The difference is on the capital a.
I think if pkg runs on BIDS, this type of difference was ignored(?) while on sql server agent, it gets picked up.
Below is the explanation from the forum
I don't think this a bug, because (I know) SSIS is a case-sensitive tool.
I can see how this could be very frustrating when you are using case insensitive SQL Servers, but there are plenty of areas where case sensitivity is an issue across application boundaries, one of the problems that keeps us all employed!
This does of course explain the metadata error. Can you get the column names in sync?
Tuesday, February 18, 2014
SSIS expression reference
From link http://bisherryli.com/2011/04/18/ssis-96-everybody-needs-integration-services-expression-reference/
In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.
In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.
If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.
OK, here is the official link to the Integration Services Expression Reference on MSDN.
So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.
Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.
Did you notice that it says expressions are complex frequently?
There are 4 topics on the MSDN reference site.
Topic
Description
Integration Services Expression Concepts
Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.
Describes the operators that the expression evaluator provides.
String Functions and Other Functions (SSIS Expression)
Describes the functions that the expression evaluator provides.
Advanced Integration Services Expressions
Provides expressions that use multiple operators and functions.
The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.
So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.
Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.
Here is a “simple” expression ( simple only after I remembered AND needed to be &&):
@varHolidayInd == "N" && @varWeekendInd == "N"
Wednesday, January 29, 2014
How to SFTP file via SSIS
We are using SSIS 2008R2 and the default FTP component does not support Secure FTP. A workaround is to utilize the Winscp program via Execute Process Task
utility and prepare a Winscp script for this purpose. Link below has all the details
Tuesday, January 28, 2014
How to output table or query to a flat file
Recently, I have a request to output a table/query to a flat file and gets Sftp to an external server
First step is to prepare the .csv file with column names.
It appears that we have two options here using xp_cmdshell
1. BCP : need to add header.csv to the contents.csv
Sample code below:
BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='my_table_name'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name
BCP my_db_name.dbo.my_table_name out TableDataWithoutHeaders.csv -c -t, -T -Smy_server_name
copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv
del HeadersOnly.csv
del TableDataWithoutHeaders.csv
2. SQLcmd it will include headers and add space paddings . Also the file extension is limited compared to BCP
Good article to share
Friday, January 24, 2014
Variables and columnName inside Dynamic Sql
IN SSIS package,
In expression, if you want to show the value of the variable, use double quote “, if you want to put the value under the single quote , use +’” @val +”’
FOR Dynamic TSQL
if you need to put quotation for the string column in the String concatenation, use four quotations ‘