jueves, 29 de noviembre de 2012

SSMS Shared SQL Templates, synchronized with Dropbox account

Actually, I've T-SQL scripts in my USB, but today I'm thinking about the possibility to store it in Template Explorer of Management Studio, it's more fast to get scripts than in USB.

First of all, Scripts of Template Explorer are stored in %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\SQL

100 for SQL Server 2008 R2, the number depends on your SQL Server version.

It's not possible to modify the path of Template Explorer, but it's possible to modify the content of the path.

For me, it's a good option to link the content with Dropbox Folder, to synchronize it.

Windows 7 has introduced a new shell command called MKLINK, which can create symbolic links.

Well, first of all we have to rename SQL folder:

  • Open command prompt and type
    • cd %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates
      For SQL2012: cd %AppData%\Microsoft\SQL Server Management Studio\11.0\Templates\SQL
    • ren SQL SQL_OLD

Now link Dropbox Folder to SQL Directory.


  • mklink /D Sql C:\Users\USER\Dropbox\SCRIPTS (Change it for your desired path).


Now, open SQL Server Management Studio a go to View Menu, Template Explorer (Ctrl+Alt+T).

If all goes ok, you have to view you Dropbox Folder with all .sql file extension.

Something like this.


SQL Server always creates his template scripts in folder, if you try to delete it, on next program execution it will re-created.
In example, non numbered directories, are default SQL Server Template Folders.

Note: If you doesn't find Template folder in  %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\, go to SSMS and open Template explorer, now folder will be created.
It's only necessary do this for first time after installation.

The path for SQL2012 is %AppData%\Microsoft\SQL Server Management Studio\11.0\Templates\SQL

miércoles, 28 de noviembre de 2012

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 )

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 ).

If this error occurs when you're trying to create a mirror, check this points:

  • Restore backup at secondary server has to be with NORESTORE option.
  • All log backup must be restored (Probably some job of SQL SERVER AGENT makes Transaction Log Backup, ensure that last log backup is restored in secondary too).
  • GRANT CONNECT ON ENDPOINT::Mirroring TO Public (I think with domain user that runs service account it's enough, but it doesn't work for me, so, PUBLIC, works).
  • Maybe Firewall block the Port Number, try to connect, with TELNET for example.

I hope it helps.

Cannot create, add, or edit steps for an SQL Server Agent job in SQL Server Management Studio 2008 R2

If you get some error description like this, "Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011." 




Probably this link to Microsoft's Fix can help you: http://support.microsoft.com/kb/2315727