I was pointed to this forum by one of the admins who has started doing some consulting work for us. We did a major conversion over the weekend for our main production server to SQL 2005 from SQL 2000. We have used replication to go from the live OLTP server to a few dedicated report servers for all of our reporting needs. In 2000 this was pretty well established (though a pain when we had to migrate in changes; I love the 2005 schema features for replication).
The only problem we have really encountered today, the Day After, involves replication. A few minor things involve lack of knowledge (like nonclustered indexes don't replicate by default, that was an OOPS). But one thing I haven't figured out yet.
Replication is working -- we are updating three servers, and all three servers are getting updates. I have run a validation on one of them and it passed in full. However, the "undistributed commands" count never goes down. It actually keeps going up and up. I verified that by first running this on the subscriber:
select transaction_timestamp
from MSreplication_subscriptions
And taking the results of one of the three rows that came back and executing this with it:
which brought back all of the "undistributed commands". Within that list, I looked for sp_MSins_ procedures. I found a few and looked up on the subscriber the value to be inserted -- and it existed on the subscriber. So it still is in MSrepl_commands even though it has been sent out.
I am afraid that eventually the system will "think" that the process isn't working and deactivate the subscriptions. Also this is useless as a monitor. Since this is our first major replication in 2005, I am willing to bet that we are missing something. Does anyone have any advice?
I have a sql2005 transactional replication replicating several databases between 4 servers, all of the replication are working fine except 1 that is going to a sql2000 sp4 enterprise database. The undistributed commands grows by several hundred thousand records a day, yet all of the records counts match exactly. At first this server was having a lot of lock issues, so i thought maybe that was the cause, but over the weekend i dropped replication and resnapshotted it. Without any activity ocurring on this database this weekend it continued to grow at the same rate
I have a publisher set up to transactionally replicate just one table which has about 19000 rows. The snapshot agent finished and everything was OK the first day. Now (day 2) the replication monitor estimates that latency to the subscriber = 10 hours and undistributed commands>600,000 and growing each minute. How could this be when data is only inserted into this table and it has <20,000 records? Please advise...thanks!
We're testen a simple lan SQL 2005 SP2 one way transactional replication config. Data is replicated ok, but in the Replication Monitor we found some strange issues:
- The subscription is giving a latency warning. When we insert a tracer tocken the total latency is 5 secs. Is there a way to alter the warning level for a subscription??
- Undistributed Commands tab Changes do arrive at the client, but the distributed commands counter shows an increase in value for every (as far as we can tel succesfull) change
If we do insert a tracer token -wich arrives within 5secs.- the the counter is reset to zero.
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
Hello. How do I increment or decrement a date by adding/subtracting days? For example I want to add 4 days to the date today. I should get 07/31/2005 since today is 07/27/2005. And if I add 5 days then I should get 08/01/2005. Thank you.
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX) @sql = ' insert into target select from table_1_currentdate a LEFT JOIN Table_2_currentdate-1 b on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
Hi guys,I wanna ask bout the problem with my web application. I'm doing a select a statement from table 1 and and with the query results i got, i need it to store the result on table 2. How will i do this? I need your tips and suggestions.
I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands? MainQuery = "declare @MinGrades as Table(GradeID Bigint) " & _ " INSERT @MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _ " FROM CreditGrades INNER JOIN" & _ " CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _ " WHERE (CreditGrades.x0x30 = - 1 OR " & _ " CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _ " CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _ " CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _ " CreditGrades.x3xNOD >= 0) " & _ " GROUP BY CreditGradeSplits.CreditGradeGroupID" & _ " Select * from @MinGrades "
I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
I am very new to sql server and I have been reading up on the dbcc commands. For instance, DBCC SHOWCONTIG where do I execute this command? I went into the cmd prompt and opened osql and typed: 1> DBCC SHOWCONTIG 2> GO
This gives me the information for what I assume is the master database. But, how do I use this command under the other databases?
Also, I have been trying to learn how to determine when to do index maintenance by using the index tuning wizard. But, I don't know what a workload is. Can someone point me in the right direction or give me some information about this topic.
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
hi all when i set up a new subcription ,i notice in hte replication monitor thet this subscription 'uninitialize subscription' and the are 70 undelevier commands.
how can i fixed this problem,or how can i remove those undeleveirs commands?
Suppose there are two tables employee and salary and a,b are two instances of tables employee and salary.There are 20 records in each table.Then what will be the result of the following query
Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was wondering if there was any way for me to see which SQL commands were being run on the database when changed like deleting/inserting users are being applied from the Web frontend?
How can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?
Hi everyoneI created a database graphically in VS2005.Now I want the text version of those DDL(create) commands. Question is where and how are those commands stored in SqlServer I have the management studio too. Any ideas??
Please forgive me if my questions seem very simple. I'm trying to pick up vb.net, asp.net, and SQL all at the same time, and while making progress in some areas I'm finding other questions I have harder to figure out where to look for answers. First question - if I want to join 2 tables do the fields I'm joining on have to have the same name? I know they have to be of the same type, but does the name itself matter? For instance in this line of code I try to join forums objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID" Second question nagging at me, since I specify the table <msg_forums and users> before the field names <forum_moderators and user_ID> does it matter which one comes first? See, my primary table in this line is msg_forums, so do I have to specify it's field first, or does that even matter (or do I have it backwards and need to specify the other one first)? Last question, and of course the root of what's caused me to wonder about all of this. I'm getting an error similar to some of what I found in the forums, but it's slightly different in that it says there is a problem near "OR". As far as I can tell I've written this correctly, and so, I'm stumped. Here is the error followed by the code. Thanks a bunch for any assistance anyone offers. :-) Incorrect syntax near the keyword 'ON'. 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: Incorrect syntax near the keyword 'ON'. 'This time trying to learn an adapter instead of a reader, will also try a join 'Build the connectionDim StrConnection As String = (System.Configuration.ConfigurationManager.AppSettings("DbConnect")) Dim objConnection As New SqlConnection(StrConnection)Dim objdataadapter As New SqlDataAdapter() Dim objdataset As New DataSet() 'set the SelectCommand Properties objDataadapter.SelectCommand = New SqlCommand objdataadapter.SelectCommand.Connection = objConnection 'try after renaming to fields to match objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.user_ID = users.user_ID" 'Try without specifying the table 'objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON user_ID = user_ID" 'Original attempt objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID" objdataadapter.SelectCommand.CommandType = CommandType.Text 'open the database connection objConnection.Open() 'Fill the dataset with object data giving the dataset to use and the table name (primary table even when pulling from a couple)objdataadapter.Fill(objdataset, "msg_forums") 'close connection objConnection.Close() 'Set DataGridView properties grdforums.AutoGenerateColumns = True grdforums.DataSource = objdataset grdforums.DataMember = "msg_forums"End Sub End Class
