MSDE Not Listening on 1433
Friday, October 27, 2006, 10:35 PM - DataBases, Sql Server
I tried to connect to my MSDE instance tonight with the MySQL Migration tool, and couldn't get in. Apparently MSDE was not listening..
I found the following advice, which worked GREAT!
You need to enable TCP/IP on the server end, not just the
client. You can use the server network utility,
svrnetcn.exe.
The utility can be found in the Tools\Binn directory of your
installation path.
All I had to do was use this utility to enable the TCP/IP protocol, restart the service, and voila.
[ add comment ] | [ 0 trackbacks ] | permalink |
( 2.9 / 223 )Finding Identity Columns in SQL Server
Wednesday, May 25, 2005, 10:13 AM - Sql Server
Here is a quick and dirty query to show all columns in the DB that have identity columns set:
select * from syscolumns where autoval is not null
That's it.
[ add comment ] | [ 0 trackbacks ] | permalink |
( 3.1 / 217 )Query All Text Values in a DB
Tuesday, May 24, 2005, 10:04 AM - Sql Server
I often need to search for a value in all the text columns in a db. Here is a little nested cursor ( I know, Cursors, EWWW ) that will find all of the text fields in all of the tables in the db you are in and query their values for the value in @value.
declare @value varchar(150)
select @value = 'test'
declare @tablename varchar(150)
declare @columnname varchar(150)
declare @select varchar(400)
declare tablecursor cursor for
SELECT sysobjects.name as TABLE_NAME
FROM dbo.sysobjects
WHERE sysobjects.xtype = 'u'
ORDER BY TABLE_NAME asc
open tablecursor
fetch next from tablecursor into @tablename
while @@fetch_status = 0
begin
set @select = 'select ''' + @tablename + ''' as TableName,
* from ' + @tablename + ' where '
declare columncursor cursor for
SELECT syscolumns.name as COLUMN_NAME
FROM dbo.sysobjects
JOIN dbo.syscolumns
ON sysobjects.id = syscolumns.id
WHERE sysobjects.xtype = 'u' AND
sysobjects.name = @tablename AND
syscolumns.xtype = 167
ORDER BY COLORDER asc
open columncursor
fetch next from columncursor into @columnname
while @@fetch_status = 0
begin
set @select = @select + @columnname +
' like ''%' + @value + '%'' OR '
fetch next from columncursor into @columnname
end
close columncursor
deallocate columncursor
set @select = substring(@select,1,len(@select)-3)
EXEC (@select)
fetch next from tablecursor into @tablename
end
close tablecursor
deallocate tablecursor
[ add comment ] | [ 0 trackbacks ] | permalink |
( 3 / 223 )What SQL2K Version am I Running?
Tuesday, May 17, 2005, 08:36 AM - Sql Server
Run the following sql in the Query Analyzer.
SELECT SERVERPROPERTY('productversion') as 'Product Version', SERVERPROPERTY ('productlevel') as 'Service Release', SERVERPROPERTY ('edition') as 'Database Edition'
this will return a nice neat summary of product installation.
[ add comment ] | [ 0 trackbacks ] | permalink |
( 3 / 231 )Find all tables containing Identity columns
Monday, May 16, 2005, 02:10 PM - Sql Server
To find all the tables containing identity columns:
SELECT
IDENT_SEED(TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_NAME) AS Increment,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),
'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
[ add comment ] | [ 0 trackbacks ] | permalink |
( 3 / 228 )
