How to query the table on remote sql server which has xml column(s)

Problem: you need to run a query on a table on remote ("linked") server. The table has one or more xml columns. Your query doesn't depend on any xml columns, however it still fails with an error Xml data type is not supported in distributed queries. Remote object 'SERVERNAME.DBNAME.dbo.tTable' has xml column(s).


Failing Query Example: SELECT TOP 10 SomeID FROM [SERVERNAME].[DBNAME].[dbo].tTable


Solution: Use OPENQUERY: SELECT * FROM OPENQUERY([SERVERNAME], 'SELECT TOP 10 SomeID FROM [DBNAME].[dbo].tTable') AS a


Thanks to: the bug (sorry, "limitation") in Sql Server 2005 and 2008

SQL Server, how to return the size and rows count for all tables in a database

The script that will return the size and number of rows for all user tables in a database.

DECLARE @Table VARCHAR(255)    
CREATE TABLE #Results
(
    [TableName] varchar(100),
    [RowsCount] varchar(100),
    [SizeReserved] varchar(50),
    [SizeData] varchar(50),
    [SizeIndexes] varchar(50),
    [SizeUnused] varchar(50)
)
 
-- Get all user tables
DECLARE cursorTableList CURSOR
FOR SELECT [name]
 FROM dbo.[sysobjects] 
 WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
OPEN cursorTableList
FETCH NEXT FROM cursorTableList INTO @Table
WHILE (@@Fetch_Status >= 0)
BEGIN
    INSERT  #Results EXEC sp_spaceused @Table
    FETCH NEXT FROM cursorTableList INTO @Table
END
CLOSE cursorTableList
DEALLOCATE cursorTableList
-- RETURN RESULTS
SELECT * FROM #Results ORDER BY CAST([RowsCount] AS INT) DESC
DROP TABLE #Results
GO

Howto remap the Windows Account to a SQL Server login

/*

Remaps the Windows Account to a database user. Works with SQL Server 2005, 2008, 2008 R2.

Expects the following variables to come from sqlcmd.exe:

$(DbName) - database name, i.e. AdventureWorks

$(ServerLogin) - the login to use, should be Windows Account, either local or domain. DON'T USE localhost! USE computer name or domain name!!

$(DbUserName) - the user within the database

Usage:

SQLCMD -S"MyServer" -E -i"RemapLogin.sql" -v DbName="AdventureWorks" -v ServerLogin="%COMPUTERNAME%\SomeUser" -v DbUserName="some_user_in_db" -b

*/

USE master

GO

IF EXISTS (SELECT sid FROM master..syslogins WHERE [name]=N'$(ServerLogin)')

BEGIN

PRINT 'Dropping the $(ServerLogin) login';

DROP LOGIN [$(ServerLogin)];

END

GO

PRINT 'Creating the $(ServerLogin) login from Windows Account';

GO

CREATE LOGIN [$(ServerLogin)] FROM WINDOWS

GO

-- Grant server role bulkadmin that's required for BCP.

EXEC master..sp_addsrvrolemember [$(ServerLogin)], 'bulkadmin'

GO

USE [$(DbName)]

GO

PRINT 'Mapping the $(DbUserName) user in DB to newly created login';

GO

/*

Need to detect SQL Server version because LOGIN clause was added only in SQL 2005 SP2

*/

IF EXISTS (SELECT ProductVersion FROM

(SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel) Vrsn

WHERE CAST(ProductVersion AS varchar(100)) LIKE '9.00.%' AND CAST(ProductLevel AS varchar(100)) IN ('RTM','SP1'))

BEGIN

PRINT 'SQL Server 2005 pre-SP2 detected';

ALTER USER $(DbUserName) WITH NAME = $(DbUserName);

END

ELSE

BEGIN

PRINT 'SQL Server 2005 SP2 or later or 2008 detected';

EXEC sp_executesql N'ALTER USER $(DbUserName) WITH NAME = $(DbUserName), LOGIN = [$(ServerLogin)]';

END

GO

PRINT 'FINISHED!';

GO

Message box for user from a DOS prompt

Approach 1:
Works in Windows XP onwards:
msg * "Hello, Cruel World!"

Approach 2:
works in Windows NT, 2000, XP. Doesn't work in Vista onwards, requires messenger windows service to be running:
net send %USERNAME% "Blah"

Approach 3:
1) Create a file messagescript.vbs:
<script type="text/vbscript">
MsgBox "your text here"

</script>

2) Run the commandwscript /nologo messagescript.js