Tag: MSSQL
-
Prevent Truncation for Dynamically Generated T-SQL Results in SSMS
I have recently run into an issue where I run one script that generated another (T-SQL), and for some reason, some of the output is cut off or truncated in SSMS. I want to prevent this. This truncation is caused by the settings below being too low for your case. Altering these setting will significantly…
-
Creating the ColdFusion Client Variable Storage Database and Tables within MySQL
ColdFusion does not create the proper tables for client variable storage when using MySQL as the database engine. This is valid in ColdFusion 9 and also still present in ColdFusion 10. These tables should be created manually for client variable storage to function. Two tables are required. CDATA and CGLOBAL. Details about theses tables are…
-
Restore of database ‘NEWDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended)
Restore of database ‘NEWDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&LinkId=20476 Lets take a look in the SQL Server error log, to do so: In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs. Right-click a…
-
Migrating SQL Server Users
Are you migrating SQL users? I recommend migrating their SID’s as well. There is this article on MSDN. The sum of it is to run this: USE master GO IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length…
-
Xp_cmdshell and Errors in SQL server.
This article is essentially a combination of two articles I had read online. One made it easy to find the errors and the other made it easy to find the fix, but I wanted one place where I could find BOTH. What do you need in place in order to use xp_cmdshell? I warn you,…
-
Adding a new user to SQL server 2005, 2008, 2010
Make sure you give the user sufficient permissions to the DB they are being given access to, typically being db_datareader, db_datawriter, db_ddladmin, and public. There is also a SQL statement you can run: CREATE LOGIN [user] WITH PASSWORD=’password’, DEFAULT_DATABASE=[your_db], CHECK_POLICY=OFF GO CREATE USER [user] FOR LOGIN [user] EXEC sp_addrolemember N’db_datareader’, N’your_db’ EXEC sp_addrolemember N’db_datawriter’, N’your_db’…
-
Generate the code to recreate all non system databases in MSSQL without content.
The following text will output the SQL statement to recreate all databases: select ‘create database [‘ + name + ‘] go’ from master..sysdatabases Where DBID > 4 You will need to set Results to Text so that the output is correctly formatted. You can then copy users/logins as seen here: http://support.microsoft.com/kb/918992.