Jun 17 2008

Search All tables within a Database

 

I came across this very useful script the other day, it simply creates a Stored Procedure, where it allows you to search all columns within every table of a selected database, While it was said to be tested on SQL 7 and 2000 I can confirm it works on SQL 2005 just as well :) 

   1: CREATE PROC SearchAllTables
   2: (
   3:     @SearchStr nvarchar(100)
   4: )
   5: AS
   6: BEGIN
   7:  
   8:     -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
   9:     -- Purpose: To search all columns of all tables for a given search string
  10:     -- Written by: Narayana Vyas Kondreddi
  11:     -- Site: http://vyaskn.tripod.com
  12:     -- Tested on: SQL Server 7.0 and SQL Server 2000
  13:     -- Date modified: 28th July 2002 22:50 GMT
  14:  
  15:  
  16:     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  17:  
  18:     SET NOCOUNT ON
  19:  
  20:     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  21:     SET  @TableName = ''
  22:     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  23:  
  24:     WHILE @TableName IS NOT NULL
  25:     BEGIN
  26:         SET @ColumnName = ''
  27:         SET @TableName = 
  28:         (
  29:             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  30:             FROM     INFORMATION_SCHEMA.TABLES
  31:             WHERE         TABLE_TYPE = 'BASE TABLE'
  32:                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  33:                 AND    OBJECTPROPERTY(
  34:                         OBJECT_ID(
  35:                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  36:                              ), 'IsMSShipped'
  37:                                ) = 0
  38:         )
  39:  
  40:         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  41:         BEGIN
  42:             SET @ColumnName =
  43:             (
  44:                 SELECT MIN(QUOTENAME(COLUMN_NAME))
  45:                 FROM     INFORMATION_SCHEMA.COLUMNS
  46:                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
  47:                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)
  48:                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  49:                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName
  50:             )
  51:     
  52:             IF @ColumnName IS NOT NULL
  53:             BEGIN
  54:                 INSERT INTO #Results
  55:                 EXEC
  56:                 (
  57:                     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
  58:                     FROM ' + @TableName + ' (NOLOCK) ' +
  59:                     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  60:                 )
  61:             END
  62:         END    
  63:     END
  64:  
  65:     SELECT ColumnName, ColumnValue FROM #Results
  66: END

 

Once you have ran the SP, you must call it, then within the ‘ ‘ you can type what you are looking for, the execute and wait!

EXEC SearchAllTables ‘your query here’

 

the original script was Found at :

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

no comments | posted in Microsoft, SQL Server, Techie

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

9 comments | posted in Microsoft, SQL Server, Software

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)

3 comments | posted in Microsoft, SQL Server, Software, Techie