Export CSV file from SQL Server from script.

 This is a quick way to export a SQL  View or Table to a CSV file

The file can only be exported to a folder on the local SQL Server where it has run.

First all you data must be in one view or table. I use a Temp table to do the Job.

Once you have your data in one place:-

Declare @sql as varchar(8000). @outputpath  as varchar(3000)

set @outputpath  ='c:\temp\testcsv.csv'

select @sql = 'bcp "select  * from Yourdb..Temptable " queryout ' + @outputpath + ' -c -t, -T -S '  + @@servername 

exec master..xp_cmdshell @sql

 EXECUTE AS login = 'user'

and there you have it, the @sql needs to be a large Varchar for this to run or it fails. @otputpath need to be on  the local sql server

Restart SQL Server mail system

To restart SQL Mail run the following

first stp it running
EXECUTE dbo.sysmail_stop_sp

no start it again.
EXECUTE  sysmail_start_sp

This works fine i find.

Round Up To Next Whole Number TSQL

I was looking for a way to round up the results of a query to the next whole number and after
 fair bit of searching the net and experimenting to get this to work in SQL 2005
The result was


declare @totalpallets as int
declare @total as int
declare @palletsize as int


set total_pallets =  (select ceiling(cast(@total as float)/cast(@palletsize as float)))



This does the job admirably

ADD Users to SQL via TSQL Script

I had need to create adhoc users with an SQL login so I did some digging and put together this TSQL



GO
declare @username as varchar(50)
set @username ='test1'
DECLARE @SQL NVARCHAR(4000);
begin

SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''12345'', DEFAULT_DATABASE=[Logic], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@SQL);

end

use {Your DB Name}
EXEC sp_grantdbaccess @username
EXEC SP_ADDROLEMEMBER N'db_datareader', @username;
        EXEC SP_ADDROLEMEMBER N'db_datawriter', @username;


The script can be put into a procedure and passing the username to it will create a user with read \right privileges on the DB.

Get List of Stored Procedures Tsql

To get a list of sprocs(Stored Procedures) from SQL run the following

select *
  from Database Name .information_schema.routines
 where routine_type like 'Procedure'

Replace Database Name  with you Database name.

Check if a temporary table exist in as stored procedure

To check if a temporary table exist  in as stored procedure use the following

 if object_id(N'tempdb..#temp') is not null

Where #temp is your table

Then do something

if object_id(N'tempdb..#temp') is not null
 drop table #temp

Check if Temp table exists

To check if a temp table exists and if not create one use the following

IF OBJECT_ID('tempdb..#archive') IS NOT NULL
begin
 drop table #archive
end
else
begin

create table #archive(orderid int)


This checks to see if the temp table #archive exists and if it does them it drops it before recreating it

Date intervals for VB and Tsql

Date intervals for Sql Server

year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Usage  DATEPART(datepart,date)

Dates for Visual Basic

DateInterval.Day	d	Day of month (1 through 31)
DateInterval.DayOfYear	y	Day of year (1 through 366)
DateInterval.Hour	h	Hour
DateInterval.Minute	n	Minute
DateInterval.Month	m	Month
DateInterval.Quarter	q	Quarter
DateInterval.Second	s	Second
DateInterval.Weekday	w	Day of week (1 through 7)
DateInterval.WeekOfYear	ww	Week of year (1 through 53)
DateInterval.Year	yyyy	Year

Usage DatePart(DateInterval.Quarter, ActualDate)

Moving MS Sql to another sever.

I had the need to swop out a SQL server and after trawling the net finally came up with a plan and discarded it at first contact with the job and did this.

Get the new server ready by duplicating the drive set up ie if there is an cd drive on E then the new server should have the cd on E.

·         Do Not put it on the network.
call it the same name as the old one, give it the same ip(S).

·         Clean the data tables of any unwanted data shrink and defrag the tables.

·         Complete a SQL back up of the database.

·         Make sure that there are no users attached clear all locks.

·         Detach the database(s) including the MSDN database.

·         Copy the data and log files onto a portable drive.

·         Remove the server from the network.

·         Attach the new server to the network.

·         Copy the data and log files to the new server.

·         Attach the databases you will have to detach the MSDN database and rename the file before copying the new one into its correct folder.

·         Check that the user has come across properly and that all tables are correct.

·         check some clients so that you can see they are working correctly.

·         Before letting the users and processes loose check that SQL mail is configured and working, this should be done while no one is logged on as it requires a lock on the database.

I have used this to swop out a server it works there are some tweaks here and there that need to be done but nothing major.

Good luck

Drop full text index SQL

From time to time you may have to drop a full text index if so the following may be of intrest.

Get the catalog

SELECT name
FROM sys.fulltext_catalogs;

Get the table

sp_help_fulltext_tables;

drop the table catalogs

DROP FULLTEXT INDEX ON  tablename

This may need a bit of work as i have just copied it from some notes i made but i should point you in the right direction.

Insert Row with Identity

To insert values with a specific id use the following code this will insert a line of data with a defined id.

Beware whilst this is running NO AUTO IDENT WILL WORK keep every one away from this table.

SET IDENTITY_INSERT Table name ON

 INSERT table name (UID, Value) VALUES (2006, 'any value')

 SET IDENTITY_INSERT Table name OFF


If this fails due to a syntax error in the insert statement then  the Identity will remain OFF

Use string builder to create connection string.

 Use this string builder to create your Sql connection string


Public Function CreateConnectionString(ByVal strUserName As String, ByVal strPassword As String) As String
        On Error GoTo err
        Dim strConnBuilder As New SqlConnectionStringBuilder


        With strConnBuilder
            .ApplicationName = "Your Application Name"
            .AsynchronousProcessing = False
            .ConnectTimeout = 20
            .IntegratedSecurity = True
            .NetworkLibrary = "DBMSSOCN"
            .MinPoolSize = 1
            .MaxPoolSize = 50
            .DataSource = "Your SQL server name or IP Address"
            .InitialCatalog = "Your database name"

        End With
        Return strConnBuilder.ConnectionString
        Exit Function
err:
        errormsg("SysModule-CreateConnectionString" & Err.Description)
    End Function


Call somthing like this

 sqlCon1 As New SqlConnection(CreateConnectionString("", ""))