Get New Database Created Then Running Script To Created Tables / Relationships
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?
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100) declare @SQL VarChar (2000) Declare spot cursor scroll for select GM from BIM_Historical_Performance.dbo.Performance_Master open spot fetch first from spot into @@GM While @@Fetch_Status =0 BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
I have created a installer for my application and database. When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON ________________________________________________________________________________________________ "I won't last a day without SQL"
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created - Get a list from each temp table of the columns created.
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose, I have four data tables with the same structure but records may be different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I want to that two records by filtering all tables and sum of rates and want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
Hey there, Trying to build a temporary table based on a parent table such that:select * into #staging from tbl_parent where 1=0 The temp table (#staging) picks up any NOT NULL constraints from the parent (tbl_parent) table and frankly, it doesn't meet my needs.I also tried to build a view of the parent (tbl_parent) table from which to base my SELECT INTO and still, the constraints followed. I'm thinking perhaps it possble to iterate though the temp table using syscolumns sysobjects and and set the column NULL properties on the temp table AFTER its been created but in this approach, I'm working to avoid directly referencing the columns by name. Is this possible? A better answer perhaps? Purpose of exercie is to populate a record in memory before it hits the table.I'll be constructing the record on the fly so that at first, I won't have all the fields to de-Null the columns.Also, I don't wish to lose the fact that some of my fields are Null - once I've fully populated my temp record, I'll insert into my 'real' table and will depend on constraints to throw the appropriate error. In this I won't have to built custom error checking in the stored procedure itself but instead depend on SQL SERVERS built capacity to the throw the error. This opens a bunch of possibilities for extensibility since if at a later date a constraint rule changes, one need only change the parent table definition rather than cracking open the stored procedure. Thank you for reading and a big thanks to you for replying :) Also,
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')
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.
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 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.
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.
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?
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
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
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
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?
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 ?
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.
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
