http://www.sqlservergeeks.com/articles/sql-server-bi/32/installation-guide-for-sql-server-2008-r2
Monday, August 15, 2011
Thursday, August 11, 2011
SSIS Packages deployment
http://decipherinfosys.wordpress.com/2008/09/16/deploying-ssis-packages-in-sql-server-2005/
There are two options available for us to deploy the SSIS Packages.
a) File System Deployment:
As the name suggests, this option allows us to deploy the SSIS Project to the File System i.e. a physical location on the hard disk on the target server. If this option is chosen, we have to back up the SSIS Packages manually every night for any disaster recovery.
b) Sql Server Deployment:
This option allows us to deploy the SSIS project to the SQL Server i.e. on the MSDB database. Since the msdb is usually backed up every night, this method of deployment saves us some extra work of backing up the SSIS Packages manually if deployed using the file system.
SSIS Packages execution options:
Syntax: dtexec /option [value] [/option [value]]...
1. Command prompt
2. Using the xp_cmdshell extended stored procedure by using the dtexec command line utility
3.Using BIDS
There are two options available for us to deploy the SSIS Packages.
a) File System Deployment:
As the name suggests, this option allows us to deploy the SSIS Project to the File System i.e. a physical location on the hard disk on the target server. If this option is chosen, we have to back up the SSIS Packages manually every night for any disaster recovery.
b) Sql Server Deployment:
This option allows us to deploy the SSIS project to the SQL Server i.e. on the MSDB database. Since the msdb is usually backed up every night, this method of deployment saves us some extra work of backing up the SSIS Packages manually if deployed using the file system.
SSIS Packages execution options:
Syntax: dtexec /option [value] [/option [value]]...
1. Command prompt
dtexec /f "c:\pkgOne.dtsx" --File system
dtexec /sq pkgOne /ser productionServer -- SQL Server
2. Using the xp_cmdshell extended stored procedure by using the dtexec command line utility
EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'
3.Using BIDS
Could not save the package "D:\Test\Deployment1\Package2.dtsx" to SQL Server "(local)".
Error while deplying the SSIS Packag: Could not save the package "D:\Test\Deployment1\Package2.dtsx" to SQL Server "(local)".
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
Solutuon: http://msdn.microsoft.com/en-us/library/ms137789.aspx
Modify the MsDtsSrvr.ini.xml, file (ServerName\InstanceName )
,which is located in the folder, %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
Solutuon: http://msdn.microsoft.com/en-us/library/ms137789.aspx
Modify the MsDtsSrvr.ini.xml, file (
,which is located in the folder, %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.
Tuesday, June 21, 2011
SQL Server Performance Tuning
http://www.brentozar.com/sql-server-training-videos/
http://vimeo.com/16316098
http://vimeo.com/16316098
Saturday, May 14, 2011
xp_cmdshell exec permission for non-sysadmin user
(1) Enable the xp_cmdshell procedure
Enable xp_cmdshell by using sp_configure or "Surface Area Configuration"
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
(2) Create a login 'Domain\TestUser' for the non-sysadmin user that has public access to the master database
(3) Grant EXEC permission on the xp_cmdshell stored procedure
GRANT EXECUTE ON xp_cmdshell TO [Domain\TestUser]
(4) Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account
EXEC sp_xp_cmdshell_proxy_account '[Domain\TestUser]','pwd'
Note: pwd means windows password for [Domain\TestUser] account id on the box.
(5).Grant control server permission to user
USE master;
GRANT CONTROL SERVER TO [Domain\TestUser]
GO
Enable xp_cmdshell by using sp_configure or "Surface Area Configuration"
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
(2) Create a login 'Domain\TestUser' for the non-sysadmin user that has public access to the master database
(3) Grant EXEC permission on the xp_cmdshell stored procedure
GRANT EXECUTE ON xp_cmdshell TO [Domain\TestUser]
(4) Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account
EXEC sp_xp_cmdshell_proxy_account '[Domain\TestUser]','pwd'
Note: pwd means windows password for [Domain\TestUser] account id on the box.
(5).Grant control server permission to user
USE master;
GRANT CONTROL SERVER TO [Domain\TestUser]
GO
INSERT Data from XL Sheet to SQL Server Database Table
--1.XL sheet to Table
Insert into Table2 Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
--2.Select data from xl and display in SQL Server Management
Select * from openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;database=D:\Test.xls',
'select * from [Sheet1$]')
--3.Insert data from SQL Table to XL sheet.
Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Test2.xls;',
'SELECT * FROM [Sheet1$]') select * from Table2
Insert into Table2 Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
--2.Select data from xl and display in SQL Server Management
Select * from openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;database=D:\Test.xls',
'select * from [Sheet1$]')
--3.Insert data from SQL Table to XL sheet.
Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Test2.xls;',
'SELECT * FROM [Sheet1$]') select * from Table2
Subscribe to:
Posts (Atom)