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) So I guess we'll be using SQL Agent, or not with SQLEXPRESS on my PC …
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SS_DBA_DASHBOARD_GATHER_STATS',
...
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.
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?
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
Here is one way that can be easily made into a procedure.
--
-- Working example for @vcTable.
--
USE pubs
GO
DECLARE @vcTable VARCHAR(128)
SET @vcTable = 'authors'
DECLARE @vcColumn VARCHAR(8000),
@vcINSERT VARCHAR(8000)
DECLARE
@vcSQL VARCHAR(8000),
@vcFormatting VARCHAR(2),
@bDatesAsText bit
SELECT @vcFormatting = CHAR(10)+ CHAR(9),
@bDatesAsText = 1
SELECT @vcColumn = COALESCE( @vcColumn + ' VARCHAR(128),', 'CREATE TABLE ##x (') + COLUMN_NAME,
@vcINSERT = COALESCE(@vcINSERT + ''',', 'INSERT ##x VALUES (') + '''' + COLUMN_NAME,
@vcSQL =
COALESCE(@vcSQL + ', ' + @vcFormatting , 'SELECT ' ) +
CASE
WHEN @bDatesAsText = 1
AND DATA_TYPE IN ('datetime','smalldatetime') THEN
'char(34) + convert(varchar(23), ' + QUOTENAME(COLUMN_NAME) + ', 121) + char(34)'
WHEN DATA_TYPE IN ('varchar','nvarchar','char','nchar','text','ntext') THEN
'char(34) + ' + QUOTENAME(COLUMN_NAME) + '+ char(34)'
ELSE
QUOTENAME(COLUMN_NAME)
END + ' AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @vcTable
ORDER BY
ORDINAL_POSITION
SELECT @vcColumn = @vcColumn + ' VARCHAR(8000))',
@vcINSERT = @vcINSERT + ''')',
@vcSQL = @vcSQL + '
FROM ' + DB_NAME() + '.dbo.' + @vcTable
PRINT @vcColumn
PRINT @vcINSERT
-- This must be in one statement or the optimizer thinks the table doesn't exist.
EXEC (@vcColumn + ';' + @vcINSERT)
-- add data here ...
PRINT @vcSQL
EXEC ('INSERT ##x ' + @vcSQL)
SELECT *
FROM ##x
DECLARE @vcCmd VARCHAR(8000),
@vcOutFile VARCHAR(4000),
@vcFieldParameter VARCHAR(2)
SELECT @vcOutFile = 'E:\MSSQL\REPORTS\test.bcp',
@vcFieldParameter = ',',
@vcCmd = 'bcp "SELECT * FROM ##x" queryout "' + @vcOutFile +
'" -S' + @@SERVERNAME + ' -T -t ' + @vcFieldParameter + ' -c '
PRINT @vcCmd
EXEC master.dbo.xp_cmdshell @vcCmd
-- very important
DROP TABLE ##x
--
--
--
PROBLEM: MSDTC seems to be working. e.g. Linked queries to servers or in this case a text file can be queried etc. while running individual commands in Query Analyzer, but fail running the entire procedure.
Not caring the whys of it, what is wrong with DTC that we can fix?
For Windows 2003 SP1, see these instructions. Basically don’t use Local System account. Try this account and search for the local account login of NetworkService. When it comes up there is something in the password field, ooops. Seems to work if we clear both password textboxes.
After your changes simply bounce the MSDTC service and run your SQL query again. This time it should work.
Why are these so hard to find on the Microsoft site? Too many products?!
SQL Server 2005 SP2 download.
-- Open a text file using OPENROWSET doesn't allow No header
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\SQL\;',
'SELECT * FROM [My Listing 1.txt];'
)
-- Open a text file using OPENDATASOURCE does. See schema.ini
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\SQL\;Extended Properties="Text;HDR=No;FMT=Delimited"')...[My Listing 1#txt]
-- Try this quoted text comma delimiter example:
-- Run this in DOS
bcp "SELECT [OrderID] AS [OrderID], char(34) + [CustomerID]+ char(34) AS [CustomerID], [EmployeeID] AS [EmployeeID], [OrderDate] AS [OrderDate], [RequiredDate] AS [RequiredDate], [ShippedDate] AS [ShippedDate], [ShipVia] AS [ShipVia], [Freight] AS [Freight], char(34) + [ShipName]+ char(34) AS [ShipName], char(34) + [ShipAddress]+ char(34) AS [ShipAddress], char(34) + [ShipCity]+ char(34) AS [ShipCity], char(34) + [ShipRegion]+ char(34) AS [ShipRegion], char(34) + [ShipPostalCode]+ char(34) AS [ShipPostalCode], char(34) + [ShipCountry]+ char(34) AS [ShipCountry] FROM Northwind.dbo.Orders"queryout "E:\MSSQL\REPORTS\Orders20081106.txt"
–SSQLDEV –T –t "," –c
—
— Check that the results has the Comma delimited Quoted text that you want.
—
— Now query this in Query Analyzer to aid in importing it into SQL.
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\MSSQL\REPORTS\;Extended Properties="Text;HDR=No;FMT=Delimited"')…[Orders20081106#txt]
NOTE: REPLACE the .txt WITH #txt AS per OPENDATASOURCE usage.
create table #myTable( bar bit) go insert into #mytable values (1) insert into #mytable values (1) insert into #mytable values (NULL) insert into #mytable values (NULL) go select distinct bar from #mytable go select bar, count(*) from #mytable group by bar go
RESULTS: bar ---- NULL 1 bar ---- ----------- NULL 2 1 2
Looks like both count/report NULLs correctly.