If @@servername returns null or return wrong host name, execute below mentioned sql Query. DECLARE @Current SysName Select @Current = Convert(varchar(128), SERVERPROPERTY(‘ServerName’) ) IF(@Current <> @@SERVERNAME) BEGIN EXEC sp_dropserver @@SERVERNAME EXEC sp_addserver @server=@Current,@local = ‘local’ EXEC master..xp_cmdshell ‘ECHO NET STOP MSSQLSERVER > restartSQL.bat’, no_output EXEC master..xp_cmdshell ‘ECHO NET START MSSQLSERVER >> restartSQL.bat’, no_output EXEC master..xp_cmdshell [...]
Archive of posts filed under the SQL category.
Export data from corrupted database
Below is the sql script to import data from source database into target database, It is assumed that you have both the databases on single server.The source database is current database & few tables are corrupted whereas the traget database is created from old backup for target database.since their is corruption ,it is not possible [...]
Compare DB – record counts
Code snippet will loop through all tables of Database SOURCEDB and compare record counts with tables in TARGETDB database USE SOURCEDB EXEC SP_MSforeachtable ‘DECLARE @OriCount INT DECLARE @Count INT DECLARE @Name VARCHAR(400) SET @Count = (SELECT COUNT(*) FROM TARGETDB.?) SET @OriCount = (SELECT COUNT(*) FROM SOURCEDB.?) SET @Name=”?” IF(@Count <> @OriCount) BEGIN [...]
SQL Server Discovery
The objective of this article is to discover the presence of Microsoft SQL Server across subnet. There are API’s to enumerate SQL Server instances in single subnet (Win32 API : NetServerEnum ) and Microsoft enterprise manger uses this API to populate the list of SQL server available in current subnet. The API NetServerEnum broadcast UDP packets [...]
Security : SQL Server Passwords
To securely use SQL Server, Microsoft recommends using Windows Integrated Security. In Windows Integrated Security mode, passwords are never stored as your Windows Domain sign-on is used as the security identifier to the database server. Password and login ID of a registered SQL Server user in Enterprise Manager for SQL Server can be easily retrieved [...]