SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Reporting Subscription: Quick Links

http://www.isolutionspartners.com/Blog/tabid/65/EntryId/35/How-to-configure-SSRS-Report-Subscriptions-to-use-Gmail-for-GP.aspx

http://www.databasejournal.com/features/mssql/report-subscription-in-ssrs-2008-r2.html

Leave a comment »

SQL Server Tools

SQL Server Stored Procedures Comparer:

http://www.codeproject.com/KB/database/StoredProceduresComparer/spcomparer_demo.zip

Compare Databases Tool: http://dbcomparer.com/

Leave a comment »

SSIS Package EXCEL Connection Error: Solution

Issue Description:

Package was running fine

Error:

[Connection manager “Dest File”] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Solution:

You have an Excel connection. The package will need to run in 32 bit mode. To enable 32 bit mode, right click the project in the Solution Explorer and go to properties. Under Debugging, set the property Run64BitRunTime to False.

Leave a comment »

linked Server Query: Fetching data from table with column of datatype “XML”

ERROR: Xml data type is not supported in distributed queries. Remote object ‘server_name.database_name.dbo.table_name’ has xml column(s).

Solution:

SELECT * FROM

(
SELECT * FROM OPENQUERY([server_name],'SELECT column_1, column_2,CAST (column_XML as varchar(max)) Converted_column_XML
 FROM
 database_name.[dbo].table_name')

)AS X
1 Comment »

Database: Create User with “Create Proc Permission”

USE MASTER
CREATE LOGIN da WITH PASSWORD = '12345'

USE yourdatabasename
GO
CREATE USER da FOR LOGIN da

CREATE ROLE yourrolename

EXEC sp_addrolemember 'db_datareader',yourrolename
EXEC sp_addrolemember 'db_datawriter', yourrolename
EXEC sp_addrolemember @RoleName = 'yourrolename', @MemberName = 'da'

GRANT EXECUTE TO da
GRANT CREATE PROCEDURE TO da

GRANT ALTER ON SCHEMA::dbo TO yourrolename
Leave a comment »

Must read: Article on Triggers

Triggers: http://vivekjohari.blogspot.in/2011/11/sql-triggers-introduction.html

addons topics:

database models:

http://en.wikipedia.org/wiki/Database_model

Query Optimization tips:

http://hungred.com/useful-information/ways-optimize-sql-queries/

 

Leave a comment »

Sql Server Mirroring

Prerequisites:
A) SQL Servers on Principal and Mirror server shopud be started under account with the same name (xyzMachineUser)
B) You should be connected to SQL Server under sqlmirroring SQL Account (With same user name and password: lets say we have account: sqlmirroring )

Steps:
1) Take Full Backup from Principal Database (database must be in Full recovery model)
2) Take Transaction Log backup from Primary Database
3) Restore Full backup on mirror server with NORECOVERY option on mirror server(with user sqlmirroring)
4) Restore Transaction Log backup with NORECOVERY option on mirror server
5) Right click on database you want to start mirroring -> Properties -> Mirroring..
6) Click ”Configure Security” -> Choose not to Configure Witness server -> Connect to Mirror Server under sqlmirroring user (other steps – click next) (Enable encryption – remove checkbox mark)
7) In dialog window choose “Do not start mirroring”
8) Check radiobutton “High Performance”
9) In “Server network addresses” replace machine names to ip addresses of servers
10) Click “Start Mirroring”

If principal server goes down, you should restore mirror database using this commands:
ALTER DATABASE database_name SET PARTNER OFF
GO
RESTORE DATABASE database_name WITH RECOVERY
GO
And connect your front end with this database.
Your website is UP again.

Leave a comment »

Explicitly insert data into identity column : Sql Server

To insert data into Identity Column
Set identity_insert table_name ON
Explicitly specify the column names of the table in which data is to be inserted
Set identity_insert table_name OFF

set identity_insert table_name ON
insert into table_name(identity_column_name,second_column_name) values
(21, 'text 1'),
(23, 'text 2'),
(24, 'text 3')
set identity_insert table_name OFF
Leave a comment »

Sql Server Service not starting up. Error: 17058

Server Error: 17058, Severity: 16, State: 1.
Error:
Server initerrlog: Could not open error log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG’. Operating system error = 5(Access is denied.).
Issue: Created a new user and restarted service with the new user.
It started giving the error mentioned above.
Solution: Moved the file “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG” to a different location and the service started.
Hurray!!
Hope it helps 🙂

NOTE: Ideally on Sql Server restart a new error file is created.

If existing file is named as “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG”

New file will be named as “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG.1”

Need to dig more.. right?

Wednesday#90210

Leave a comment »