What Will Happen In Target Database When Publishing Profile File Is Not Being Used
Sep 18, 2015
I'm exploring using VS2013, TFS 2013 and Release Management for DB projects.In VS 2013 the deployment configuration is stored in the publishing profile, which is not necessarily being used by deployment tools. (Release Management for example, while it's supporting the profiles, it does not require them) So, the question is how to control what will happen in the target database when the publishing profile file is not being used?
Is there any way to store the deployment configuration with project so it will be applied by default?For example, if I want to control the following settings: Block incremental deployment if data loss may occur...Drop objects in the target DB that are not in the DB project, e.t.c.
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.
I am new in SSIS. Anyone know how to valify number of record that I load from csv file to SQL database table?
For example, the source file call product.csv and target table in database named DSS table name PRODUCT. I load data from flat file to table then I need verification if count between source and target not match send e-mail to me.
We have a previous SQL 2012 cluster that emails us when a new database is added. I am unable to figure out why we get this error any time we add a new database to it, and also it prevents us from adding a new availability group to this cluster because of this error.
I also am unable to figure out what profile it is talking about as this was setup before me and I am not a DBA.
I am using sql 2000 SP4 and I am really struggling finding some dead locks. I can enable Startup Parameters T1204 and T3606 which gives me a partial picture.
But then I need to run a profile for deadlocks etc. This is where I have a problem what Events and Data columns do I need?
Also I have an extremely heavy loaded server; if I run a full profile I will grind the server to a halt.
I can get away with running a duration trace and setting duration > 3000. But I find that if I add more events and data columns the duration > 3000 no longer stops the flood.
This is because if the field you are filtering by has no value it will include it in the trace. Which is a real problem.
So has anyone got and advice on how I can set up a profile to help me find dead locking but wont cripple my server.
I am using ASP.NET 2.0 C#, SQL Server 2000Connection string is: "Data Source=USMAN;Initial Catalog=CMS;Integrated Security=True" And by searching forums and goodle, I have also set sql server 2000's secutiry as mixed modeInfact When I compile and run my projcet from visual studio it is fine, every thing works ok Then I publish my site to a folder on desktop, when publishing completed I right clicked the folder and in web sharing option I added alias = CMS Now when I open: http://localhost/CMS/Home.aspx, then it works fine infact all the pages that dont have SQL Server 2000 connectivity work fine, but as soon as I open some page that has connectivity with the database, it gives me error like: Login failed for user'USMAN/ASPNET' I wonder why this error only comes after publishing, there is nothing probemaltic when running it from Visual Studio, plz tell me what to do
We are trying to script out database schema and data with the Database Publishing Wizard and are having problems with output script trying to create views and stored procedures before the tables they depend on. I've seen discussion of this on their codeplex site and back in April 2007 they said that the CTP they just released fixed the problem - but we are still seeing it. Does anyone know a solution for this (beyond the obvious copy and pasting into the right order)? What we are trying to do is move the SQL and some basic reference data into source control and a CI process.
I have developed a database application using c# and SQL Express 2005.
I have coded a connection string that works fine on the development machine but upon publishing to another machine I am getting the following error message:
Cannot open database "name of database" requested by the login. The login failed. Login Failed for user "machine nameuser name".
The remote machine has SQL server express installed.
Now on the development machine the machine name and user names are different to that on the remote machine. I am using Windows Auth. The application successfully runs on the remote machine. It even updates the same database (as mentioned in the issue above) on a different form which all connection strings etc were created automatically by c#. I'm pretty new to all this and don't know where to go from here. Any help would be greatly appritiated.
I have developed a database application using c# and SQL Express 2005.
I have coded a connection string that works fine on the development machine but upon publishing to another machine I am getting the following error message:
Cannot open database "name of database" requested by the login. The login failed. Login Failed for user "machine nameuser name".
The remote machine has SQL server express installed.
Now on the development machine the machine name and user names are different to that on the remote machine. I am using Windows Auth. The application successfully runs on the remote machine. It even updates the same database (as mentioned in the issue above) on a different form which all connection strings etc were created automatically by c#. I'm pretty new to all this and don't know where to go from here. Any help would be greatly appritiated.
Now here is something I have been asking the SQL Server Team for a long long while : http://www.microsoft.com/downloads/details.aspx?familyid=16D8C1E9-A6A3-43A2-9BA7-916C6EB5023B&mg_id=10046&displaylang=en . I hope this will be of use to many of us
The SQL Server group at Microsoft has just launched the SQL Server Hosting Toolkit with the release of the Database Publishing Wizard Community Technology Preview 1.The objective of the SQL Server Hosting Toolkit is to enable a great experience around hosted SQL Server. The Database Publishing Wizard works toward this mission by making it easy to upload a database from a development box up to a shared hoster. In its first incarnation, the Database Publishing Wizard is a command line tool that generates a T-SQL script designed to be executed in the script execution windows provided by most hosters in their database management consoles. In the coming months we'll be adding a GUI and building seamless integration between the tool and an upload service we'll provide to hosters for deployment.Details on the Database Publishing Wizard as well as the download can be found at http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard.We plan to be releasing advances in the Toolkit frequently so check out this first CTP and give us your feedback!Thanks,Dan WinnSenior Program Manager, Microsoft SQL Server
I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise.
I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too.
My client will upload a file to a FTP folder regularly, but the time patten is unknown, only thing i know is he'll use today's date as the file name. for example, he uploaded a file 'wk070705_id.txt' today, but i don't know when the next file will be uploaded. so how can i write a package like, eveytime when he upload a new file (for example if he upload a file 'wk070708_id.txt'), i can import the data into my table? i am a very newbie of SSIS, it would be perfect if i can get a full script for this. thanks in advance with appreciation!
after i login, i should be able to see the login person's profile, and edit it. anyone know how to do this?currently i am using grid view to display the profile, and created a store procedure in database ALTER PROCEDURE [dbo].[select_customer_by_login] -- Add the parameters for the stored procedure here(@Username nvarchar(50))AS SELECT C.CustomerName, C.TFN, C.Address, C.City, C.State, C.PostCode, C.Phone From dbo.Login L, dbo.Customer C Where L.CustomerID = C.CustomerID and L.UserID=@Username; @Username is the login account when people login. the error output is Procedure or Function 'select_customer_by_login' expects parameter '@Username', which was not supplied.
What could be simpler: map a flat file record structure, extract the data, and populate essentially the same flat file record struc in an Oracle table. Let the fun begin.
Specifically: the flat file record struc is fixed length 196 bytes. A particular field consists of 4 bytes of Integer data; IS deals very nicely with the definition, does not appear to be any issue with that. The issue is trying to get the 4 bytes of integer to map and load into the Oracle table. The data type in the flat file def is DT_UI4. The data type in the Oracle target is DT_NUMERIC. One would think that perhaps a simple transform and Viola?! I've defined the transform but does not seem to matter - whatever I try yeilds the same results.
I 've tried many different src/trg data type defs., but all yeild the same results.
Execution Results from debug:
Everything validates and then...
[kcd [8671]] Error: Data conversion failed. The data conversion for column "load_time_min" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[kcd [8671]] Error: The "output column "load_time_min" (11050)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "load_time_min" (11050)" specifies failure on error. An error occurred on the specified object of the specified component.
Control Flow Load Data Flow Task to Copy Data Flow Task to Scrip Task
Data Flow under Load Data Flow Task has Flat File Source to Row Count1 to OLE DB Destination (ODS database) Variable name for Row Count1 is RowCount
Data Flow under Copy Data Flow Task has OLE DB Source to RowCount2 to OLE DB Destination (WIMS database) Variable name for Row Count2 is RowNumber
Data Flow under Script Task is code:
' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.
' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main() Dim varMyRowCount As Variable = Dts.Variables("RowCount") Dim varMyRowNumber As Variable = Dts.Variables("RowNumber") Dim varPackageName As Variable = Dts.Variables("PackageName") Dim varStartTime As Variable = Dts.Variables("StartTime") Dim varInstanceID As Variable = Dts.Variables("ExecutionInstanceGUID") Dim varMailMsgtext As Variable = Dts.Variables("MailMsgText") Dim PackageDuration As Long Dim Filenum As Integer Dim FilNam As String
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ' Event log needs '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Dim sSource As String Dim sLog As String Dim sEventMessage As String Dim sMachine As String '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We want to verify flat file count should be same as the data load to WIMS database. Now if I run this job I get the error message: Error: Failed to lock variable "RowCount" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
My co-work said even I get balance from RowCount1 and Rowcount2 still need to have query from table in Wims database because for SQL server sometimes process counts not same as the data results load in table so need select count(*) from table. How and where I can put at the job.
My design is row count from flat file put one variable then row count from final table put the other variable then send e-mail to me show both variable for testing. For real job I need if both variables same ok else send me an e-mail.
We have our replication environment with a separate SQL 2014 publisher, distributor and subscriber. We have a table named DPSP on 14 databases which is to be published via transactional replication to 1 subscription database.
The source tables are identical in name, schema and clustered index. We have created 14 tables in the subscription database with a suffix to the name (see below):
Each table in the subscription database has its own uniquely named primary key and clustered index:
Below are the settings for one of the published article:
The same process is repeated for each DPSP table created in 14 separate publications. Snapshots are generated and subscriptions created successfully. After some time, the log reader starts to show the following errors in replication monitor:
2015-08-12 21:48:24.156 7 transaction(s) with 11 command(s) were delivered. 2015-08-12 21:49:04.233 5 transaction(s) with 7 command(s) were delivered. 2015-08-12 21:50:04.355 No replicated transactions are available. 2015-08-12 21:50:09.378 13 transaction(s) with 17 command(s) were delivered. 2015-08-12 21:51:12.168 No replicated transactions are available. 2015-08-12 21:52:12.306 No replicated transactions are available. 2015-08-12 21:52:32.337
We had a strange incident with our Database Mail today. We use sp_send_DBMail to send mail from stored procedures in our SQL Server 2012 (11.0.2138). There are 7 profiles available for use by different databases. The actual stored procedures are called by Web apps using a connection string that has a specific SQL user identified.
Last night app 1 was using profile 1 like usual and app 3 was using profile 3 like usual. This this morning (7 hours later) App 3 was sending from profile 1 everytime a call to sp_send_dbmail was made. Not good. App 1 was still correctly sending from profile 1.
We ran a call to sp_send_dbmail from a query window using profile 3 and it sent using the correct profile 3. We used the Database Mail right-click option of "Send Test Email..." to both profiles and they worked as expected.
Our investigation showed that in the Database Mail configuration wizard "Manage Profile Security" section Private Profiles tab... the User name that is used to call the stored procedures from web app 3's sp_send_DBMail did not have access to profile 3 anymore. It did have access to 5 of the other 7 profiles. 1 of the no access profiles was legitimate #7.
The only thing that has been changed lately was, 2 days ago a mail profile was deleted as it was no longer used. We used the Database Mail Configuration Wizard to remove it.
This script can be used to shrink a database file in small increments until it reaches a target free space size.
It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment until it reaches the target free space.
It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.
-- Shrink_DB_File.sql /* This script is used to shrink a database file in increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk. 1. Set @DBFileName to the name of database file to shrink. 2. Set @TargetFreeMB to the desired file free space in MB after shrink. 3. Set @ShrinkIncrementMB to the increment to shrink file by in MB 4. Run the script */
declare @DBFileName sysname declare @TargetFreeMB int declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink set @DBFileName = 'MyDatabaseFileName'
-- Set Desired file free space in MB after shrink set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB set @ShrinkIncrementMB = 50
-- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB]= convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName]= a.name from sysfiles a
declare @sql varchar(8000) declare @SizeMB int declare @UsedMB int
-- Get current file size in MB select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
-- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB]= convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName]= a.name from sysfiles a
I am very new to XML file area in ssis, need to load several files into one DB . Details requirement as below.
- XML file is loaded into DW_EXTERN, and then moved to the archive with a time stamp suffix. --I Know how to move this to Archive folder but i need to move with today's (execution)date, how to do this?
- Loading should be done with usual logging using batches etc. --Done
- We will receive one xml file per day containíng all changes since the previous file. The file is date stamped, showing the period of time the file contains.
- Initially we will receive 700 files (2 years of data). The package must support more than one file in the input queue, and able to load them in the correct order. -- using for each loop to loop thru all files ?As per my knowledge files will sorted and stored in a folder with date modified so, those will get executed in that order, right?
- The package must be able to reload a period. Delete all future records compared to the current file before loading the file. -- Need to delete > source file date from target table and load the file
Here, i have couple of doubts.
1) How to select source file name with date modified value from source folder using For each loop
I am created a SSIS package to export data. I am exporting query data to a flat file to a different server. I tried to use the UNC path and it failed saying could not access the file. How can I create a SSIS package to export data from one server to another?
Can someone please help me with the following error?
I am trying to create a trigger that copies data from a table in one database to another. However I am getting the error 'Cannot create trigger on databasename.dbo.tablename as the target is not in the current database.
Below is the SQL I am using to create the trigger:
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET. I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page. Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer. Cheers ~ J
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
I have created Database projects in VS 2013 using SSDT. I have been mostly successful in creating and building the projects without any errors/warnings.
However for one of the databases in the project, when i do schema compare to apply the changes from a SQL Server Database to a Database Project in VS, code changes are not applied to the database project.
After i select the Update option in Schema compare window, I'm getting the following message
"Target update complete. Press Compere to refresh the comparison."
Even tough the message implies that target database is updated successfully, I do not see the objects i selected in schema compare being added to the target database project.
I see the following warning
"Target update: Could not update script for element 'dbo'"
I have 9 Database projects in the Solution and I'm able to apply changes to 8 of the database projects through schema compare successfully. I get the same warning after schema compare for all database projects.
I have same project level setting for all database projects in the solution. I'm using Visual Studio 2013 Premium Update 5 SQL Server Data Tools 12.0.41012.0
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
Hey, ive got a listof events that have occured on my site, updates etc. and Im trying to show how long ago the updates happened. For instance, say the date of an update is : 16/10/2007 15:16:03 I want the Label to say "Happened over 2 months ago" etc.Now ive tryed to use an IF statment but I cant seem to get it right : DateTime dt = Convert.ToDateTime("16/10/2007 15:16:03"); if(dt.ToShortDateString() == DateTime.Now.ToShortDateString()) { //happened today if(dt.ToShortTimeString() == DateTime.Now.ToShortTimeString()||dt.ToShortTimeString() < DateTime.Now.AddMinutes(-1).ToShortTimeString()) { //happened within a minute UpdateLabel.Text = "About A Minute Ago"; } } else if (dt.ToShortTimeString() == DateTime.Now.AddDays(-1).ToShortTimeString()) { //happened yesturday UpdateLabel.Text = "Updated Yesturday"; } else if (dt.ToShortDateString() == DateTime.Now.ToShortDateString() || dt.ToShortDateString <= DateTime.Now.AddDays(-7).ToShortDateString()) { UpdateLabel.Text = "Updated Last Week"; }Any ideas where Im going wrong? Im probally staring it straight in the face, but I cant see it. Thanks in advance John
In MOC (SQL 7.0 Implemetation), it says there will be rollback, but in my humbel opinion, there should be nothing happened.
I think there was no dirty writing in this case. No data pages written, no log pages written to disk. Am I right? Then, there might be nothing happened instead of ROLLBACK.
If I am wrong, would you please let me know what is wrong in my thougt?
I am using SQL Server 6.5, when two or more independent applications put transactions through SQL, it locks up. Example of locks up.
When the OrderLines table is locked, then I put the following (Select * from OrderLines) then the query does not return any values, the world goes round and round, the only way out is to shut down and cross my fingers whilst SQL goes into recovery mode.
I have read through some of the documentation, such as deadlocks, livelocks and lock starvation but it say none of these will lock the whole machine. But somehow simultaneous transactions can, and the current activity dialog goes red, bright red.
I have a SQL 2000 server(sp2) on Win2000 SP2. My largest database is about 5 gig. Log shipping went astray and had to reinitialize. WHen I did the part where it actually backups the init db to disk failed with the following
3041 : BACKUP failed to complete the command BACKUP DATABASE [Development] TO DISK = N'G:SQLBACKUPdevlog' WITH INIT , NOUNLOAD , NAME = N'Development backup', NOSKIP , STATS = 10, NOFORMAT
In the NT event log I got the following
{Lost Delayed-Write Data} The system was attempting to transfer file data from buffers to DeviceLanmanRedirector. The write operation failed, and only some of the data may have been written to the file.
