Jun 13 2008

MSSQL: Automatically Create table and insert records

 

So we have started to create an IP accounting package the, package will dump to a CSV file on a daily Basis. We needed to be able to report on that, the easiest way is defiantly importing it into SQL server.

So I created a script that will create a table called ‘accounting_datehere’

With our requirements, we needed yesterdays date, as the import would happen once a day to and would happen after the day has ended (12:00am) which would give us the most accurate results. so therefore we needed to change the date stamp on the table name to yesterdays date, by adding a -1 to the Getdate() this would allow us to append yesterdays date.

Once the table is created, it does an insert from a CSV which is using a UNC connection.

 

Here is the code.

   1: Use Master
   2: declare @tablecreate varchar(1000)
   3: set @tablecreate = 'create table [Accounting_'+convert(varchar,getdate()-1,112)+']
   4: (
   5: Date varchar(500),
   6: IP_Address  varchar(500),
   7: Packets varchar(500),
   8: Out_Bytes varchar(500),
   9: In_Bytes varchar(500)

  10: )'

  11: exec (@tablecreate)
  12: go
  13: 
  14: 
  15: declare @insert_command varchar(MAX)
  16: set @insert_command = 'insert into Accounting_'+convert(varchar,getdate()-1,112)+'
  17:
  18: SELECT *

  19: FROM OPENROWSET('

'Microsoft.Jet.OLEDB.4.0'',
  20: '

'Text;database=\\server\share;HDR=NO'',
  21: '

'SELECT * FROM test4.csv'')'
  22: execute (@insert_command)

 

Some things you might need to know, if you need to use a UNC path like I, then your SQL service must be running under an account that does has network access (unlike the default local system account) .

Also that user that is used as the service account must have permissions to the file via the shares.

Thanks needs to go to Adrian Foyn for a little help with this code.

 

Brad


Jun 3 2008

Configuring the CRM v4 outlook Client Fails

A while ago we rolled out CRM v4, however we only started to roll out the client for V4 only very recently, When trying to roll out the application I was getting an odd error message.

 

“The configuration wizard cannot connect to the Microsoft CRM server. This might indicate that the server is down”

 

The server was clearly not down, as the web interface was fine. It turned out to be a mix of IIS and database related issues.

Lets look at IIS:

if you are running a host header with CRM, simple answer don’t. Use DNS rather then then setting a host header on CRM, this will cause you all sorts of grief (this was explained in a previous post also). Also within regards to the Host header, Also I find not setting a host header will cause problems when installing the client.

So you ask what can I do… easy have a host header of the server name, ensure you are NOT using ALL UNASSIGENED, but use a specific IP (the primary for the adaptor if more then one)

Then do a IISRESET.

So that’s IIS sorted.

So lets look at SQL server.

Now if you are like us we moved our front end server (IIS) a few times, which is fine but the CRM database makes reference to the web server and the port it runs on, so you must change these references also.

Backup your database before using this script.

USE MSCRM_CONFIG
Update DeploymentProperties SET NVarCharColumn = '<serverName>:<port>' WHERE ColumnName = 'AsyncSdkRootDomain'
Update DeploymentProperties SET NvarCharColumn = '<serverName>:<port>' WHERE ColumnName = 'ADSdkRootDomain' 
Update DeploymentProperties SET NvarCharColumn = '<serverName>:<port>' WHERE ColumnName = 'ADWebApplicationRootDomain' 

NOTE: replace the ‘<serverName>:<port>’ with your IIS servername and the Port it runs on. (STD install port for CRM is 5555)


May 15 2008

Show all Full Text Index’s in MSSQL

I looked long and hard for something that would query all databases on a MS SQL server where it would display all of the full Text Indexes. However there is really not that much I could find. However I came across this query

sp_msforeachdb ‘use [?] exec sp_help_fulltext_catalogs’

 

we all know that the SP_msforeachdb has the ability to query all databases on the server, then we combine that with:

exec sp_help_fulltext_catalogs’

which shows us

image

As you can see it only shows you for the database selected nothing more.

if you run the below query by itself then you will are unable to see the results in any clear manner, especially if you need to export them to a CSV or alike (like I needed to do)

sp_msforeachdb ‘use [?] exec sp_help_fulltext_catalogs’

image

 

So I got one of our brilliant Developers Adrian Foyn, do something where we can use this data into a readable format.

So he create a temp table against the master database, then we save the results to the table, then query and drop  the table

 

create table mytemp
(
FT_ID varchar(500),
FT_NAME varchar(500),
FT_PATH varchar(500),
DB_Name varchar(500),
col5 varchar(500)
)
go
sp_msforeachdb ‘use [?];insert into master..mytemp exec sp_help_fulltext_catalogs; update master..mytemp set DB_Name = ”?” where DB_Name=”0”’
go
select    *
from    mytemp
go
drop table mytemp

 

this will also show the database name which corresponds to the FT index.

 

works brilliantly.

Thanks Adrian.


May 5 2008

SQL 2005 Vs 2008 Backups

I was searching through SQL central the other day and I came a cross a fantastic article on ‘SQL 2005 vs. SQL 2008 Part 1 (backup File Sizes & Times)’

So I thought for those that don’t subscribe this is fantastic information to share.

If you do subscribe find the article here:

http://www.sqlservercentral.com/articles/Compression/62746/

(I also urge to to subscribe its free and there are some fantastic resources.)

By Kevin van der Merwe, 2008/05/05

“Backup File Sizes

Databases today are growing bigger and bigger by the day and this seems to only be the start of what we can see in the near future with more content being accumulated and stored than ever before.

In SQL 2005 there was no capability of the engine to compress database backup files to disk at the time of the backup being executed.

In SQL 2008 there are 3 options while backing up databases, these are:

- No Compression (same as SQL 2005)
- Server Setting (if server backup compression setting is on/off – use this setting)
- Compression (Compress backup file during backup)

Scenario

We have a 3.5GB Database Backup File from a SQL 2005 Server Backup.

881

This 3.5GB Database was then restored to the SQL 2008 Server
(Note: No additional transaction activities on this database after restore)

After the Restore:

871

SQL 2008 Backup Options:

Backup without Compression:

This will be the same as the SQL 2005. The General Page does not have any visible changes as can be seen below:

879 The second Page – under Options has a new section titled “Compression” – here we get our first look at the options available:- Use the default server setting
- Compress Backup
- Do not compress backup

878 For this first option we are going to be using “Do not compress backup” CPU Activity during Backup without Compression:

877

Zoom in  |  Open in new window

CPU activity is for the entire backup process – averaging approximately 18% CPU usage. SQL 2005 BAK File Database SQL 2008 Backup File – no compress:

876 Note these are virtually identical in size – as the backup method used is the same for:
SQL 2008 and SQL 2005
Backup WITH Compression:The Options Page now looks like the following:

875 For this first option we are going to be using “Compress backup” The SQL 2008 Backup File Size – using the above setting:SQL 2008 – No Compression SQL 2008 with Compression

874 CPU Activity during Backup WITH Compression:

873 Note the CPU usage during backup with Compression is on average 35% – approximately double

File Size Summary

The SQL 2005 .BAK file that we created the Database with was 3.5GB
The SQL 2008 Backup without compression was 3.5GB
The SQL 2008 Backup with compression was 986MB. For this database the reduced space is 2.5GB; the compressed file is only 28% of the original size!

Note: Not all databases will have this compression factor or CPU usage due to systems and data types etc.

Backup Time Summary

The SQL 2008 Backup without compression took approx 6 minutes and the SQL 2008 Backup with compression took approx 3 minutes. You might be asking – how is that possible?

Well the CPU is used to compress the data before it is written to disk, so LESS data is written to disk. The less data that is required to be written to disk the faster this can be done by SQL Server and the operating system. 

Summary

With databases growing daily, the knock-on effect of this additional data has various cost and time factors. If you are managing a 100GB database, the speed of the backup window, which can now be reduced, is excellent news for DBAs.The size of the actual backup file that needs to be stored on disk and then archived to tape or preferred method is using up less space, which reduces costs ultimately.The quicker backups as well as smaller backup files are more than enough to warrant an investigation into SQL 2008, especially for VLDBs.

Until next time.

Kevin”

By Kevin van der Merwe, 2008/05/05

Apr 30 2008

SQL Search is Slow on the First Search

This is an Old one but some people I find still get stuck scratching their head, including me.

If you are using full Text indexing for your web based searches, you most likely don’t have Internet access on your SQL server, and this is the root cause of the search performing very slowly on the first attempt of the search, as SQL is trying to establish a connection for ‘Signature verification’

You can turn this feature off:

run

sp_fulltext_service ‘verify_signature’, 0;
GO

 

for more info go to

http://support.microsoft.com/kb/915850