Database Is Not Being Created
Apr 10, 2006
Hi 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
View 3 Replies
ADVERTISEMENT
Jun 29, 2015
trying 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?
View 3 Replies
View Related
Jul 15, 2015
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 Related
Apr 8, 2007
Hi 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 Related
Jan 14, 2008
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
View 5 Replies
View Related
Oct 22, 2015
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.
View 6 Replies
View Related
Dec 26, 2007
Hi,
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
View 16 Replies
View Related
Jul 29, 2006
I 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?
View 3 Replies
View Related
May 27, 2007
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 Related
Jun 12, 2007
I 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
View 2 Replies
View Related
Oct 12, 2007
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?
View 4 Replies
View Related
Jan 10, 2001
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
View 2 Replies
View Related
Aug 24, 2006
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 Related
Jun 18, 2008
I 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
View 3 Replies
View Related
Nov 1, 2006
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.
View 1 Replies
View Related
Jul 24, 2015
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 ?
View 3 Replies
View Related
Mar 10, 2007
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?
View 5 Replies
View Related
Mar 20, 2008
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!
View 3 Replies
View Related
Mar 7, 2008
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
View 1 Replies
View Related
Mar 19, 2007
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.
View 3 Replies
View Related
Sep 2, 2007
I installed netframework 2.0 Visual Web developer and MSSQL 2005 express edition with SQL Server management express.I have got this configuration: 2*256 mb ram Intel Pentium 3.2Ghz Windows XP HUN SP2 latest version.server name: localhostSQLEXPRESSAuthentication: Windows AuthenticationI run aspnet_regsql.exe and the setup wizard created aspnetdb see here, Microsoft sql server management studio can see the database:But! When I run to the asp.net web application administration tool in Provider Configuration and chooseAspNetSqlProvider only 1then I click Select a single provider for all site management data link -> then testThe Tool write this:Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site
Administration tool, use the aspnet_regsql command-line utility to
create and configure the database, and then return to this tool to set
the provider.
View 5 Replies
View Related
Mar 23, 2008
I created a new database in SQL Server 2000, using the command
"CREATE DATABASE ContactManager ON PRIMARY(NAME = ContactManager,FILENAME ='C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)LOG ON(NAME = ContactManager,FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 2MB)Go"
Databse was successfully created and I created two tables also in it. Now when i try to create a new connection in Visual Web Developer 2005, I get the error message :
"Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" failed with the operating system error 5 (Acess is denied.).
Cannot attatch the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" as 'ContactManager'."
What else do I need to do use the database?
View 7 Replies
View Related
Dec 2, 2005
Using VWD 05 Express & SQL Server 05 Express, how do I transfer my database to my host that is using SQL Server 2005.
On the SQL Server express web site http://msdn.microsoft.com/vstudio/express/sql/powerful/ one of the features listed for SQL Server 05 Express is: Transfer any database created in SQL Express to other editions of SQL Server 2005.
Thanks
View 7 Replies
View Related
Jun 1, 2012
I have just started in a brand new role where I need to collect a large amount of diverse data. Part of this data is whether the steps in a guideline has been followed ( a simple check box or yes/no) (but there are maybe 100 guidelines)
My IT department have suggested we use SQL to create the database, but this will obviously take time. In the meantime my boss is keen that I get on with the data collection before the database is ready. I do not want to repeat steps when I create the SQL database so is there any format you would recommend I collect my data in now that can be easily applied to the SQL database - ie if I used Excel, would that be best, and should I then try to keep potential SQL table data separate?
View 3 Replies
View Related
Jun 16, 2015
Is there any maximum cap on the number of users that could be created in a given database of MS-SQL Server?
View 4 Replies
View Related
Oct 3, 2006
I have created a database in Visual Studio 2005 by adding a new "SQL Database" item to my project, and then designing the tables with Server Explorer.
How can I add this database to my local instance of SQL Server Express, and to my SQL Server 2000 database engine?
View 4 Replies
View Related
May 4, 2007
Hi,
I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
CREATE PROCEDURE dbo.InsertProduction
@DATEOUT datetime(8),
@DATEREQUIRED datetime(8),
@PREPAREDBY varchar,
@COMMENTID INteger,
@TOTALQUANTITY INteger,
@VENDORID INteger,
@WPO varchar,
@TCAPONUMBER INteger,
@APPROVEDBY varchar,
@Identity int OUT
AS
INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY)
SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update
'Get a reference to the Production table.
Dim dtProduction As DataTable = DS.Tables("Production")
Dim dtLineItem As DataTable = DS.Tables("LineItems")
' Create the SqlCommand to execute the stored procedure.
Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection)
Production.InsertCommand.CommandType = CommandType.StoredProcedure
' Add the parameter for the CategoryName. Specifying the
' ParameterDirection for an input parameter is not required.
'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName")
' Add the SqlParameter to retrieve the new identity value.
' Specify the ParameterDirection as Output.
Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID")
parameter.Direction = ParameterDirection.Output
' Create a new row with the same schema.
Dim dr As DataRow = dtProduction.NewRow()
'you need the ID from this to insert into the Production DB
' Set the value of all the columns.
dr("DateOut") = CDate(DateTimePicker1.Text)
dr("DateRequired") = CDate(DateTimePicker2.Text)
dr("VendorID") = CInt(vendorbox.SelectedValue)
dr("HomeAddress") = txtApproved.Text.ToString
dr("ApprovedBy") = txtPrepared.Text.ToString
dr("TCAPO") = CInt(txtTCAPO.Text.Trim)
dr("CommentID") = CommentID
dr("TotalCost") = CDec(txtTotals.Text)
dr("TotalQuantity") = CInt(txtQtyTotal.Text)
' Add to the Rows collection or table .
dtProduction.Rows.Add(dr)
'Update the Production Table and then retrieve the ID created in this case
Production.Update(dtProduction)
Dollarjunkie
View 2 Replies
View Related
Nov 12, 2014
Query to find the date/time when a database schema was created and who created it.
View 2 Replies
View Related
May 26, 2007
When I try to add a (SQL Server 2005 Express Edition) database to my project (I'v tried a windows application and an ASP.NET application) in Visual Studio 2005 Professional Edition; It fails with an error like this :
"create database failed. some file names listed could not be created (...)"
but when I open the project from the real path It works.
Is this a bug? Is there any solution? (In many situations there is a need for working with virtual drives. There must be some work around ...)
Thanks
View 1 Replies
View Related
Jun 11, 2015
We have a production server "prod" which has a user say 'test' that lets the users connect to the application and a logon trigger which stops them from connecting to the server through SSMS. I log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.
Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.
View 9 Replies
View Related
Aug 22, 2013
We recently migrated our production server from SQL 2005 (Standard) on Win2003(32-bit) to SQL2012 (Standard; v11.0.3000) on Win2008-R2(64bit). Single-server Dell R510 with 1.2TB storage. Everything went smoothly; the only nagging issue remaining is failure of our maintenance jobs. I tracked the issue down to failure of DBCC CHECKDB. Specifically, the error is:Executed as user: NT SERVICESQLSERVERAGENT. The database could not be exclusively locked to perform the operation. [SQLSTATE 42000] (Error 5030) Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be lockedI have Googled this issue and read extensively. For instance, informative blogs (albeit dated) such as these by Paul Randal (Managing
CheckDB by default takes an internal DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to get an exclusive database lock before proceeding (same as if you had executed DBCC CHECKDB WITH TABLOCK). The root problem is not that the lock could not be obtained, it's that the internal database snapshot could not be created. msdn.microsoft.com/en-us/library/ms188796.aspx details the specific situations when an internal database snapshot is not created and table locking is attempted.
I have verified the SQLSERVERAGENT service account has full permissions on the SQLDATA directory where the databases reside and has full permissions on each database within the directory. Just for giggles, I created a job (run as SQLSERVERAGENT) that creates and then deletes a text file in the SQLDATA directory. It runs fine.
Also testedI get the snapshot creation error when manually running DBCC CHECKDB against any of our databases and when executing under a variety of administrator accounts that are members of the SQL sysadmin role and the Domain Admins security group (the Domain Admins is a member of the local Administrators group that has full permissions on all SQL directories/folders).
Additionally, the databases in question are small (200MB to 6GB) and the disk has plenty of elbow room (978GB free on 1.22TB RAID5 array) to create the internal database snapshots. CHECKDB doesn't surface an error message that is detailed enough to determine the precise cause of the error. Any example successfully running DBCC CHECKDB on the SQL2012 (Standard) on a Win2008 R2 (64-bit) server.
View 22 Replies
View Related
Jul 17, 2007
Hi,
I needed to recreate/publish a DDL and data file(of a database) that was produced using Database Publishing Wizard. The size of the file is 4G. I receive the following error when I try to open it up in the new query window of sql server 2005 (sp2)for execution.
"The operation could not be completed. Not enough storage is available to complete this operation."
I know my server has 829G of free storage space available. i receive no errors when I open up a smaller file also created by database publishing wizard.
What possibly is causing this?
Thanks in advance
View 3 Replies
View Related
Mar 17, 2014
I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
View 6 Replies
View Related