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?
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).
View 2 Replies View RelatedAll
Can I ask what data type i use for a true false response (Boolean) in my table?
Thanks
Gibbo
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
Hello
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?
Thanks a lot.
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
View 2 Replies View RelatedSir
I want to Return 1 and 0 after update , delete , Insert statement
IF Records Effected Return 1 else return 0
Pls help me out .........Sir
Yaman
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?
TIA
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
ex
if convert(int,@x) is true then do; else do
please email me if anyone has some advice
Mike
Actually title should be returns true when should false.
I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:
IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END
The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.
When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:
INSERT INTO tblTable (IsSomething)
VALUES (False)
I've heard that a better way is to use 1 and 0, or something.
Hi everyone.
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]
Thanks
Andrew
Hai,
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.
Thanks,
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?
It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.
Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:
OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.
... when set to off, the error goes away.
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 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
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?
kushpaw
greetings
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
thank u
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?
View 3 Replies View RelatedI'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.
Hi,
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 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.
Hello,I am setting up log shipping between two SQL servers. Everything in thesetup seems to work fine. The problem I am having is that the "LogShipping Alert Job - Backup" job is failing, event though the tlogbackup copy & restore process is working fine. When I look at thehistories of both the backup and copy/restore jobs, they are successfulall the way through, I can make changes to the primary db, and see themget shipped to the secondary server, so I know log shipping itself isworking. 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 logshipping tables within msdb. I have verified that the account underwhich log shipping runs has access to all those tables, and the dbabove. The account which runs log shipping and the jobs is the samedomain account under which MSSQLServer service runs.Here is the error message from the job history -Executed as user: ROTELLACAPITALsqlserver. The log shipping sourceODSQLK02.ROTELLACAPITAL.COM.dbTradePL_LS has not backed up for 1246minutes. [SQLSTATE 42000] (Error 14420). The step failed.Any ideas why this might be failing? Any ideas how to fix or work aroundit?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedVB.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//
View 1 Replies View RelatedConfiguration: 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)
Greetings,
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.
Does anyone have a solution or advice?
Thanks,
BCB
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.
Hi,
I have a float column with Allow Nulls= false
Is it normal that when I try to set the column to 0, i get the error:
Cannot insert duplicate key in object
Please note that the column is part of a primary key and that there is no record with the value of the column 0 in the table in question.
Thank you.
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?
View 7 Replies View RelatedI 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?
The LS monitor is on the secondary server.
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?
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.
Thank you.
My kind regards,
Eng. Ahmad Alkilani