Debug SQL Network Interfaces, error: 26 ….

Posted: April 29, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
If you have already set your remote connection, you still have this error. Please check this post:
http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
 

Max value from one column of a goup with other columns

Posted: April 28, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
declare @testTable table
(main_id int,  Group_id int, [type_id] int,  
info1  char(1), info2 char(1),   lenghts int)
INSERT INTO @testTable
SELECT 1,3,1,‘1’,‘1’,1
UNION ALL SELECT 1,3,2,‘2’,‘2’,3
UNION ALL SELECT 1,3,3,‘2’,‘2’,2
UNION ALL SELECT 1,4,1,‘s’,‘s’,3
UNION ALL SELECT 1,4,2,‘2’,‘2’,5

–SELECT * FROM @testTable

SELECT t.main_id,t.Group_id,t.[type_id],
t.info1,t.info2,t.lenghts FROM(
    SELECT main_id, Group_id, MAX(type_id) AS Maxtype_id
    FROM @testTable
    GROUP BY main_id, Group_id) d
    JOIN @testTable t ON
d.main_id =t.main_id AND d.Group_id =t.Group_id
    AND d.Maxtype_id =t.[type_id]
ORDER BY t.main_id, t.Group_id

–Or SQL Server 2005 and 2008
SELECT main_id, Group_id, [type_id],
info1, info2, lenghts FROM
(SELECT main_id, Group_id, [type_id], info1, info2, lenghts
,ROW_NUMBER() OVER(Partition by main_id, Group_id ORDER BY [type_id] DESC) rn
FROM @TestTable) t
WHERE rn=1

Schedule a Job for an SSIS package

Posted: April 27, 2009 | Author: | Filed under: SQL SSIS | Leave a comment

After designing and testing your SSIS package at your development machine, you want to deploy it to your SQL Server machine to run as a job. You may have issues with the security settings associated with the job. Here is the required steps for how to set up a job to run the SSIS package through File System deployment.

1. Create a login account for the window account of your server your SQL Server is running at;

2.Create a user account for the user you created for the window account in database msdb;

3.Assign the user in msdb to SQLAgentUserRole, SQLAgentReaderRole,  SQLAgentOperatorRole ;

4.Create a new credential for the window account (identity);

5.Under SQL Server Agent node(Proxies): Create a proxy account with the credential we created early and assign the proxy to SSIS Package Subsystem;You may need to assign the proxy account to the login under Principals selection with Add;

6.When you as window login are creating job under SQL Server Agent, you need to use SSIS Package as Type and Run as the proxy we created during your Step creation; Assign a schedule as you would with other jobs.

7. you can test run your job manually and wait for your schedule job to run if there is no other issue(You had better test your package in BIDS comes with SQL Server 2005).

Refer: Charles Kangai http://www.mydatabasesupport.com/forums/sqlserver-dts/182974-ssis-schedule-jobs.html

Hongwei Li: http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Run query in a script file from SQLCMD command line tool

Posted: April 16, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
Here is a link to Microsoft for how to use this powerful tool:
 
Running Transact-SQL Script Files by Using sqlcmd
http://technet.microsoft.com/en-us/library/ms170572(SQL.90).aspx
 
Command window:
type: sqlcmd -S myServer\instanceName -i C:\myScript.sql

Issue with restore a bak database file: “Exclusive Access could not be obtained because the database is in use.”

Posted: April 16, 2009 | Author: | Filed under: SQL Server | Leave a comment
When we try to restore a database from a backup file, we may run into this error message:
 "Exclusive Access could not be obtained because the database is in use." We can fix this error to restore our database by changing the database to single_user mode to disconnect all connections and try to run restore again and finally to reset the database back to multiple user mode to finish up the process.
 
1. USE [master]
ALTER DATABASE [mydb]
SET single_user with rollback immediate;
 

2. Restore your database again.

3.USE [master]
ALTER DATABASE [mydb]
SET multi_user;

BCP utility from Stored Procedure or export result for a Stored Procedure

