I am setting up log shipping between two SQL servers. Everything in the
setup seems to work fine. The problem I am having is that the "Log
Shipping Alert Job - Backup" job is failing, event though the tlog
backup copy & restore process is working fine. When I look at the
histories of both the backup and copy/restore jobs, they are successful
all the way through, I can make changes to the primary db, and see them
get shipped to the secondary server, so I know log shipping itself is
working. The only job that is failing is the backup alert job.
I have the secondary (destination) server setup as the monitor server.
I read that this issue may occur when the job can't access the log
shipping tables within msdb. I have verified that the account under
which log shipping runs has access to all those tables, and the db
above. The account which runs log shipping and the jobs is the same
domain account under which MSSQLServer service runs.
Here is the error message from the job history -
Executed as user: ROTELLACAPITALsqlserver. The log shipping source
ODSQLK02.ROTELLACAPITAL.COM.dbTradePL_LS has not backed up for 1246
minutes. [SQLSTATE 42000] (Error 14420). The step failed.
Any ideas why this might be failing? Any ideas how to fix or work around
it?
Thanks,
TGru
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.
How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I have a table by the name of Info, in this table I have columns with the following names FirstName, LastName, PhoneNumber, CustomerID. In the columns I have the following data FirstName = Beth LastName = Riddle PhoneNumber = 864-555-1212 CustomerID = 4 The problem is with the following statement in the .vb file. The statement keeps produceing a false value, when the desired value is Beth. TextBox1.Text = SqlDataSource1.SelectCommand = "SELECT 'FirstName' From 'Info' Where(PhoneNumber = '864-555-1212')" Please help Thanks for the information
I have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false. What would my where cluse look lile for this?
when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?
Hi, I need to check the existence of a row in a table. So i am using an if condition like set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[CheckNOAStages] @NOAID int, @StageCode nchar(20) AS BEGIN
SET NOCOUNT ON; Declare @Count int Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode if (@Count>0) Begin return 1 end else begin return 0 end
END
The stored proc is executing but on the Data Access Layer I have this Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));
Some how I am always getting false . How can I fix this? Thanks
I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?
I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?
Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen
i am use this query to select the primary field colums in a table "select Column_Name as PrimaryKeycolumn from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'tbl_Activity' and Constraint_Name like 'PK_%'"
but i want to select the fields which have a nullable=false for that i want know the information schema for null
In a script task, I prompt the user for some parameters. I use the System::InteractiveMode variable to tell me if the package was launched via user interaction or from an automated process such as a file watcher. I display the prompts only if System::InteractiveMode is true. When I run from VS 2005 then I get the prompts, and when the package runs automated (dtexec) the prompts are not shown, so all is OK. However, when the package is run from dtexecui, I do not get the prompts because it seems the InteractiveMode value is false. Shouldn't the InteractiveMode value be true when the package is run from dtexecui since it is a user interface?
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't. This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going. I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also. Any idea what I'm doing wrong? Or is this a bug? Is there a work around? Any help would be greatly appreciated.
If I use IRowsetFastLoad::Commit(FALSE) not IRowsetFastLoad::CommitCommit(TRUE), are there any limitation of IRowsetFastLoad::InsertRow(..) before Commit(FALSE)?
Now, I'm using everlastingly InsertRow(..) and Commit(FALSE) method repeatedly. A few days after, it seems not to work.
I need a way to test if a convert function will work before I process it. if it fails, I want to intercept the error and return my own error to the front end
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT); INSERT INTO #ABC VALUES ('2310947',110), ('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
VB.NET 2003 / SQLS2KThe Stored Procedure returns records within Query Analyzer.But when the Stored Procedure is called by ADO.NET ~ it produced thefollowing error message.---------------------------Exception Message: Failed to enable constraints. One or more rowscontain values violating non-null, unique, or foreign-key constraints.------------------------------------------------------Exception Source: System.Data---------------------------If I click OK past the error messages I will get data filling thedatagrid. However not as I would like to see it.Even though it returns the proper data rows and includes all thecolumns I asked for, it also returns plenty of columns I didn't ask for(all the columns of the main table) and all those columns are filledwith "null"In addition each row header contains a red exclaimation mark whch whenhovered over reads;"Column 'cmEditedBy' does not allow DBNull.Values."An interesting thing about this column 'cmEditedBy' is that there isnoting wrong with it and all rows for that column contain data.I believe this error is a mistake! But it probably indicates some otherproblem. How should I track its cause?M O R E ...Below is the code in the data layer, the stored procedure, and the datareturned within query analyzer.\'DataAdapterFriend daView041CmptCyln As New SqlDataAdapter'SqlCommandPrivate daView041CmptCyln_CmdSel As New SqlCommand'Add the commanddaView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel'SelectWith daView041CmptCyln_CmdSel.CommandType = CommandType.StoredProcedure.CommandText = "usp_View_041Cmpt_ByJobCyln".Connection = sqlConnWith daView041CmptCyln_CmdSel.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, _4, ParameterDirection.ReturnValue, False, CType(0,Byte), _CType(0, Byte), "", DataRowVersion.Current, Nothing))'Criteria.Add("@fkJob", SqlDbType.Text).Value = _"48c64a55-874d-40d0-addc-7245f5d9c118"'.Add("@fkJob", SqlDbType.Text).Value = f050View.jobIDEnd WithEnd With//\ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln(@fkJob char(36))AS SET NOCOUNT ON;SELECTJobNumber,DeviceName,ComponentName,Description,Quan,Bore,Stroke,Rod,Seconds,CylPSI,PosA,PosB,PosC,PosD,PosE,HomeIsRet,RetIsRetrac,POChecks,Regulated,FlowControl,PortSize,LoadMassFROM tbl040cmptINNER JOIN tbl030Devi ON fkDevice = pkDeviceIdINNER JOIN tbl020Proc ON fkProcess = pkProcessIdINNER JOIN tbl010Job ON fkJob = pkjobIdINNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeIdINNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeIdINNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeIdWHERE(fkJob = @fkJob)--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'AND fkComponentType = 2GO//(note - columns are wrapped)\F1111Clip DriverCylinderClip Driver_2 - Top -Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_222.1002.0001.0001.234NULL11000110011/8NPTNULLF1111ClipDriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_122.1002.0001.0001.000NULL01000110011/8NPTNULLF1111DegateCylinderDegate 1 -Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULLF1111Clip DriverCylinderClip Driver 1 -Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL//
Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1 The sendStringParameterAsUnicode has been set to false for performance reasons. However, when inserting unicode data, we would like to override the setting and send the data encoded in unicode, instead of defaulting the whole app to unicode=true and take a performance hit.
Any suggestions? We have tried the cast(? as nvarchar) function, but that did not help.
Sample code/output: String text = "u0143u0144"; sendStringParametersAsUnicode=false insert into unitable (_ntext) values (?) Inserting into databse: 143 144 (printed hex values) Read from database: 3f 3f (printed hex values)
I have a package that uses a complex expression for the MessageSource property in a Send Mail task. I receive the email that is composed based on the expression and it is formatted properly. However, the application also throws an exception complaining that the width of the expression may be too wide.
This is the error message (with the long expression represented by "...") from the exception:
Error Code: -2147192616
Error Description: The result string for expression " ... " may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
I tried wrapping the expression in SUBSTRING(expression, 1, 4000) to guarantee that the resolved expression could not cause an exception but that didn't help. Numbers lower than 4000 also do not solve the problem.
I want to replicate the foreign keys to secondary.I changed the value Copy foreign key constraints value is to True.
I changed this value at pub properties - Articles -
And then it is asking for MARK for reinitialization with the new snapshot.I clicked ok.
When I checked sync status it has given the message like initial snapshot is not yet available.I started the snapshot and the subscription started replication records.When check at pup properties the value Copy foreign key constraints again false.
After changing the value to true it is showing as False.
I have installed the Report Execution Sample reports and with it came the RSExecutionLog_Update.dtsx and instructions to enable it in a SQL Agent job. I have followed the instructions, however, where do I set the PreCompile property to false? Is there a way to pull in a dtsx file into BIDS?
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this: SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
I am trying to design a database for storing trule/false questions. I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses. So does the following tables and fields suffice to achieve that?.
Questions_table(id(autogenerated),course_id,Questi on(text type)) id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.
Response_table(id,response) Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.
So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.
I have log shipping every 15 minutes working between 2 servers, and while the secondary server reports everything ok (time since last copy, restore are all under 15 minutes), the primary server reports that the time since last backup is 7400+ minutes. I have verified that the LS backup job has no failures. How can I correct this false error state and get the primary server to start reporting actual statuses?
Hi, I have a matrix with 2 row groups and 1 column group.
CGroup1 Val1
CGroup1 Val2
Total
- RGroup1 Val1
RGroup2 Val1
In
In
Out
RGroup2 Val2
In
In
Out
- RGroup1 Val2
RGroup2 Val3
In
In
Out
RGroup2 Val4
In
In
Out
Total
Out
Out
Out
I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:
=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?
Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?
Hi, I'm using SSIS to update the destination table.
Both servers are running identical SQL Server 2005 versions.
I want to copy data and replacing existing data WITHOUT dropping the destination table first.
The SSIS package works when: 1) DropObjectsFirst = TRUE 2) CopyData = TRUE 3) ExistingData = REPLACE
The SSIS package does not work when: 1) DropObjectsFirst = FALSE 2) CopyData = TRUE 3) ExistingData = REPLACE
The error I get is the "destination table already exists". Of course, it already exists. I just want to copy/update the destination table. So, why does SSIS insist on dropping the table first?
Why does the Execute Package Task in SSIS always fail when set to execute in-process?
Having ExecuteOutOfProcess = False always results in that the package fails when run from within BI Studio.
I have also tried it from Command Line using dtexec., same results.
The problem with setting ExecuteOutOfProcess = True all the the time and running from within Bi Studio is that an enourmous amount of processes is created, none of which die because SSIS runs in Debug mode, another problem which is there doesn't seem to be a way to run it in Release mode so as not to get so many Dtexec processes, which ultimately results in having the Server die.
By Dying I mean, thrashing of the CPU & Memory. Page file to extreme limits.
And yes, the hardware should be able to take much more than what SSIS is supposed to eat. Dual 3GHz Xeon & 4GB of Ram.
