I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.
The idea is to automatically grant some permissions and take some other actions whenever a database gets created.
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.
View 4 Replies View Relatedtrying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
I've installed Reporting Services 2008R2 STD edition on my local system. I was trying to configure the Report Server, I am able to connect to the Report Server but the ReportDB and ReportTempDB are getting created in Database Engine instead of Report Server, when I connect to Report Server from SSMA this is what I see (Attached) Screen 1.
The screen2 is of Reporting Service Config Manager, When I connect to the report server using the URL what I am seeing is blank last screen shot attached.
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
I tried to call a exe from sql server through trigger with xp_cmdshell, But its not working as expected, shows preemptive_os_pipeops in process under the activity monitor and the process got hang.
View 18 Replies View RelatedI have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed. At a simple level it is doing something similar to the code below
IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.
IF EXISTS (SELECT * FROM inserted)
begin
IF UPDATE(Col1)
update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
end
However, I would like to know whether there is some system setting other than "nested triggers" that I am missing that would cause the behaviour.
we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also
View 7 Replies View RelatedI have SQL Server 2014 Enterprise Edition with a number of in-memory tables sitting in my database.When server is restarted it takes many hours to recover my database if there was data in these in-memory tables before shutdown.As a result, I need to clean up in-memory tables every time before server instance shutdown. This is really annoying and requires extra prescriptive actions for support team. Can I have DDL server/database level trigger to catch shutdown event and clean my data before instance goes down?
View 3 Replies View RelatedCREATE TABLE [dbo].[Access](
[AdminID] [int] NOT NULL,
[FnID] [int] NOT NULL,
CONSTRAINT [PK_AccessFn] PRIMARY KEY NONCLUSTERED
[Code] ....
why the cluster and non cluster indexed created on the same key?
I have a logon trigger on a SQL Server 2008 R2 Express Advanced production database to prevent remote logons. The trigger works fine. When I need to connect via my local machine, remotely, I connect via a VPN, can connect with SSMS and do whatever I need. However, if I use a linked server on my local machine (still connected via VPN), I receive the logon error
Msg 17892, Level 14, State 1, Line 1
Logon failed for login 'sa' due to trigger execution.
The trigger is below and it logs any failures, except for the linked server.
If I disable the trigger, the linked server connects ok.
CREATE TRIGGER [tr_MasterLogon]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE
@ClientAddress varchar(48) = (SELECT client_net_address
[Code] ....
I create a logon trigger on a sql server 2008 r2 instance,the trigger is very simple,like this:
CREATE TRIGGER tg_login
ON ALL SERVER
FOR LOGON
AS
IF ORIGINAL_LOGIN() IN('sa') AND HOST_NAME()='TestHost1'
[Code] ...
I use master.dbo.LoginRecord table to record the bad logon. When the trigger is working ,latches produced sometimes,wait type is PAGELATCH_UP and wait resource is 2:1:3. At this time, a large number of logon failed ,error message is "Logon failed for login 'login name' due to trigger execution."
I think logon trigger create internal temporary objects maybe,it is right?
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedHi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
View 2 Replies View RelatedHi there.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
DROP DATABASE [db_name_something]
CREATE DATABASE [db_name_something] ON (NAME = N'db_name_something_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'db_name_something_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
..
..
any ideas where I am going wrong?
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
Many thanks
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
View 4 Replies View RelatedHi,
i've sql 2005 std sp2,and i'm working with a program that create every view month's a new database automatically.
my question is - how can i make a full backup of the newly created database automatically only one time when it's created?
Thx
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies View RelatedI created an SQL database in Visual Studio 2005 Express Edition and would like to edit it in SQL Server Management Studio Express. The only databases listed in the Object Explorer of SQL Server MSE are the system databases (ones that are part of the SQL Server MSE).
If I dig through the directories, I can locate my database under the Visual Studio directory, but I am unable to open it. I get an error message stating, "There is no editor available for [my database]... Make sure the application for the file type (.mdf) is installed."
I'm sure that this is a ridiculously simple rookie mistake that I'm committing somehow, but I'm stumped. Any idea what I'm doing wrong?
Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated
View 4 Replies View RelatedI create database in App_Data. Now i wish to get backup of this database and also wish to have this database in my sqlserver 05 databases. Please provide a way so that both tasks will accomplished.
Thanks.
With regards
Dev
Using SQL Server 2005 under XP Professional. I am attempting to log into the Server Management Studio as "UserX". I had previously been using Server Management while logged in as admin.
I now get this error message when attempting to connect to the database server:
**********************************************************************TITLE: Connect to Server------------------------------Cannot connect to [**NAME OF SERVER**].------------------------------ADDITIONAL INFORMATION:Login failed for user '[**NAME OF SERVER**].UserX. (Microsoft SQL Server, Error: 18456)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476------------------------------BUTTONS:OK------------------------------
*******************************
Microsoft help doesn't have a specific help for that particular error message. Any hints as to what is going wrong?
I am a new SQL Server developer using Visual Basic 6 Professional Edition. I am using ADO and SQLDMO to develop my application. The SQL Server is Version 7.0, SP2 and SP3 (two different servers).
I am having difficulty gaining access, using ADO, to databases I create using SQLDMO. I create the database, create the DBFILE object, create a login and attach it to the database, create a user, assign the created login to the user,
and assign the created user to the 'db_datareader', 'db_datawriter' and 'public' roles. the login object was set to 'standard', not NT. the SQL Server is set to 'mixed' NT -SQL Server login mode. Thw SQL Server is running under the 'system account'. Using SQL Server Enterprise Manager, everything looks OK. An attemp to connect using the ADO connection object fails, giving an error of Login failed for user 'engbom3admin'.
Connecting to the SQL Server using SQLDMO uses the 'sa' user name with a blank password.
I can successfully connect to the created database using the ADO connection object using the 'sa' user name and blank password. Using the SQL Server Enterprise manager, I cannot see anything at all different between the 'sa' and my created 'engbom3admin' user. I've manually set the created user to have 'db_owner' roles, etc. Same result. I'm stumped. I've read all I can gather from the Microsoft SQL Server books. I'm still stumped.
I would greatly appreciate any help, information or tips you could provide. Thank you in advance.
Sincerely,
Bob Wohlers
SVP, IS Datamax Corporation
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V
View 2 Replies View RelatedI created a database called sports and then created a table which I'm going to use as a template for other tables within the same database.
What is the proper way to structure and implement copying this template table?
Should I store the template table under the system tables folder?
How do I copy the template table and create a new table from this template?
Thanks you
Goldmember
Hello,
I have managed to create a backup of my SQL Express database by using the Transact-SQL from article http://support.microsoft.com/kb/241397.
I am not sure if it backed up the correct database, as in the script from the article, there is no where for me to specify my SQL Express database. The back up file that was created after I ran the script is, msdb.dat_bak.
Now if this is the correct database, how can I restore the data into my vb.net project that holds the SQL Express Database?
Thanks in advance.
Hi!
I have a piece of code that connects to the master database and uses the simple 'CREATE DATABASE MYDATABASE' statement to create a new database on an sql server 2005 express instance.
I then close that connection and try to open a new connection using my newly created database. I use integrated authentication in both cases. However, I get the following error: Cannot open database "MYDATABASE" requested by the login. The login failed.Login failed for user 'DOMAINNAMEusername'.System.Data.SqlClient.SqlException: Cannot open database "MYDATABASE" requested by the login. The login failed.
I don't get this error all the time. I tried to run my code many times and it some times work. But in some cases it doesn't. The database is always created but some times I cannot connect to it. But again, if I wait a little and try again with the same database, the connection is made.
I know that my connections are opened and closed normally and they don't remain open. So I don't believe I reach the maximum number of available connections.
I also sometimes get this exception : A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.
Any clue?
I'd like to be able to create a new database and have a backup plan automatically created for it. Is there a way to do something like this?
Thanks!
Hi,
I have created an asmx web service that deserializes a JSON string and should execute stored procedures in SQL Server Express 2005, the database is a mdf file. I have created a System DSN which works fine, I have allowed the remote connections and TCP/IP and created the firewall exceptions for sqlserver and sqlbrowser. I believe there's an issue with my connection string, I have tried everything and it still won't connect.
I had another running example but that was on a previous install of Windows XP Pro SP1 with login and this asmx web service worked fine. I run Windows XP Pro SP2 without a login now so I guess I can't use Integrated Security=true or Trusted Connection=yes but does anybody know the default login of a database created in Windows Visual Developer Express?
Here's my connection string:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|
antanplan.mdf;Integrated Security=SSPI;User Instance=false;UID=sa;PWD=sa"
And here's the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at WebService.pushJsonAction(String req) in C:Documents and SettingsAlbanMy DocumentsVisual Studio 2008WebSitesWebSite1
antanplan.asmx:line 62
Thank you,
Alban
Please help me.
I'm a college student working on a database project using MS SQL Server 2005 Express Edition.
The program (SQL SMSEE) is installed on both the computers in class and on two of my computers at home. The first installation resulted in the "remote connections" error. No matter what I do, I can't get the program to fully load. So I tried installing on another computer at home. The second installation went well. Didn't do anything different from the first installation, but any hoo....................
My 2-week old problem is this - I save my database that I do in class on my thumbdrive. Last class, I saved all the files that had my database's name on it on my thumbdrive--.mdf, .log, .bak, etc. On the second home computer, I cannot open the databases that I work on at school. Even my professor is stumped on this one.
Here is what I'm doing......
After I connect to the server, I right-click "Databases" then left-click "Restore Database".
In the "To database" box, I enter the name of the database, as I saved it at school.
In the "To a point in time" box, I leave the default "Most recent possible" entry.
I select "From device" as the location of backup and click the "..." button. The file is on my thumbdrive, so I click "Add" and select the location on my usb (with the .bak extension) and click OK.
I pick the most recent file checkbox and click "OK".
The green progress circle goes to 50% and then gets stuck. The database never opens and the Object Explorer shows the database name followed by "(Restoring...)". So if I try to do anything else with it, I get an error message stating the database in the middle of a restore and that I have to wait until its done. Well, of course it never finishes.
Please help me. Right now I am stuck doing duplicate work at home and at school and am making little to no progress. The final project is due on 26 March and right now I only have my tables, a few attributes and a couple of relationships. And I have a LOT more work to do.
Thanks.