Posted: April 15, 2009 | Author: | Filed under: SQL Server | Leave a comment
Export Stored Procedure result with bcp:
 

declare @sql varchar(8000)

select

@sql = ‘bcp "exec test_for_forum.dbo.testCard" queryout c:\test\mytab2.txt -c -t, -T -S’+ @@servername

exec

master..xp_cmdshell @sql

Use bcp in a Stored Procedure:

ALTER

PROCEDURE [dbo].[testBcp]

AS

BEGIN

SET NOCOUNT ON;

declare @sql varchar(8000)

–select @sql = ‘bcp test_for_forum.dbo.Dates out c:\test\mytab.txt -c -t, -T -S’+ @@servername

select @sql = ‘bcp "SELECT TOP 5 * FROM test_for_forum.dbo.Dates" queryout c:\test\mytab.txt -c -t, -T -S’+ @@servername

exec

master..xp_cmdshell @sql

END

Import from Excel to SQL Server through BulkCopy with ColumnMappings

Posted: April 3, 2009 | Author: | Filed under: Excel 2003 | Leave a comment

 

Here is a code snippet I copied from a question I answered at ASP.NET forum:

If the source columns are not matching with destination in position and/or name, you need to define ColumnMappings during the bulkcopy.

protected void Page_Load(object sender, EventArgs e)
   {
       // Connection String to Excel Workbook
       string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\property.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

       // Create Connection to Excel Workbook
       using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString))
       {
           System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand
           ("Select CountyCode,PropertyAppraiserName FROM [county$]", connection);

           connection.Open();

           // Create DbDataReader to Data Worksheet
           using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader())
           {
               // SQL Server Connection String
               string sqlConnectionString = "Data Source=.; Database=test_for_forum;Integrated Security=True";

               // Bulk Copy to SQL Server
               using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))
               {
                   bulkCopy.DestinationTableName = "tblPropertyAppraiser";

 

                   //Define ColumnMappings: source –destination

                   bulkCopy.ColumnMappings.Add("CountyCode", "DesCountyCode");
                   bulkCopy.ColumnMappings.Add("DesPropertyAppraiserName", "PropertyAppraiserName");
                  

 

bulkCopy.WriteToServer(dr);
               }
           }
       }
   }

http://forums.asp.net/t/1405240.aspx

Generate script from SQL Server 2005 SSMS for SQL Server 2000

Posted: April 2, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment

 

You can generate script to target SQL Server 2000 by using Script Wizard.
In your SSMS, Right click on your database name >> Tasks>>Generate Scripts>>From the wizard, choose your database >>under Choose Script Options >> Script for Sever Version>> choose SQL Server 2000……  a few more clicks to generate your script that is targeted for your SQL Server 2000.

How to handle mixed type data in an Excel column when import to SQL Server through SSIS?

Posted: April 2, 2009 | Author: | Filed under: SQL SSIS | 1 Comment

When you import mixed data type data from an Excel, you may end up with some missing data. Here is a three steps work around to solve this problem:

1. Create a SSIS package through import/export wizard in SSMS and save the package in a file system:

Make sure the mixed type column in target table is a NVARCHAR type.

2.In SSIS design IDE (VS/BIDS), import the package we created early and open it up. Add IMEX=1 to the Excel Connection Manager’s ConnectionString. (Right click on Excel Connection Manager and choose Properties)

Here is an example: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\test.xls;Extended Properties="EXCEL 8.0;IMEX=1;HDR=YES";

3. Follow step 2, Double click on Data Flow Task to go to Data Flow Tab and Right Click on Excel Source and Show the menu with Show Advanced Editor and click on the Editor and go to tab: Input and Output Properties. On the left side window, you will see Excel Source Output, there are two items listed: you expand the Output Columns and choose the column we are interested in and from the property window on the right: Change the DataType to unicode string [DT_WSTR] and an appropriate size like 50 for example. Click OK to finish.

 

When we run the package now, the mixed type data will be imported to the target table’s nvarchar column from the source Excel.

 

Excel Source from MSDN: http://msdn.microsoft.com/en-us/library/ms141683(SQL.90).aspx