Create a linked server to a local directory path

\ by

Here's what I did to successfully set up a linked server to a directory on a SQL 2000 or SQL 2005 server.


/* Remove any previous references to the linked server */
USE [master]
GO

/****** Object:  LinkedServer [LOCAL_PATH]    Script Date: 01/06/2009 14:04:46 ******/
EXEC master.dbo.sp_dropserver @server=N'LOCAL_PATH', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver 'LOCAL_PATH',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'E:\MSSQL\Reports', NULL, 'Text'

GO

EXEC sp_tables_ex
   @table_server='LOCAL_PATH'

—
— If you get this error
—
—
–OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH" returned message "Unspecified error".
–Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
–Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH".

–Simply log onto the server as the same account that the sql services run as.
–Then under my computer properties changes the TEMP and TMP environment variables to something like
    C:\temp
    or
    C:\TempSQL
–which should have security permissions changed to allow local users to have RWC permissions.

 You might also lookup this on the Microsoft site.
    PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet

 

Posted in: SQL Server

SQL limitations columns sizes etc.

\ by

Find it here.

 

SQL Server Architecture (SQL Server 2000)
Maximum Capacity Specifications
  Topic last updated — January 2004

The first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition.
 

Posted in: SQL Server

T-SQL Searching using Wildcard Characters

\ by

Searching for Wildcard Characters

You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:

Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string 5% anywhere in a string, use:

WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
In this LIKE clause, the leading and ending percent signs (%) are interpreted as wildcards, and the percent sign preceded by a slash (/) is interpreted as the % character.

Use square brackets ([ ]) to enclose the wildcard by itself. To search for a hyphen (-), instead of using it to specify a search range, use the hyphen as the first character inside a set of brackets:

WHERE ColumnA LIKE '9[-]5'
The following table shows the use of wildcards enclosed in square brackets.

Symbol Meaning
LIKE '5[%]'
5%

LIKE '5%'
5 followed by any string of 0 or more characters

LIKE '[_]n'
_n

LIKE '_n'
an, in, on (and so on)

LIKE '[a-cdf]'
a, b, c, d, or f

LIKE '[-acdf]'
-, a, c, d, or f

LIKE '[ [ ]'
[

LIKE ']'
]

When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and trailing blank (space). If a comparison to return all rows with a string LIKE 'abc ' (abc followed by a single space) is requested, a row in which the value of that column is abc (abc without a space) is not returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE 'abc' (abc without a space) is requested, all rows that start with abc and have zero or more trailing blanks are returned.

 

Posted in: SQL

List Database Recovery Mode on a server

\ by

A handy script to have around for a nice report.

SELECT LEFT(name,50) DatabaseName, CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name, 'recovery')) RecoveryMode  
    FROM sysdatabases 
    WHERE name NOT IN ('model','tempdb') 
    ORDER BY name
            

Posted in: SQL

Rename a SQL Server, steps.

\ by

You've renamed the server itself. Now it's time to rename SQL instances from within SQL. You'll need to reboot the services after the rename.

 Let's see what the whole picture is now by running this.
  EXEC sp_helpserver

Get the old servername to use to drop it.
    SELECT  srvname AS OldServerName  FROM  master.dbo.sysservers WHERE srvid=0  

Check the name of the server as it is now to use to create the new SQL instance name.
  EXEC master.dbo.xp_getnetname   

Now we have enough information to change the name. e.g.
    EXEC master.dbo.sp_dropserver  'SQLOld\I2K'

    EXEC master.dbo.sp_addserver 'SQLNew\I2K','local'   

Running this should show the old server name until we restart the SQL Services.
     SELECT @@SERVERNAME  

— results in SQLOld\I2K

Stop and restart the SQL Server services.

— results in SQLNew\I2K

 

 

Posted in: SQL, SQL Server

Installing SQL 2005 Performance Dashboard Reports

\ by

I'm a little obtuse sometimes so when following the install instructions I didn't see where this was installed on my PC.

SQL Server 2005 Performance Dashboard Reports

So searching my entire PC here is the script that now needs to be run on each instance of SQL 2005 you want to monitor. 
      C :\Program Files\SQL Server DBA Dashboard

Here are some highlights from the script that may concern you.


CREATE ASSEMBLY DBA_Dashboard
FROM 'C:\Program Files\SQL Server DBA Dashboard\DBA_Dashboard.dll'
WITH PERMISSION_SET = SAFE

GO

and

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SS_DBA_DASHBOARD_GATHER_STATS', 
... So I guess we'll be using SQL Agent, or not with SQLEXPRESS on my PC …

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
SQLServerAgent is not currently running so it cannot be notified of this action.
SQLServerAgent is not currently running so it cannot be notified of this action.

 

Posted in: SQLAgent

Using SQLAgent Tokens – Automatically add a schedule

\ by

Automatically add a schedule to a SQL Agent job. This example adds one that will run two hours from now, once. Left to do: what if no longer today?

More tokens.

DECLARE @vcSchedule VARCHAR(128),
   @nJOBID uniqueidentifier,
   @nDATE INT,
   @nTIME INT,
   @nHours INT
SELECT @vcSchedule = 'Reschedule',
   @nJOBID = CONVERT(uniqueidentifier, [JOBID]),
   @nDATE = [DATE],
   @nTIME = [TIME],
   @nHours = '020000' + @nTIME
--SELECT @nJOBID, @nDATE, @nTIME, @nHours 

IF EXISTS (SELECT [name] FROM [msdb].[dbo].[sysjobschedules] WHERE job_id = @nJOBID AND name= @vcSchedule)
  EXEC msdb..sp_delete_jobschedule @job_id=@nJOBID, @name=@vcSchedule

EXECUTE msdb.dbo.sp_add_jobschedule 
   @job_id = @nJOBID, 
   @name = @vcSchedule, @enabled = 1, 
   @freq_type = 1, 
   @active_start_date = @nDATE, 
   @active_start_time = @nHours, 
   @freq_interval = 1, --once
   @freq_subday_type = 1, --At the specified time
   @freq_subday_interval = 1, 
   @freq_relative_interval = 0, 
   @freq_recurrence_factor = 0, 
   @active_end_date = 99991231, 
   @active_end_time = 235959

Posted in: SQL, SQLAgent