CS

SELECT * FROM XLTEST_SP…Sheet1$

////////////////////////////////////////////////////////

 

EXEC master.dbo.sp_addlinkedserver@server = N’AAA’, @srvproduct=N’SQLSERVWER’, @provider=N’SQLNCLI’, @datasrc=N’USER-PC’

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N’AAA’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’a’,@rmtpassword=’a’

 

////////////////////

 

DECLARE @RC int

DECLARE @server nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider nvarchar(128)

DECLARE @datasrc nvarchar(4000)

DECLARE @location nvarchar(4000)

DECLARE @provstr nvarchar(4000)

DECLARE @catalog nvarchar(128)

— Set parameter values

SET @server = ‘XLTEST_SP’

SET @srvproduct = ‘Excel’

SET @provider = ‘Microsoft.Jet.OLEDB.4.0’

SET @datasrc = ‘c:\tt.xls’

SET @provstr = ‘Excel 8.0′

EXEC @RC = [master].[dbo].[sp_addlinkedserver]@server, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

 

/////////////////////////////

 

EXEC sp_configure’show advanced options’, 1

RECONFIGURE

GO

EXEC sp_configure’ad hoc distributed queries’, 1

RECONFIGURE

GO

 

USE [master]

GO

 

EXEC master . dbo. sp_MSset_oledb_propN’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

GO

 

EXEC master . dbo. sp_MSset_oledb_propN’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

GO

 

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xlsx;HDR=YES’,

‘SELECT * FROM [Sheet1$]’)

////////////////////////////////

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xlsx;HDR=YES’,

‘SELECT * FROM [Sheet1$]’)

///////////////////

EXEC sp_configure’show advanced options’, 1

RECONFIGURE

GO

EXEC sp_configure’ad hoc distributed queries’, 1

RECONFIGURE

GO

 

USE [master]

GO

 

EXEC master . dbo. sp_MSset_oledb_propN’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

GO

 

EXEC master . dbo. sp_MSset_oledb_propN’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

GO

////////////////////////////////////////////////////

The system stored procedure sp_addlinkedserver is used in order to link a server from the server in which you are executing the command. In order to connect to a remote SQL Server, you basically only need 3 parameters.

EXEC sp_addlinkedserver
@server=N’HOGEN-PC’,             — Remote Computer Name
@srvproduct=N”,                 — Not Needed
@provider=N’SQLNCLI’,            — SQL Server Driver
@datasrc=N’HOGEN-PCSQLEXPRESS’;  — Server Name And Instance

If the remote SQL Server does not have an instance name, then the @datasrc parameter nee only contain the remote server name and not the instance.

EXEC sp_configure

/////////////////////////////////////////////////////////

C:\AccessDatabaseEngine_x64.exe /passive

http://www.techrepublic.com/blog/the-enterprise-cloud/how-do-i-query-foreign-data-using-sql-servers-linked-servers/

////////////////////

 

/****** Script for SelectTopNRows command from SSMS  ******/

 

;with tst as

(

SELECT DISTINCT [name], [aid]  ,

ROW_NUMBER() over( partition by name order by name) as rnk

FROM [tst].[dbo].[A]

)

select * from tst

where rnk=1

……………………………………………………………….

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt20.xls;HDR=yes’,

‘SELECT *

FROM [Sheet1$D10:e14]’)

 

…………………………………………………………………………………………………………………………….

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xlsx;HDR=YES’,

‘SELECT *

FROM [Sheet1$a2:a2]’)

////////////////////////////////////////////////////////

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xls;HDR=YES’,

‘SELECT *

FROM [Sheet1$a5:b]’)

////////////////////////////////////////////////////////

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xls;HDR=YES’,

‘SELECT *

FROM [Sheet1$] where id<3’)

////////////////////////////////////////////////////////

select * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’,

‘Excel 12.0;Database=C:\tt.xls;HDR=YES’,

‘SELECT *

FROM [Sheet1$]’)

////////////////////////////////////////////////////////

sp_configure ‘Ad Hoc Distributed Queries’,1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @ExecutionStatus INT
DECLARE @JobName VARCHAR(100) = ‘%yourjobnamehere%’

SELECT name, current_execution_status, job_id
INTO #Jobs
FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’,
‘set fmtonly off exec msdb.dbo.sp_help_job’)

SELECT @ExecutionStatus = current_execution_status
FROM #Jobs
WHERE job_id = (SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
WHERE name LIKE @JobName)

IF @ExecutionStatus != 1
BEGIN
RAISERROR (‘Job Is Not Running’,
16,
1
)
END

////////////////////////////////////////////////////////

sp_who is a system stored procedure designed to return back information regarding the current sessions in the database. These sessions are commonly referred to as SPIDS (Server Process ID’s). While sp_who is sometimes used, it’s sister procedure sp_who2 is used far more often. This is because sp_who2 returns back more information than sp_who.

Let’s look at a comparison. sp_who, and sp_who2 both have one optional parameter which is the session id. If you do not pass a session ID, then all sessions are returned. In order to execute, simply copy the text below.

— execute sp_who
EXEC sp_who

— execute sp_who2
EXEC sp_who2

 

Kill All Connections on a Database

« Conditional Where Clause

Creating a Rules Engine »

 

This seems to be the easiest way to kill all connections (sessions) on a SQL Server database:

ALTER DATABASE database_name

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

After executing the above, it will put the database in single user mode. So you then need to remove single user mode using:

EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’

 

////////////////////////////////////////////////////////

completion of the operation or the server is not responding.

« Find Dependent Objects

View Active Connections »

 

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes.

EXEC SP_CONFIGURE ‘remote query timeout’, 1800
reconfigure
EXEC sp_configure

EXEC SP_CONFIGURE ‘show advanced options’, 1
reconfigure
EXEC sp_configure

EXEC SP_CONFIGURE ‘remote query timeout’, 1800
reconfigure
EXEC sp_configure

After making this change, make sure to close the window and create a new connection in order to inherit the new query timeout settings.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: