Logical XOR Operator In T-SQL (SQL Server 2000)
Sep 5, 2006
Hi all....
I was wondering why SQL Server 2000 lacks a logical XOR operator. Do you have a ready to use function that implements it?
How can I program one by myself? I know that A XOR B is equivalent to NOT A * B + A * NOT B. So I tried to create this function :
CREATE FUNCTION XOR
(
-- Add the parameters for the function here
@A BIT,
@B BIT
)
RETURNS BIT
AS
BEGIN
RETURN NOT @A AND @B OR @A + NOT @B
END
But it didn't compile.
How can I pass boolean values as parameters? any other hint?
Thanks
Jaime
View 12 Replies
ADVERTISEMENT
Aug 22, 2007
I am using a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes. I have the following condition:
LTRIM([PatientCity]) != 'Wichita Falls' && [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')
I found the && which is the logical equivalent to AND. I now need a logical operator for IN.
Any ideas or approaches on how I may do this?
View 9 Replies
View Related
Mar 16, 2008
This local test DB was doing just fine. I fire up SQL Server again, and all of a sudden I can't access it (expend the database in Management Studio). So I detach and try to run this script which was run initially to re-attach it:
USE [master]
GO
CREATE DATABASE [ABS] ON
( FILENAME = N'C:DatabaseABS.mdf' ),
( FILENAME = N'C:DatabaseABS_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'CMS' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [CMS].dbo.sp_changedbowner @loginame=N'domain estuser', @map=false
GO
and I get this error: SQL Server detected a logical consistency-based I/O error: torn page
View 1 Replies
View Related
May 22, 2008
A table in one of my databases is running very slowly. The IO is very high and below is a printout from the SET STATISTICS IO ON command run on a common query used on the table:
(4162 row(s) affected)
Table 'WebProxyLog'. Scan count 3, logical reads 873660, physical reads 3493, read-ahead reads 505939, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I have a clustered unique index and a nonclustered index on the table. I have ran SQL Profiler and opened the trace in Database Tuning Advisor, DTA displays 0% improvement suggestions. I have a number of statistics on the table and index which are all up to date and fragmentation is less than 1%. I've tried a number of variations on indexes to improve performance but to no avail. There is only one query which runs on the table, and the nonclustered index created on the table did significantly improve performance, however the query still runs at around 23 seconds. The query does bring back a large amount of data however i'm sure there is a way to bring down the IO and logical reads to improve performance.
The table and index scripts are below:
Code Snippet
-- =================== Table and Clustered index ===========================
CREATE TABLE [dbo].[WebProxyLog](
[ClientIP] [bigint] NULL,
[ClientUserName] [nvarchar](514) NULL,
[ClientAgent] [varchar](128) NULL,
[ClientAuthenticate] [smallint] NULL,
[logTime] [datetime] NULL,
[servername] [nvarchar](32) NULL,
[DestHost] [varchar](255) NULL,
[DestHostIP] [bigint] NULL,
[DestHostPort] [int] NULL,
[bytesrecvd] [bigint] NULL,
[bytessent] [bigint] NULL,
[protocol] [varchar](12) NULL,
[transport] [varchar](8) NULL,
[operation] [varchar](24) NULL,
[uri] [varchar](2048) NULL,
[mimetype] [varchar](32) NULL,
[objectsource] [smallint] NULL,
[rule] [nvarchar](128) NULL,
[SrcNetwork] [nvarchar](128) NULL,
[DstNetwork] [nvarchar](128) NULL,
[Action] [smallint] NULL,
[WebProxyLogid] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pk_webproxylog_webproxylogid] PRIMARY KEY CLUSTERED
(
[WebProxyLogid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- =================== Nonclustered Index ===========================
CREATE NONCLUSTERED INDEX [dta_ix_WebProxyLog_Kaction_clientusername_logtime_uri_mimetype_webproxylogid] ON [dbo].[WebProxyLog]
(
[Action] ASC
)
INCLUDE ( [ClientUserName],
[logTime],
[uri],
[mimetype],
[WebProxyLogid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-- =================== Query which is called regularly on the table ===========================
SELECT [User] = CASE
WHEN LEFT(clientusername,3) = domain' THEN RIGHT(clientusername,LEN(clientusername) - 3)
ELSE clientusername
END,
logtime AS [Date],
desthost AS [Site],
uri AS [Actual Site]
FROM webproxylog
WHERE CONVERT(Datetime,CONVERT(VarChar(25),logtime,106),106) BETWEEN '20 apr 2008' AND '14 may 2008'
AND(RIGHT(uri,4) NOT IN('.css','.jpg','.gif','.png','.bmp','.vbs'))
AND (RIGHT(uri,3) NOT IN('.js'))
AND LEFT(mimetype,6) = 'text/h'
AND (uri NOT LIKE '%sometext.local%')
AND (uri NOT LIKE '%sometext.co.uk%')
AND [action] = 9
AND (clientusername IN ('USERNAME'))
ORDER BY logtime ASC;
PS There are 60,078,605 rows in the table
Please help!
Many Thanks
View 6 Replies
View Related
Oct 2, 2014
We would like to benchmark our logical reads daily to show our improvement as we tune the queries over time.
I am using sys.dm_exec_query_stats summing the Physical and Logical Reads. Is this a viable option for gathering this metric? Is this a viable metric to gather?
select sum(total_physical_reads) as TotalPhyReads, sum(total_logical_reads) as TotalLogReads from sys.dm_exec_query_stats;
How best to provide performance based metrics.
View 4 Replies
View Related
Feb 27, 2015
I have a SQL Server 2008 R2 Cluster. I have moved a file to another drive by adding a new file on the drive, doing a shrinkfile with emptyfile. I have removed the old file and when I try to rename the new file to the old files old name, I get an error.
ALTER DATABASE mydb REMOVE FILE myfilename
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
ALTER DATABASE mydb
modify FILE ( NAME = temp_filename
, NEWNAME = myfilename
)
Msg 1828, Level 16, State 3, Line 4
The logical file name "myfilename" is already in use. Choose a different name.
I have completed a failover, thinking the name was being held in cache. I've also renamed to something completely different then tried to rename to the old filename.
View 9 Replies
View Related
Apr 30, 2015
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
For instance:
database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf
Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].
View 3 Replies
View Related
Mar 15, 2007
Did anyone familiar with these errors:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x1a00d2fd; actual: 0x1a02d2fd). It occurred during a read of page (1:103329) in database ID 11 at offset 0x00000032742000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAServer02.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database c (Source: MSSQLServer, Error number: 824)
Get help: http://help/824
Thank you.
TJ_1
View 17 Replies
View Related
Apr 3, 2011
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies
View Related
Aug 27, 2015
I have a server with 2 processors, 12 cores each making it a 24 core logical system.
My tempdb has 4 datafiles and 4 logfiles created.
I know creating more number of log files in tempdb may not make sense, but how many tempdb data files are supposed to be created?
Should it be 24?
View 4 Replies
View Related
Oct 3, 2007
I am migrating a classic ASP application that ran on Access to ASP.NET with SQL Server 2005 DB. I am having some trouble getting a query to work. It runs part of our courses section of the web site.
SELECT CREDIT_DATA_LOCCODE.Loc_Code, CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Fiscal, CREDIT_DATA_COURSE.Term, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Status_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Course_Number2, CREDIT_DATA_COURSE.Section_Number, CREDIT_DATA_COURSE.Course_Title, CREDIT_DATA_COURSE.Credits, CREDIT_DATA_COURSE.Credits_Other, CREDIT_DATA_COURSE.Web_Website_link, CREDIT_DATA_COURSE.Web_Description, CREDIT_DATA_COURSE.Web_Instructor_1, CREDIT_DATA_COURSE.Web_Instructor_2, CREDIT_DATA_COURSE.Web_Location, CREDIT_DATA_COURSE.Web_LocCode, CREDIT_DATA_COURSE.Web_Dates, CREDIT_DATA_COURSE.Web_ETV_Dates_Times, CREDIT_DATA_COURSE.Web_ETN_Dates_Times, CREDIT_DATA_COURSE.Web_Times, CREDIT_DATA_COURSE.Enrollment_Limit, CREDIT_DATA_COURSE.Web_Icon_1, CREDIT_DATA_COURSE.Web_Icon_2, CREDIT_DATA_COURSE.Web_Note, CREDIT_DATA_COURSE.Web_Prereq, CREDIT_DATA_COURSE.Web_Special_Message, CREDIT_DATA_COURSE.Web_Alt_Major_Code, CREDIT_DATA_COURSE.Web_Alt_Course_Number1, CREDIT_DATA_COURSE.Web_Alt_Course_Number2, CREDIT_DATA_COURSE.Web_Alt_Section_Number, CREDIT_DATA_COURSE.Deleted, CREDIT_DATA_COURSE.Update_DateFROM CREDIT_DATA_LOCCODE INNER JOIN CREDIT_DATA_COURSE ON CREDIT_DATA_LOCCODE.Loc_Code = CREDIT_DATA_COURSE.Web_LocCode WHERE (CREDIT_DATA_COURSE.Fiscal = N'078') AND (CREDIT_DATA_COURSE.Term = N'10') AND (CREDIT_DATA_COURSE.Status_Code <> 5) ORDER BY CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Section_Number
But when I run this query with the ORDER BY Clause in place against the SQL 5 tables Sheila dumps into I get this Error:
View 5 Replies
View Related
Jul 6, 2007
If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0
How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:
SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0
It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.
View 8 Replies
View Related
Jan 9, 2002
I'm trying to get a tree structure from a table. Ex : Security table.
How can I accomplish that task in SQL Server?? I will do it using Level and connect by operators in Oracle to accomplish the task.
Your reply in this regard is highly appreciated.
Thanks,
Navy
View 1 Replies
View Related
Jun 2, 2004
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
View 3 Replies
View Related
May 18, 2004
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
View 3 Replies
View Related
May 1, 2015
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO
View 8 Replies
View Related
May 2, 2015
I have a text box on a web application that allows the user to type what words they want to look. In the first version of the web application the user wrote the words he wanted and was wanted on the table by CONTAINS various words with the OR operator . What I wanted now was to allow the user to write several words, but with a particularity . The words that were inside '' was like a word.
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
What I meant was that even using the full-text functionality. I'm using CONTAINS . But before the CONTAISN was :
SELECT Description FROM Production.ProductDescription WHERE CONTAINS ( Description, PRODUCT )
Since PRODUCT and was researched by words such as ORANGE LEMON OR OR OR BANANA APPLE OR PEAR OR PEACH .
What is wanted now the words that come from the WEB application within '' stay as if it were the AND operator then was :
Product = '' ORANGE LEMON ' BANANA APPLE ' PEACH PEAR ''
PRODUCT = ' ( ORANGE AND LEMON ) OR BANANA OR APPLE OR ( PEACH AND PEAR) '
View 3 Replies
View Related
Sep 12, 2015
how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'
View 2 Replies
View Related
Oct 29, 2015
Below doesn't work for varchar column?
select sum(cast(Enter_your_field_name as int)) from Table_name
View 5 Replies
View Related
Nov 11, 2006
Greetings,
I have a problem, i am upgrading a system that uses Oracle databases for using SQL Server 2005 databases. The thing is that the operator (+) (involved in many queries) is from Oracle and operators(*= and =*) are not supported in SQL Server 2005 (and not comply with the Ansi standard) i need help translating these queries. An example would be like this:
SELECT ...
FROM IBA_MPACCIONESXREQ, IBA_MPPERMISOS, IBA_MPENTIDADES, IBA_MPREQUERIMIENTOS, IBA_MPPROCEDENCIAS, IBA_LOCALIZACIONES, IBA_MPPROCEDXLOC, IBA_USUARIOS
WHERE IBA_LOCALIZACIONES.ID_LOCACLIZACION = IBA_MPPROCEDXLOC.ID_LOCACLIZACION
AND IBA_MPPROCEDENCIAS.IDPROCEDENCIA = IBA_MPPROCEDXLOC.IDPROCEDENCIA
AND IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION = IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION
AND IBA_MPENTIDADES.IDENTIDAD (+) = IBA_MPPROCEDXLOC.IDENTIDAD
AND IBA_MPPERMISOS.IDPERMISO (+) = IBA_MPPROCEDXLOC.IDPERMISO
AND IBA_MPACCIONESXREQ.IDREQUERIMIENTO = IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
ORDER BY ...
I didnt include the SELECT and ORDER BY text because i dont think its important. Also i know that you have to move the outer joins to the FROM clause but i didnt manag to get it. Could you help me? thank you in advance.
Sincerely,
Fernando Martinez
View 8 Replies
View Related
Apr 8, 2015
I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.
1) I created an operator - me.
2) I created a job which runs a query.
INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC
3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.
4) I've set the alert to email and delay is between responses 2 minutes.
It history tables says number of occurences is 18964. However, I don't receive an email.
Shouldn't the alert send an email? Do I need to include email code in the job?
View 6 Replies
View Related
May 25, 2010
I've been working with Database Mail for some time but I haven't seen this one before. I have a maintenance plan that does the following:
1. Check database integrity
On Success:
2. Perform backups
On Success:
3. Perform a maintenance cleanup
On Success:
4. Notify operator of success
Steps 1 - 3 are also linked (via On Failure arrows) to a singular Notify Operator of Failure task.
The maintenance plan does exactly what I want it to, and if everything goes correctly, it successfully sends an email to an operator.
If steps 1 - 3 fail, the job ends in an error state, but does not trigger the Notify Operator of Failure task.
I was able to recreate the problem by creating another maintenance plan on the same instance with the same steps. Its "Notify Operator" on failure task also doesn't work.
It occurs to me that maybe I'm missing something, so here's some of the details of my SQL server:
SQL 2008 + SP1 (10.0.2531) x64 on Windows 2008 R2
DB Mail profile is public and default
I do have a slightly unusual profile, in that it uses two accounts:
1. A connection to a SharePoint SMTP service (where it catches emails directed at document libraries)
2. A connection to a UNIX-based smtp server (which routes mail to regular mailboxes and my SQL DBA mailing list)
The Profile will attempt to send to the SharePoint server first. The SharePoint server does not relay. If the document library email address doesn't exist, SQL will raise a warning (in the Database Mail log) and the profile will use the second account on the list, which is a real mail server and can relay the message to any mailbox.
It works really well, actually. When the maintenance plan completes successfully, the message is sent to the drop folder on the SharePoint server, and SharePoint routes the email to the correct library, and we have a central archive of all DB Mail notifications.
But if the job fails (for example, if the backup disk is out of space), none of this happens. According to the log, the job doesn't even try to send a notification. Looking at the DBMail log, the Mail service does not start. No email is delivered to the drop folder of the receiving SMTP server. So I don't think my Database Mail configuration is the problem here. It is apparently something to do with the way the job itself handles errors.
View 9 Replies
View Related
Dec 5, 2013
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
View 6 Replies
View Related
Jul 20, 2005
Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.
View 6 Replies
View Related
Feb 13, 2008
Hi all
We generally modify the logical name of a data file or log file from the vendor defined to our standard name. Is there any impact that you know about this? If you have 2 seperate database and accidently you have 2 same logical name .. I dont' think that will be an issue ... correct?
ALTER DATABASE XXX_UAT
MODIFY FILE (NAME = 'mdd_Data', NEWNAME = 'XXX_UAT_Data')
GO
ALTER DATABASE XXX_UAT
MODIFY FILE (NAME = 'mdd_Log', NEWNAME = 'XXX_UAT_Log')
GO
View 1 Replies
View Related
Jul 17, 2007
Hi,I want to change the logical name of the database. My database name and.MDF has the same name, but the logical name is different. Willchanging it impact anything?--sharif
View 1 Replies
View Related
May 21, 2008
I have a query. on my search page i have a number of textboxes e.g. title, subtitle, ISBN where users can enter a search string and when they click on a button the data is displayed in the gridview below.
my query seems to be bringing up the right records but with some additional records into the gridview. I have tried the same query within the SQL server management studio and it brings back two records like indicated but within my aspx page it brings back 6 additional records that have no relevance to the query. Im quite baffled as to why this is happening and i have debugged but nothing comes to light.
my code in my aspx page is as follows:
public void BindData(){String _BookID = TxtBookID.Text;String _Title = TxtTitle.Text;String _Subtitle = TxtSubtitle.Text;String _ISBN = TxtISBN.Text;String AuthorFName = TxtForename.Text;String AuthorLName = TxtSurname.Text;
SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID, Book_ID_Internal AS [Book Internal ID], Title, Subtitle, ISBN, Edition, Publication_Date AS [Published Date] FROM Book WHERE Book_ID_Internal = '" + _BookID + "' OR Title = '" + _Title + "' OR Subtitle = '" + _Subtitle + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");if (ds.Tables[0].Rows.Count == 0){//if there are no rows returned-state in the gridds.Tables[0].Rows.Add(ds.Tables[0].NewRow());GridView1.DataSource = ds;GridView1.DataBind();int columncount = GridView1.Rows[0].Cells.Count;GridView1.Rows[0].Cells.Clear();GridView1.Rows[0].Cells.Add(new TableCell());GridView1.Rows[0].Cells[0].ColumnSpan = columncount;GridView1.Rows[0].Cells[0].Font.Italic = true;GridView1.Rows[0].Cells[0].Text = "No Records Found";}else{GridView1.DataSource = ds;GridView1.DataBind();}}
if i input the same query into SQL management studio:
SELECT * FROM Book WHERE Book_ID_Internal = 'ITD 450' OR Title = 'HTML 4.0'
it returns two records which is correct.
Can anyone see where i am going wrong?
View 4 Replies
View Related
Jun 19, 2002
Hello,
Is there any way to change the logical filename of exsiting database
Thanks
Lara
View 1 Replies
View Related
Nov 7, 2000
In the database properties screen, there are four tabs: General, Transaction Log, Options, Permissions. In the General Tab, it lists four columns: File name, Location, Space Allocated, File Group.
The string in the File name column has a value such as MY_DATABASE_DAT, whereas the Location column has a value like D:mssql7dataMY_DATABASE.MDF.
The Location value is clearly the Windows file name. The "File name" (which I have seen called the "logical file") is a mystery to me. What is it used for? How can it be changed? Is there a problem if two different databases have the same "logical file" name?
(For example, if you do sp_detach_db, copy the file to another place and then sp_attach_db the new file to another database name, you have two different databases with the same "logical file" name.)
Much thanks to anyone who can shed light on this.
View 1 Replies
View Related
Jul 5, 2000
Hi everyone,
I have a database (xyz) one Machine A. On the same machine I want to create a copy of the (xyz)database with different name(xyz_1). When I restore with move option it will restore but the thing is logical file names of xyz and xyz_1 are same. I know I can change these file names by manupulating the system table.
My question is If I didn't change the logical file names of database xyz_1(new). Is there any problems or issues may arise.
Thank you,
Dindu.
View 1 Replies
View Related
May 4, 2007
The organisation I'm working for has SQL Server 2005 merge replication up and running on 11 servers in 3 different countries.
Ocasionally we experience problems caused by rows in a logical record relationship being replicated between servers in the wrong order, i.e. an attempt is made to insert child rows before the parent. I am not able to reproduce this behaviour on my test servers.
Has anyone else experienced this problem?
View 16 Replies
View Related
Jun 28, 2007
We have a progress database that we use RS on. The "status" field (active/inactive) is a logical field (true/false). When doing the SQL how do I specify that I want only active records (value = true).
View 1 Replies
View Related
Apr 4, 2001
How to rename logical name for transaction log ?
thanks
View 2 Replies
View Related
