[ B1zT@Lk ] - Microsoft SQL Server

Microsoft SQL Server

all about T-SQL

WCF-SQL Adapter MSDTC / Identity Insert Issue: I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we've tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues. To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I'm configuring the mySAP adapter, I found out that license has expired,...; posted @ Friday, July 03, 2009 4:58 PM | Feedback (0)

SSIS - “OLE DB provider ‘STREAM’ for linked server ‘(null)’ returned invalid data for column ‘[!BulkInsert].column_name’.": I've been trying all day to solve the problem with export of data using SSIS and keep encountering this error: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].{column_name}'. It seems that data in that column is invalid so I've tried to add validation to my source OLEDB source. I've created a dataflow to filter all records that have bad data and redirect it to a file. To do this in the query of my source OLEDB I've add the following condition: WHERE isnumeric(convert(varchar(250), coalesce([{column_name}],0))) = 0 then put the results in the file IDs...; posted @ Friday, March 06, 2009 6:24 PM | Feedback (2)

SQL 2005 Configuring SQL Job to send email notification if it failed.: This are the simple steps to enable it. 1) Run SQL Server Surface Area Configuration - > Surface Area Configuration for features -> Select Database Mail - > Check Enable Database mail stored procedures. 2) Open SQL Management Studio - > Management - > Database mail - > Configure SMTP Settings. 3) Go to SQL Server Agent - > Operators - > Create New Operator 4) Go to SQL Job - > In the Notifications, Check email and select the Operator. You can either select the option to send notification if job fails / succeeds/ completed. Lastly, don't forget to restart the SQL Agent.; posted @ Wednesday, March 04, 2009 11:24 PM | Feedback (3)

Pagination using Stored Procedure: There are different approach on how to do pagination, but the I personally uses the dynamic sql pagination using subquery, and this I think is very efficient when doing a pagination in which all the fields to be filtered and sorted are all in the same table: EXEC ( 'SELECT ' + @sqlQuery + ' WHERE [ID] IN ' + ' (SELECT TOP ' + @strPageSize + ' [ID] FROM ' + @strBaseTable + @strFilter + @Connector + ' [ID] NOT IN ' + ' (SELECT TOP ' + @strSkippedRoW + ' [ID] FROM ' + @strBaseTable + @strFilter...; posted @ Thursday, June 23, 2005 1:18 PM

Naming Convention in Tables, Stored Procedures, Views, and UDF's: I would like to share my standard naming practice in MSSQL. This naming convention wouldn't be only good for the eyes but its purpose mainly is to know what the SP,View, or UDF does by just looking its name, also to easily access all related objects. Tables: Use a noun, instead of using an underscore just use Pascal Case Naming just like in .NET. 1. CustomerProfile - table for Customer's Profile 2. Customer - table for Customer 3. Account - table for accounts Stored Procedure. In SP names I use noun + the purpose of the SP. 1. CustomerProfileInsProc - sp for inserting operations in CustomerProfile Table 2....; posted @ Tuesday, May 31, 2005 10:27 AM | Feedback (4)

Article Categories

Archives

Post Categories