DB Design :: Automate DDL Scripts Deploy On Database Directly?
Mar 19, 2015
I have an existing XY database which has tables and triggers , I have a new updated DDL scripts which I have deploy on XY database. Every week I will be updated with new DDL scripts which I have to deploy on database dropping all existing tables.
Currently I am dropping all the tables and then copying all tables script to one and executing it.
My Que: Is there a way to automate the DDL scrips deploy on database directly?
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
Hey all Im trying to make a system in which users logged on can change their password. I dont want to use a grid view because that dosent give me the chance to let them put in their old password first for validation but now I need a way of takeing data out of the textbox with the new password and placeing it in to the field in a database.As I stand at the moment I have a data.dataview variable equal to an SQL Data Source with my query in it, bringing back a record from a users table with the users username, password and a field stating what type of user they are. It filters this by username which is stored as a session variable and username is the primary key. there is then an if statement checking that the string in the password field is equal to what the user has written in the old password box and if they match it uses the command:dv.Table.Rows(0)(1) = txtNewPassword.TextMsgBox("Password Successfully Changed", MsgBoxStyle.Information)When I run this the validation successfully checks the password is correct and then displays the message box saying the password is correct but it hasnt actually changed it. Somone I know told me that I need to use the command sqldatasource.update but I cant see where it fits in. Anyone got any ideas?
Hi, and thanks for your help. I usually generate script of a database table, triggers, views, and store procedures by right click table/Task manager/selecting Generate sql script...etc
How can I automate this process, Is there a way where I can set up such sql scripts generation via Ms sql Agent so every week I can generate database script.
I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?
I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type *i am using VS 2005 and VB language
what i need to do for my project are as following:
a mobile user send data via GPRS to SQL server Database. then i need to have a method to detect while a particular table is being inserted. and then extract data from table construct a report dynamiclly. what should i do to achieve this goal? e.g. window application, store procedure or trigger ?
PS : client side is a mobile application developed using MCL. i don't in which way he will send all data to SQL server Database, so what i need to do is to monitor new data inserting.
2) how to auto generate and print report directly after record been inserted into the table ? Do i need to import report web service API ? if yes, which one? or i can use other methods e.g. predefine report control view in my window application, turn off pop-up menu while printing a report(I guess)
We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.
Accounting2014, Accounting2015 in AccountingDB; Sales2014, Sales2015 in SalesDB; Products2014, Products2015 in ProductsDB; Inventory2014, Inventory2015 in InventoryDB etc....
These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....
Am getting errors trying to deploy a dtsx created by ms (the reporting services execution log one) to which I have made zero changes, but it is not working (2 errors shown below)
error from deployment wizard: ===================================
Could not save the package "H:SSISRSlogRSExecutionLog_UpdateinDeploymentRSExecutionLog_Update.dtsx" to SQL Server "xxxxxxxxxxx". (Package Installation Wizard)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
Hi All, Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.
I'm deploying an ASP .Net application on which 20 users will be working at the same time. The database will start at less than 100mb. Is SQL EXPRESS 2005 powerfull enough to use for such an application. Thanks in advance for your answers
Hi all, I have created a database in MS-Access. I want to deploy that database on the local server, how can i do it?
Let me clear, i have created a DSN for my database. I want to deploy a report on RServer(on SQL Server2005). This DSN will be the datasource for the report. When i am trying to deploy this report with this DSN i am getting an error message "Cannot create a connection to data source 'DataSource2'. (rsErrorOpeningConnection)". Could any one help me?
I am trying to write an installation for my application.
I am using an SQL Express database as well as an ODBC driver to connect and querry an existing interbase database.
Therefore the install needs to do the following:
Install .NET - done
Install SQL Express - done
Install application - done
copy some XML files - done
Install ODBC Driver - done
Create database - not done
I am using a setup and deployment project to do the above. I have also written a small application to install the ODBC Driver and attepmt to create the database which I intend to run as a custom action if possible.
I have used the SQL management studio script wizard to write an sql script for creating the database with the intention of using an SQLconnection to run the script into the database but I can't seem to be able to make the connection.
I am trying using the following connection string...
Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamaster.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
But I get the following error message...
System.Data.SqlClient.SqlException: Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamaster.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamaster.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I finished a web application, it need sql database support. I made a deploy project and it work perfect to create a visual directory on target machine. But I need create and config the sql server database manually.
My question is how to make a deploy project which can let user input sql user and password, then create sql server database and write down the connection information to web.config file?
I see some Microsoft sample's setup can do that. Any body know how to do?
We just converted one of our commercial applications over to using SQL Server. We have a database that is about 700megs in size, what I want to know is how is one suppose to actually deploy a database in an automated install? I have created T-SQL insert scripts, but it is 764M in size, and it takes forever to read the script in to send to the SQL Server. So I am looking for suggestions on how to actually do this.
I have a vb.net web page which I created (a shopping cart). I created this site using vb.net 1.1 and SQL 2000 on my laptop. On my laptop everything works properly, but when I tried moving it to GoDaddy (http://currahee3-506.org/px/default.aspx). In the code, each query to the SQL server is done using stored procedures. When you first go the page, a drop down is presented. When you select "Shirt" from the category drop down. A second drop down menu is then displayed and will let you select what type of shirt you are interested in purchasing. Select Polo Shirt Without Pocket. Upon doing so, I receive an error message:
Invalid object name 'tbl_LookupCategory'.Invalid object name 'tbl_pxProducts'.Invalid object name 'tbl_LookupColor'.Invalid object name 'tbl_LookupSizes'.Invalid object name 'tbl_pxQtySold'.Invalid object name 'tbl_pxImages'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'tbl_LookupCategory'.Invalid object name 'tbl_pxProducts'.Invalid object name 'tbl_LookupColor'.Invalid object name 'tbl_LookupSizes'.Invalid object name 'tbl_pxQtySold'.Invalid object name 'tbl_pxImages'.Source Error:
Line 143: sqlCommand.Parameters.AddWithValue("@str_ItemName", Trim(DDL_Items.SelectedItem.Text)) Line 144: Line 145: Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection) Line 146: Line 147: dl_Description.DataSource = dataReader Source File: d:hostingcurraheepxdefault.aspx Line: 145 Keep in mind the Stored Procedure works in my testing environment, but not on GoDaddy. I moved the selected statement into a view on the GoDaddy SQL server, and the view worked perfectly. The tables do exist on the GoDaddy SQL Server. This is the Stored Procedure: CREATE PROCEDURE [dbo].[sp_GetItemInfo] @str_ItemName VarChar(50) AS SELECT TOP 100 PERCENTtbl_pxProducts.int_ItemID,tbl_pxProducts.str_ItemName,tbl_pxProducts.int_RetailPrice,tbl_pxImages.str_SmallImageURL,tbl_pxImages.str_MediumImageURL,tbl_pxImages.str_MediumImageBackURL,tbl_pxImages.str_LargeImageBackURL,tbl_pxImages.str_LargeImageUrl,tbl_pxImages.str_SmallImageBackURL,tbl_pxProducts.int_Quantity - tbl_pxQtySold.int_QtySold AS int_QtyAvailable,tbl_LookupCategory.str_Category,tbl_pxProducts.Int_SizeID,tbl_LookupSizes.str_Size,tbl_pxProducts.Int_ColorID,tbl_LookupColor.str_Color FROMtbl_LookupCategoryINNER JOINtbl_pxProductsONtbl_LookupCategory.int_CategoryID = tbl_pxProducts.int_CategoryIDINNER JOINtbl_LookupColorONtbl_pxProducts.Int_ColorID = tbl_LookupColor.int_ColorIDINNER JOINtbl_LookupSizesONtbl_pxProducts.Int_SizeID = tbl_LookupSizes.int_SizeIDLEFT OUTER JOINtbl_pxQtySoldONtbl_pxProducts.int_ItemID = tbl_pxQtySold.int_ItemIDLEFT OUTER JOINtbl_pxImagesONtbl_pxProducts.int_ItemID = tbl_pxImages.int_ItemID WHERE(tbl_pxProducts.int_Quantity - tbl_pxQtySold.int_QtySold > 0)AND(tbl_pxProducts.str_ItemName = @str_ItemName)
ORDER BYtbl_LookupSizes.str_Size Does anybody have any suggestions as to why this stored procedure is failing?
hi, I have developed my database on express edition of 2005 .Now I need to deploy this database which contains storedprocedure .will u give me some link for some help. I donot know from where to start. And I also want to change the Authentication mode from windows to MIX mode for sa user ....or whatever the username is given by the hoster....
i want to develop a web application that use SQL Server Database, i have installed SQL Server & VS.NET in my local computer and i'm ready to develop, but before getting started, i want to know how i will upload mydatabae in my host,
actually i want to know should i know the settings & properties of my host that support SQL Server, or without know them i can devekop my apploication and after developing localy with just a few tasks upload my ( Sql Server) database?????
i'm worry actually about this, plz say what you know about this. thanks
Dear Sir,How do I deploy database to remote server in web matrix. It does not have enterprise manager.I have to use osql.exe command line that runs msde statements that are exuceted against database. Can youplease let me know how to use osql.exe.Regards,Farhan
Hi,A quick question, is it possible to deploy a website on a shared hosting environment which attaches to a MDF database file stored in the App_Data folder?
I want to deploy my database application to my client's computer. My application uses SQL Server 2014. My client has to run that software on a single PC. I was looking to download a lightweight version of SQL Server that can fulfill my requirements, so when I was looking for SQL Server downloads, I saw these files there, which file should I use in my situation
I have a data dictionary report that is driven by measure and dimension description attributes in the SSAS project. Is there a way to generate some XMLA or something that allows me to update this "metadata" only without overwriting the database or requiring a full reprocess?
i need a regex support in SQL 2005 server. i've downloaded SQL regex support project from http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx?loc=en. but in order to enable regex support i needed to open VS 2005. compile the project and deploy it to SQL server. now i can use Regexp-s in SQL queries. However this is not elegant way to deploy everytime SQL Database project via Visual Studio to every MS SQL server i want. my question is : how can i deploy without using Visual Studio?
How to Deploy SQL server Database to another PC, How to create apackage that craetes Database as well as ODBC driver for accessing dataat enduser PC, using .Net VB
i have done a project in vb.net ,now i want to create setup for sql server database so that we can run easily on client machine,plzzzzzzzzz give me answer for deploying
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
