Linked servers and an Identity property

Someone in sqlserver.programming group asked nice question about getting an IDENTITY from the linked server. I came up with the following solution. I assume that there is a table on the linked server that has one column defined as indentity property.

CREATE FUNCTION dbo.fn_get_remoteidentity()
RETURNS INT
AS
BEGIN
RETURN(
SELECT row_id
FROM OPENQUERY(
[put here your server name],
'SET NOCOUNT ON;
INSERT INTO dbname..usertable DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS row_id;') AS Der)
END
Another question was how to insert an INDENTITY property in the table on remote(linked) server?
Well in that case you will need to build dynamic SQL along with SET IDENTITY_INSERT
command.

DECLARE @sql NVARCHAR(400)
SELECT @sql = '
CREATE TABLE test(id INT IDENTITY(1,1))
SET IDENTITY_INSERT test ON
INSERT test DEFAULT VALUES
SET IDENTITY_INSERT test OFF
SELECT id FROM test'
--Usage
EXEC [LinkedServer].master.dbo.sp_executesql @sql

Global stored procedure

I have my doubts that many of us have been used this system stored procedure(sp_MS_marksystemobject) to resolve the problems. But it exists and gives you an ability to return the data in the scope of selected database. Well, across number of databases having one table with the same structure and being in the scope of desired database you'll return the data (by using the following stored procedure) from the table located in desired database.To make the stored procedure to recognize the 'desired' data we use sp_MS_marksystemobject system stored procedure. BTW, it works as well as on SQL Server 2000 and on SQL Server 2005(SP2).

USE master
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('master')
go
CREATE PROC sp_test AS
SELECT * FROM test
GO
USE northwind
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('northwind')
USE pubs
CREATE TABLE test(c1 VARCHAR(50))
INSERT test VALUES('pubs')
USE pubs
EXEC sp_test --returns 'master'
USE master
EXEC sp_MS_marksystemobject sp_test
USE pubs
EXEC sp_test --returns 'pubs'
USE northwind
EXEC sp_test --returns 'northwind'

Find specific data type of the column

Recently i was told to return tables which have FLOAT datatype defined on columns.The version of the product was SQL Server 2000 ,however it will work on SQL Server 2005 as well, so take a look at very very simple script.

SELECT columns.TABLE_NAME ,COLUMN_NAME
FROM information_schema.columns columns
JOIN information_schema.tables tables
ON tables.table_name = columns.table_name
WHERE tables.table_type = 'base table'
AND columns.data_type = 'float'
ORDER BY columns.table_name, columns.column_name

Can we look into LOG file?

In SQL Server 2005 with fn_dblog function you can get more info about what's happening in SQL Server. MS has added a new data like 'partitionid' column for example.

Let us do some testing

USE Demo
--create a table for testing
CREATE TABLE dbo.Test1 (c INT)
--populate the table with data

WITH numbers AS
(SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM numbers WHERE Num <10)
INSERT INTO dbo.Test1 SELECT Num FROM numbers OPTION(Maxrecursion 10)

--delete the data
DELETE FROM dbo.Test1

In order to identify how many 'DELETE' operation and when does it start? Run the followinq query.

SELECT [Transaction Name],[begin time FROM ::fn_dblog( NULL,NULL)WHERE [Transaction Name] ='DELETE'

Ok, the next step I'd like to know how long does DELETION take? So we have [end time column] for this purpose. We need to write the below query which gets little complicated.

WITH cte_delete
AS
(
SELECT [Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL
) ,cte_d
AS
(
SELECT [Transaction Name],[begin time],(SELECT MIN([end time]) FROM cte_delete C
WHERE cte_delete.[begin time]<=C.[end time])AS [end time]
FROM cte_delete WHERE [Transaction Name] ='DELETE' OR
[Transaction Name] IS NULL
)
SELECT * FROM cte_d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL

Also take a look at 'Operation' column.

SELECT Operation,AllocUnitName,[Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)
WHERE Operation ='LOP_DELETE_ROWS' OR [Transaction Name]='DELETE'

To identify which tables are being hit by DELETE operation.

SELECT AllocUnitName, COUNT([Current LSN])
FROM ::fn_dblog(NULL,NULL )
WHERE Operation = N'LOP_DELETE_ROWS'
GROUP BY AllocUnitName

String manipulation (extracting)

I probably should create some group of articles for string manuipulation.At this time I'd like to show you an example to extract portion of text prior/after to some charcter/symbol.An idea is simple to iterate through string and return the text prior (in my example) to 'N' appearance of specified symbol.


CREATE FUNCTION dbo.udf_Extract(
@target varchar(8000),
@string varchar(8000),
@i int
) RETURNS varchar(8000) AS BEGIN
DECLARE @pos int
SET @pos = 0
WHILE @i > 0 BEGIN
SET @pos = CHARINDEX(@target,@string,@pos+1)
SET @i = @i - 1
IF @pos = 0 RETURN '0'
END
RETURN SUBSTRING (@string,1,@pos-1)
END
GO

--Usage
SELECT dbo.udf_Extract('@','hjdhjdhj@hjjsd@hjdfhdfj@jjj,3)