Debug SQL Network Interfaces, error: 26 ….

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

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

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

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.”

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

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

 

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