SQL Server Thinks True Is A Column Name
May 12, 2007
Hello SQL Server Experts,
I'm upgrading an application that uses a VBScript/ASP front-end and MS Access Backend. The application has many points expecting fields to be true or false..
E.g. SELECT * FROM MyTable WHERE Active=True
However, since SQL Server requires 0 or 1 for bit fields, this query keeps failing. SQL Server (2005 Express) thinks True is a column name and is not automatically converting "True" to 1.
It would be so difficult to re-write the application to do this at the VB level, is there a way to make SQL server do this conversion automatically ?
Thanks for any assistance available.
Sunny
View 1 Replies
ADVERTISEMENT
Sep 13, 2007
Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
View 4 Replies
View Related
Apr 22, 2008
I have a large SQL script that creates about 20 stored procedures. It worked fine up until our dev SQL server died and was rebuilt. Now, when I try and run the entire script, it complains that I am redeclaring the parameters multiple times in a batch:
The variable name has already been declared. Variable names must be unique within a query batch or stored procedure. (see snippet below)
My feeling is it is a setting on the server but I didn't find anything on Google. Anyone know what it might be? How can it be matching 2 parameters in DIFFERENT stored Procedures?
Thanks in advance.
-------------------SNIPPET------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_PartyInviteMessage_Create
@PartyInviteID int,
@PartyInviteTypeID int,
@PartyInviteRecipientTypeID int,
@PartyID int,
@TemplateSetID int,
@FromEmail varchar(255),
@ToEmail varchar(255),
@Subject nvarchar(255),
@Body ntext,
@Enabled bit
AS
BEGIN
INSERT INTO PartyInviteMessage (PartyInviteID, PartyInviteTypeID, PartyInviteRecipientTypeID, PartyID, TemplateSetID, FromEmail, ToEmail, Subject, Body, Enabled)
VALUES (@PartyInviteID, @PartyInviteTypeID, @PartyInviteRecipientTypeID, @PartyID, @TemplateSetID, @FromEmail, @ToEmail, @Subject, @Body, @Enabled)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_PartyInviteMessage_Delete
@PartyInviteID int,
@PartyInviteTypeID int,
@PartyInviteRecipientTypeID int,
@PartyID int
AS
BEGIN
DELETE FROM PartyInviteMessage
where
PartyInviteID = @PartyInviteID
AND PartyInviteTypeID = @PartyInviteTypeID
AND PartyInviteRecipientTypeID = @PartyInviteRecipientTypeID
AND PartyID = @PartyID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------END SNIPPET-----------------------------------
View 8 Replies
View Related
Jul 31, 2015
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 Related
Jul 23, 2005
I hosed myself, please help me somebody.We have a development server (SQL7) where the database is a restored copy ofproduction (where we use replication, but not in the developmentenvironment).While we rebuild the server recently (so we could move off of NT onto 2000),everything looked to be working fine.But I recently, messed up and did a massive update filling the transactionlog of the development server. Now I can't update data in development.I tried to do my usual:BACKUP LOG LAMS WITH Truncate_onlyonly to get a message:The log was not truncated because records at the beginning of the log arepending replication. Ensure the Log Reader Agent is running or usesp_repldone to mark transactions as distributed.Did a little research into BOL and tried to call the following two procs:exec sp_repldone@xactid = null ,@xact_seqno = null ,@reset = 1exec sp_repltransBoth of which gave me an error of :The database is not published.Which it isn't as we don't run replication in the development environment.I stopped and restarted the SQL Service (as that has cleared issues in thepast), but didn't reboot the server.How can I make the database not think It's replicated (or that its caughtup?).I was thinking, would I be even be able to do another restore of a morerecent copy of Production?Sincerely ...Depressed in Delaware
View 1 Replies
View Related
Jun 11, 2008
DECLARE @UserEmail as Varchar(50)
DECLARE @UserZipPassword as Varchar(50)
DECLARE @CoupMember as int
DECLARE @member As Bit
--Check if Email Exists…. If yes Then
--
SELECT @RewardsMember = COUNT(*),
@member =CASE M.EMAIL
WHEN ((LEN(EMAIL)) <> 1)
THEN 1
WHEN (LEN(EMAIL)) IS NULL THEN 0
END
FROM MemberTable M
Trying to get information from table for User based on data provided. What I am trying to do up there is check to see if User provided correct Email, if User provided right password and if User provided right zip code. Basically, I need to return bit values to my client application from this. Ran into some problem with my SELECT CASE statement up there. Need help please. Thanks in Advance.
View 3 Replies
View Related
Feb 13, 2013
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code:
SqlCommand command = new SqlCommand();
SqlConnection conn = database.TypeLibraryDatabaseConnection;
command.Connection = conn;
// create the command to create the stored procedure
command.CommandText = database.GetProcedure(node.Name);
// create the stored proc in the database
try
{
command.ExecuteNonQuery();
}
catch
{
}
command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
View 3 Replies
View Related
Sep 8, 2006
Hi all,I use the following connectionstring connect to sqlservserver=(local); database=mydata;Integrated Security=trueIf I tried browse the website in VS Web Developer 2005 environment, it's ok. After I compiled the site and access through IIS server, can not connect to sqlserver , I need to use login and password to connect. How can I connect wihtout using LogID and password.Thanks
View 2 Replies
View Related
Nov 7, 2007
THE WINDOWS LOGIN AND DOMAIN PASSWORD GIVES AN USER SA ACCESS RIGHT FROM THE START AS SOON AS THEY INSTALL AND RUN MS SQL SERVER.
IS THIS CORRECT, AND IF ITS NOT, WHAT ARE THE REAL PERMISSION THAT TIE IN WITH WINDOWS AND ITS AUTHENTICATION AND HOW IT AFFECTS MICROSOFT SQL SERVER.
PLEASE HELP.
THANKS and Best Regards,
Dirce
View 3 Replies
View Related
Apr 14, 2006
We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com
View 17 Replies
View Related
Jul 19, 2007
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
View 8 Replies
View Related
Sep 26, 2007
This problem happens on this particular machine and doesn't happen on other machines that I have. And I think this problem belongs to this forum. Because the problem is triggered by a special setting in the connection string.
I followed the steps of threads including Xinwei Hong's instruction for fixing this error(26) but has no luck.
The problem occurs when I call SqlConnection.Open().
I am using window xp, sql2000. the named instance is on local machine, my connection string is like
"Data Source=MSL-1417762\SQL2000;Initial Catalog=PopulateForeignKeys_MSL_1417762_2_5984;Integrated Security=True;Pooling=False;Asynchronous Processing=true"
Whenever I change Asynchronous Processing=true into Asynchronous Processing=false, the problem is gone.
This problem also occurs when I use Visual Studio - Server Explorer - Add Connection dialog - Advanced button - Advanced Properties window where you can set Asynchronous Processing property to true.
Can you please help?
Thanks,
View 1 Replies
View Related
Jul 7, 2006
Microsoft E-Learning products are currently available for purchase only within North America at this time
Jezz, bad luck I am currently living in Spain...
View 1 Replies
View Related
Oct 20, 2007
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?
View 4 Replies
View Related
Oct 16, 2006
I am designing a package performing some data imports from a text file to some tables, passing by a temporary table.
My specific requirements are:
initially the package checks if the input file exists, if it doesn't it will not continue.I have implemented this with a Script Task, and if the
file doesn't exist I fail the all package (Dts.TaskResult =
Dts.Results.Failure). Is this the right way?
after copying the data into a temporary table, it checks via SQL if some conditions are verified, if not it will not continue.I would have liked to do it via an SQL Task, but I don't know how to stop the package if the conditions are not what expected. Anybody can help?
Thank you.
View 2 Replies
View Related
May 13, 2007
All
Can I ask what data type i use for a true false response (Boolean) in my table?
Thanks
Gibbo
View 1 Replies
View Related
May 3, 2007
This is stupid, I used to be able to do this all the time by mistake now I can't do it on purpose
I want to be able to return a full list of matching records when only one is true
LikeRow 1, ID_1, falseRow 2, ID_1, falseRow 3, ID_1, trueRow 4, ID_2, falseRow 5, ID_2, trueRow 6, ID_2, false
I currently getRow 3, ID_1, trueRow 5, ID_2, true
View 3 Replies
View Related
Mar 10, 2008
Hi all, I am trying to access a sql database and if the userid exists in the database to the let me query another statement within an IF block to check another statement within the data, however I cannot seem to get it to work. I need something like sql.row.count != 0 within the 2nd IF statement below. What can I do?Thanks in advance.Jason using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnStart_Click(object sender, ImageClickEventArgs e) { if (Session["userid"] == null) { Response.Redirect("accessdenied.aspx"); } else { //Response.Redirect("page1.aspx"); SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); objConnect.Open(); SqlCommand cmd = new SqlCommand("SELECT user_id FROM users WHERE user_id = '" + Session["userid"] + "'"); if (cmd == true) { Response.Redirect("page5.aspx"); } objConnect.Close(); } }}
View 7 Replies
View Related
Feb 9, 2004
what does this imply Trusted_Connection=true parameter in the Connection String signify?
Secondly how can I make users with Windows Authentication Login to work in SQL Server Connection String?
Thanks in advance,
Neeraj.
View 1 Replies
View Related
Mar 17, 2006
hi
I just created a DB , & ran sp_dboption , it showed me that the trunc. log on chkpt. is true .....
What if i set the recovery model of this DB to full , would I be able to recover the DB to a specific point in time ,
or since trunc. log on chkpt. is true.. I would not be able to collec the transaction log backups ???
Cant understand this concept , need some link to read this stuff ....
Thanks
View 1 Replies
View Related
Mar 20, 2008
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
View 7 Replies
View Related
Apr 23, 2008
Hi,
we've got this problem with some particular jobs: they look as they
ran correctly, but actually they didn't made it all through their
duties.
The problem is that this job is calling a sequence of DTS, where there
is a DTS with an ActiveX control which modifies another DTS before
launching and some other tasks: the error happens there, .
Launched from the DTS we get the error, from the job no...any idea how
we could get the correct job information?
Thank you
Daniele
View 5 Replies
View Related
Oct 29, 2007
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.
View 1 Replies
View Related
Sep 24, 2007
Hello all,
I am trying to migrate date from Oracle 10g to SQL serve 2005 during the data transformation I get the following error
Messages
Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)
I searched internet and solution seems to be setting AlwaysUseDefaultCodePath="TRUE"
But where do you do this ?
I found this too: It's on the Properties tab of the OLE DB Source in Custom Properties section.
But still do not know where to go to set this parameter
Please help
View 4 Replies
View Related
Aug 15, 2005
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 Related
Mar 31, 2006
When accessing a web application from an intranet....
And the web app tries to make a connection to a 'SQL Server' using the sqlconnection where does ASP.NET grab user credentials?
My webpage displays web security.principal.windows.getcurrent = domainUser (displays the correct information).
But the connection to sql says Null. Where is ASP.NET grabbing (retrieving) this information from?
Thanks,
View 3 Replies
View Related
Jun 11, 2013
I have this OR in a simple query:
Code:
DECLARE @searchString nvarchar(100)
SET @searchString = 'sample'
SELECT TOP(1) * FROM user
WHERE (user.identity LIKE @searchString OR
CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0)
PS: Handwritten...
If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).
I've tried various things:
Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P
Flipping the conditions around
Adding/removing paranthesis... without any luck.
Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
View 12 Replies
View Related
Jan 30, 2004
Hi all,
I have a database filled with contracts, suppliers and administrators who administrates those contracts.
I want to make a sproc that checks the difference between the expiration_date and the current date(in months). the sproc compares this output with a given period in the contracts-table. when the output <= the given period ---> send mail to the administrator with info about the contract. and that the contract will be expired in X months.
Having a Sproc that only gets the info from the tables and compares this info is no problem, but to let the sproc send an email to the admin whose email-adress also comes from a table is a little bit to tricky for me.
I have searched the internet but i can't figure it out.
If anyone knows where I can get more info about this subject please be so kind and let me know, or anything that could help me around for the moment.
PS. I'm using SQL server 2000
Thnx in advance
StylizIt
View 12 Replies
View Related
Jun 10, 2008
Sir
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
View 4 Replies
View Related
Jul 23, 2005
We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Nov 1, 2007
I have multiple viewers on a web page and initialy would like AsyncRendering = true on all viewers, so I can have the loading message. Once the user drill-through, I hide all other viewers and change to AsyncRendering = false, so the autosizing will work.
But, with AsyncRendering = true (in code behind):
private void SetDominateViewerProperties(ReportViewer rptViewer)
{
rptViewer.AsyncRendering = false;
rptViewer.ShowToolBar = true;
rptViewer.ZoomPercent = 100;
rptViewer.Visible = true;
}
private void SetDefaultViewerProperties(ReportViewer rptViewer)
{
rptViewer.AsyncRendering = true;
rptViewer.ShowToolBar = true;
rptViewer.ZoomPercent = 100;
rptViewer.Visible = true;
}
I have only one viewer showing, even though, I can see in debug that all viewers are set to visible. In SetDefaultViewerProperties, I change rendering to false, I get multiple viewers. Are there combinations of property values I need to set?
View 1 Replies
View Related
Aug 21, 2007
I am designing a SSIS package (source=Flat files,, destination =OLEDB) which is executing fine on my system but when I try to execute it on Server 2003 (x64)
it says Error: The AcquireConenction method call to connection manager "name of conn" failed with error code 0xC0014019 and
it says can't Aquire Connections because OfflineMode is true, while as I checked several times while saving the package it is set to false...
View 7 Replies
View Related
Jun 16, 2006
I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:
Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable
adp.Fill(dt, Dts.Variables("CountryTable").Value)
It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.
Note: I have SP1 installed.
View 4 Replies
View Related
