list local files from SQL Server 2005 (xp_cmdshell enabled)

-- found this on the sqlserver central website.

USE master
GO
CREATE PROCEDURE dbo.sp_ListFiles
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)

SET @Work = 'DIR ' + '"' + @PCWrite + '"'

CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

SET @Retain = @@ERROR

IF @Status = 0 SET @Status = @Retain

IF @Status = 0 SET @Status = @Return

IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '% %')
AND (@DBUltra != 0 OR Name NOT LIKE '% %')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))

+ ' SELECT SUBSTRING(Name,40,100) AS Files'

+ ' FROM #DBAZ'

+ ' WHERE 0 = 0'

+ CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END

+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING

(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END

+ ' ORDER BY 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO


--And to test:
--EXECUTE sp_ListFiles 'c:\ftp',NULL,NULL,NULL,1

bcp format file: sundry thoughts

I am indexing this link for my own edification. 

http://msdn.microsoft.com/en-us/library/ms191516.aspx

What are the columns in that table?

Oh my.
I wish I found this many years earlier.

use MyDatabase
go

SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

replace MyDatabase and MyTable with your database and table values

RESTORE HEADERONLY

If you don't know which backup files hold the right database to restore?
If you need to locate the correct backup file to restore, use RESTORE BACKUP WITH HEADERONLY option.

See msdn definition file:
http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx

How Heavy is my SQL Server database is being used?

I wrote this script after studying about I/O statistics.
This view is based on sys.dm_io_virtual file status
This looks at the physical file reads and writes, and how many times it stalled.
If it is stalling, you can dig deeper to see what is causing the stall… then you can partition the offensive table/object etc.

Cool system view!

-----------@Mydatabase-----------------------

-- type in name of database between the ''
-- or type in 'all' between the ''
--------------------------------------------

declare @Mydatabase nvarchar(255)
set @Mydatabase = 'all'
--------------------------------------------

if @Mydatabase = 'all'
begin

select

db_name(database_id)
as database_name,
file_name(file_id)
as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes

from
sys.dm_io_virtual_file_stats(null, null) ;
end
else
begin
declare @database int
set @database = db_id(@Mydatabase) ;

select

db_name(database_id)
as database_name,
file_name(file_id) as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from

sys.dm_io_virtual_file_stats(@database, null) ;
end

I keep losing this helpfile.. when I need it the most, it is located in the BOL labeled "sp_addlinkedserver (Transact-SQL)", if you scroll to the bottom of the article.

The frustrating thing about learning about linked servers is the coordination between the different types of connections! Viva la difference?

Remote OLE DB data source	OLE DB provider (@srvproduct)	product_name (whatever you want to name it "blah blah"…)	provider_name (@provider)	data_source (@datasrc)	location (anyone?)	provider_string (@providerstr?)	catalog (@catalog)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server 1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle	Microsoft OLE DB Provider for Oracle	Any2	MSDAORA	SQL*Net alias for Oracle database
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB. Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

Link SQL Server 2008 to 2005

Have on hand:

1. A name for your linked server you want to create; can be anything, just keep it clean.
   I will call mine MyLinkedServer
   
   
2. If default instance, name of the server only (as opposed to server/instance)
   Mine is MyComputerName\SQLEXPRESS2
   
   
3. If second instance, server/instance_name
   
   
4. It is presumed you have same login name as the server you are reaching over and linking to.
   I tested these two servers on the same computer (SQL Server 2005 & 2008)
   If they are not, impersonate yourself to the server you are trying to talk to:
   
   1. After scripting this out, go to SMSS and right-click your near MyLinkedServer à Properties
      
   2. Select the security option
      
   3. Add the login name the other server uses and click impersonate.
      

   EXEC
   master.dbo.sp_addlinkedserver
   

   @server =
   N'MyLinkedServer',
   

   @srvproduct=N'',
   

   @provider=N'SQLNCLI',
   

   @datasrc=N'
   MyComputerName\SQLEXPRESS2'
   

If you use the graphical interface, go for it. This way is easier for me.

Now you can call whatever database you have there. Here's what I did:

select
*
from [MyLinkedServer].[MyDatabase].dbo.[MyTable]