Schema Changes When DROPS Are Necessary
Oct 9, 2007
We have a database that we are preparing to set up Merge replication on. We often make schema changes via T-SQL, many of these changes are made to tables on which an ALTER TABLE statement will not do (rather the creation of a temporary table, copying of the data, deleting the original table then renaming the temp table).
My question is how this will affect Merge replication. I have not been able to find anything that is very clear on this. From what I gather, if a table needs to be dropped that is participating in a merge replication, I need to go through the manual process (manual, as in calling the necessary system stored procedures) of removing the the article from any publication (and subsequent filtering), make the modifications, then re-add it to the publication and filtering.
Is this correct? If so, a new snapshot needs to be created, correct? If so, I have a follow-up question regarding that snapshot.
If a new snapshot needs to be created, what happens during replication/synchronization? Meaning, since it is a new snapshot, does the client (subscriber) see the whole thing as new or is it smart enough to recognize that only that one table i have changed needs to be synched?
I am quite new to replication, as you can tell, so please forgive the rambling. I ask these questions because I have heard different answers on both questions...so I would like to get the correct answers.
Greatly appreciated...
View 4 Replies
ADVERTISEMENT
Jan 12, 2006
Is there a way to have SSIS monitor a folder for file drops? I have been unable to determine which object/task to use for this. We need the ability to have it monitor for files being dumped by other systems, pick those up and then process them. Thanks for your assistance.
- DeKlown
View 6 Replies
View Related
Jul 16, 2007
We've implemented mirroring between two identical servers. Sporadically, the mirroring session will drop and the ERRORLOG reflects the errors below at the exact time the mirroring session becomes suspended. We do not manage our back end network since we use a dedicated hosting environment at a remote location. Is this issue solely caused by network connectivity issues, or are there other factors at work?
2007-07-16 04:24:37.24 spid23s Error: 1453, Severity: 16, State: 1.
2007-07-16 04:24:37.24 spid23s 'TCP://192.168.215.92:5022', the remote mirroring partner for database 'evestment', encountered error 1204, status 4, severity 19. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.
2007-07-16 04:24:48.46 spid23s Error: 1479, Severity: 16, State: 1.
2007-07-16 04:24:48.46 spid23s The mirroring connection to "TCP://192.168.215.92:5022" has timed out for database "evestment" after 10 seconds without a response. Check the service and network connections.
View 1 Replies
View Related
Mar 7, 2007
I've written an SSIS package which has two DataFlow tasks running simultaneously, importing the data from two separate ASCII files into two staging/prep tables: one Tasks imports records into an ORDER table while the other Task imports the ITEM-level records into an ITEM table.
Starting several days ago, the ITEM DF Task began importing a partial set of records even though the ASCII file clearly showed more records were available.
For example, in today's processing, here is the message from SSIS: << [ITEM Ascii File [1695]] Information: The total number of data rows processed for file "\webserv01globalscapeusr vr logstandarditem.txt" is 217361. >> yet i only found 10,401 rows -- and they all appeared to be the bottom 10,401 rows of the incoming ASCII file.
I cannot see any other untoward messages about SSIS encountering a problem.
There are no Error Output restrictions on the ASCII file.
The ASCII row preceding the 10,401 rows i DID get looks OK.
Can anyone suggest a possible place to look which might explain this "jumping" over the nearly 207,000 rows?
Thanks very much.
Seth J Hersh
View 7 Replies
View Related
Jul 19, 2006
XML looks like this:
<?xml version="1.0" standalone="yes" ?>
<hist key="ABC">
<r date="2006/04/21" time="08:53:04" seq="1029">123</r>
<r date="2006/04/21" time="09:21:40" seq="1613">123.25</r>
<r date="2006/04/21" time="09:37:22" seq=" 89">194.21</r>
<r date="2006/04/21" time="09:37:22" seq=" 91">194.21</r>
<r date="2006/04/21" time="09:37:22" seq=" 93">194.22</r>
<r date="2006/04/21" time="09:37:22" seq=" 95">194.22</r>
</hist>
In SSIS it reads all the date, time, seq etc but I lose that super critical "key" in the outputted data flow. If I look at the XSD in visual studio it shows 2 tables related via a "nested heirarchy"... "hist" and "r" with "key" being an attribute in the hist table and all the rest as attributes in the "r" table. However in SSIS it only shows one table so I can't get at that key :(
Any idea how to remedy? I keep messing around with the XSD file but that hasn't gotten me anywhere thus far...
Thanks
View 9 Replies
View Related
Mar 23, 2007
I ought to know how to do this, but it escapes me at the moment. I need to write an insert statement for a table that will be based on a complex select query. The select query may return rows that are already in the target table. In that case, I don't want duplicates created, but I don't want the query to error, either. I can't remember how to set that up.
View 3 Replies
View Related
Apr 28, 2008
My server (SQL 2005 SP2) typically runs with a procedure cache usage of about 92% or higher... lately it seems like at some point in time during the day it just drops to anywhere between 50% and 65%... with this comes horrible server performance and many snowball effects. If I clear the procedure cache it will go up only about 10% for a minute or two. The only way I can get it to recover completely seems to be restarting the SQL service. Then it will be fine till the next incident. The database is a read only (not set to read only but no updates other than replication). and the same SPs are run over and over and over throughout the day. also did notice that the compiles of the SPs goes up drastically at this point also. not sure if this is part of the cause or part of the effect.
CPU is normal. response from anything (even sp_who) is slow.
i do not understand the way procedure cache works completely so I thought I would ask for some direction.
Any ideas where to look or where to start???
Any thing I can do to catch this when it happens would be great.
thanks a head of time.
View 34 Replies
View Related
Dec 31, 2003
Hi all,
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success.
Anyone have some info on this?
Thanks.
View 8 Replies
View Related
Aug 28, 2007
Does anyone have a good strategy or technique for preventing database drops in SQL Server 2000? I know in 2005 DDL triggers rock, but in 2000 what can you do to audit who drops a database why keeping the same permissions intact.
Jason
View 3 Replies
View Related
Mar 12, 2007
We are having some unusal problems with an upgrade to SQL Server 2005. I've come across two other people who have had this type of issue but I haven't found a resolution. We are running SQL Server 2000 in production and we have installed SQL Server 2005 on new hardware. We have been working for about a week to verify that all of out existing applications will work properly with SQL 2005.
We have several old VB applications that open a connection to the database and reuse it for days on end. We can test these apps against the production 2000 server and they run fine, when they connect to the 2005 server the connections work fine for 5 - 15 minutes then we get an error trying to execute a command and are told that the connection doesn't exist. (One interesting note, there is only one line of code in the whole program that connects to the database but we have seen 3+ connections from the app when we check in Management Studio for SQL 2005.)
Just to get though the tests we put in some error handling to reconnect if the connection is lost. When we do this the application ends up re-connecting only once, the new connection never has a failure even after hours of testing and we see a 300% performance improvement.
We can reliably cause this problem by stopping the sql server, starting it, waiting a few minutes, then running our app. After one run of the app the problem seems to go away for a while and we can't re-create it nearly as consistantly. Only occasionally will the initial connection fail and every time it does it will be after 5-10 minutes of activity.
Both machines are connected to the same switch and we have ruled out networking issues. We are only seeing this problem with VB6 apps, the VS2005 apps run great. We are looking for the cause of this connection behavior and a fix for it because we don't have the time to fix code in dozens of VB apps because the connections are not stable. Granted the practice of holding a connection open isn't good to begin with but with legacy code you have to work with what you're dealt.
Any thoughts as to why ADO connections from VB6 to SQL 2005 would not be stable?
View 4 Replies
View Related
Sep 11, 2007
Please forgive my ignorance, I am by no means a SQL Expert, but have encountered a strange issue.
I have 6 SQL Servers, Primarily SQL 2005 (one older SQL 2000) all loaded on Windows Server 2003 SP1.
We use the servers for a proprietary database that we created which is the backend to a software package we sell.
The issue I have is: We have added a Security account to the servers, and in one case we have granted rights for this account to the TempDB system database. However, whenever we restart this server SQL drops this user account, thus severing connectivity for the app that is relying on that account.
I have set the account as DB-Owner etc, but nothing sems to keep it on re-start.
Any input would be greatly appreciated.
View 4 Replies
View Related
Oct 22, 2015
I'm using SQL Server 2012 Bi edition.
I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.
However, when I deploy the model to the server it is only deploying 76,500 records for those tables.
Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.
View 3 Replies
View Related
May 22, 2006
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
View 1 Replies
View Related
Jul 19, 2006
We are using BCP in a Process task. The value for the path to BCP's error log requires double quotes around it. We initially put this value in a configuration file, and that worked fine. Yesterday, we eliminated the config file and tried to use the "Set Values" tab of the SSIS SQL Server Agent job to pass in this value.
The package variable was assigned the value of the path -- all except for the leading double quote. The closing quotation mark was included in the value.
We tried adding a second double quote at the beginning of the value, but that caused the job to fail immediately.
For now we will work around this problem by putting the quotes around the value in a Script task inside the package.
Has anyone else encountered this problem?
Thanks,
Ron
View 2 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
Jul 28, 2015
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
View 0 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Apr 19, 2006
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies
View Related
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Apr 3, 2008
Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
Any words of wisdom gratefully recieved,
Howard Stiles
View 1 Replies
View Related
Jun 25, 2001
SQL 7.0 SP3 replicating a database from a publisher to a SQL 7.0 SP 3 subscriber.
Is there a way to change publisher schema without stopping the replication? Example Add a 'bit' column to a table with default of '0'.
View 1 Replies
View Related
Jun 3, 2004
Hi all,
I want to set up a db for an e-commerce site. I need to know how to set up the db correctly with out getting anything mixed up ... 'cos I never done an e-commerce db b4:
I need to know where and how to store the passwords, the product pictures and the customers delivery address which is different from the billing address.
The tables are as follows...
--Customer--
CustID
Email (email & pwd is for login)
password (will this be secure here)
Name
Address (should it all be in one column or firstline,secondline,zipcode columns)
DeliveryAddress
--Product--
ProductID
ItemName
Catogary
Price
SellingPrice
Quantity
ItemsPicture...(not sure where to link the pics to)
DistributorsID (the warehouse who dispatches the item)
--Distributors--
DistributorsID
Name
Company
Address
It would be much appreciated if you could share some info and tips for how to set it up all correctly.
Tools I'll be using are, ASP.NET(C#), ADO.NET, MSSQL (Stored procedures)
Thanks In Advance
View 4 Replies
View Related
May 28, 2007
HI All,
What Is Schema in sql Server 2005 ?
View 4 Replies
View Related
Nov 23, 2007
Good afternoon
could someone tell what is a schema is when using within SQL SERVER 2005
Many thanks
Rob
View 3 Replies
View Related
Aug 26, 2005
I know in SQL Server the terms Database and Catalog are usedinterchangably. But a table is also assigned a schema. As seen in theINFORMATION_SCHEMA.Tables View. I don't get what this schema qualifieris all about. Like if a table has a schema of dbo.Can someone explain the relationship the schema has and what it is?Thanks.
View 10 Replies
View Related
Nov 8, 2006
I have setup merge replication between two machines and the data seems to be updating when changed on either side ok. However I am now looking at how I can handle schema changes. I have found on msdn (although not tried) stored procedures that will add and remove columns, but what happens if I want to delete a table for example.
At the moment I can deselect the table being deleted from being published and then delete it or modify it in Management Studio, which then means I need to create a new snapshot which can take sometime before the change is updated on the subscriber.
Is there a better way to tackle schema updates that I have not found yet? Also, how will the subscriber cope if someone starts modifying data while synchronisation to the new snapshot is going on.
I am using SQL 2005 by the way.
Thanks
John
View 1 Replies
View Related
Oct 17, 2006
Hi,
I am new to SQL Server and started it with SQL Server Express.
I tried unfruitful to know about 'Schema' in SQL Server.
I have to mention it as prefix for table name in SQL query. But if it is in "dbo" schema, it is not required.
Please tell me about it..
Thanks
View 1 Replies
View Related
Nov 19, 2006
I wrote a website that hits an SQL Server DB using queries that directly acces the table name with no Schema references. (Ex: SELECT * FROM tblHelpNotes;) I have also backed the file and and somehow (I have forgotten how I managed to import it.) imported it into SQL Server Express to run at home. The problem I am having is that in order to query at home I HAVE to use the Schema or it won't work. (Ex: SELECT * FROM dbo123456.tblHelpNotes;) How can I change this so I can just hit the table directly? I have tried miserable to set up a user that uses the default schema of db123456 but I can't seem to get it to work. Any idea's to point me in the right direction?
View 2 Replies
View Related
Jun 19, 2000
Hi,
i need to move the data and schema from 6.5 to 7.0. could you please tell me the most simple way.
thank you
raju
View 1 Replies
View Related
Dec 27, 2004
Hi,
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Thanks
View 4 Replies
View Related
Apr 28, 2005
What would be the best way to get the Schema from a complete database.
Thanks.
View 1 Replies
View Related
