Working With TIMESTAMPS

Oct 5, 2007

Hello!

I think I am missing some essential idea when working with a timestamp column, maybe someone around here can give me the needed pointers on how-to...

My situation is: I want to copy data periodically from one DB to another. Both are in the same SQL instance, so there are no transaction, networking or similar issues involved. It all really comes down to identifing the new rows in source and moving them over to destination.

I added a TIMESTAMP column to my source, and I can see it count up slowly for every line inserted. On the destination side, I added a table containig just one field, type binary(8) to store the last value to which the last transfer ran.
Notice that on source side I got the counter on every row, but on destination side I got only one value, I thought it would be waste to carry over the data for every row when I really only need the latest one.

Now, what my package does is:
a) Select the last used binary(8) value into a package variable named TS of type object (works)
b) Start a dataflow, where the source is SELECT statement and the WHERE clause is TIMESTAMP > package variable TS (works)
c) Multicast the data into two recordsets
d) One recordset makes most of the columns flow through some Lookups, Derive Columns into a Destination and are written back. (works)
e) The other exit of the Multicast shall go through all lines and catch the highest TIMESTAMP that came by using an Aggregate and write it down, since that is the point I need to pick up later. (and that is the problem)

Problems are: I cannot run Aggregate max on the TIMESTAMP. Only Count is allowed.
So I need to convert the BINARY(8) into a number to be able to catch the max value. Converting between BINARY(8) and DT_I8 or similar seems not possible, or the result is wrong due to the byte ordering (MSB/LSB, most/less significant byte first)

Later on, I need to write out my new found highest value again, but here the same problem applies. How do I convert from DT_I8 back to BINARY(8)?

Having my own reference value stored as bigint instead of binary(8) does not work either: You cannot assign bigint from a select to a package variable, bigint are loaded as string (as I read in a blog, and I think they are right since I get exactly the same errors).

So...
HOW does one work in an efficient way with a TIMESTAMP column to Aggregate it to max and store out this max value?
Or do you all keep the TIMESTAMP from source appended to your rows and stored with each row in destination (Wasting eight precious bytes a row)?

Thanks for your comments!
Ralf

View 3 Replies


ADVERTISEMENT

Discouraged About Timestamps

Mar 30, 2007

I have read the forums and fail to be encouraged about using the timestamp to handle concurrency.  I have SQL 2005 and am using the DataReader and command objects for data manipulation.  The database has about a dozen related tables to each other in some way.  Correct me if I am wrong, but using timestamps means I must store each table's original rowversion at read time in several possible combinations of variables (based on the joined tables at the time of read).  Is this right?  Then at update time, what if some related tables are timestamped with the original value and others have changed since read time?  How would each update stored procedure know which other dependent tables were fetched at read time?  It's easy to corrupt the data this way.
SELECT s.OrderNumber, a.UserName AS OrderedBy, cn.LastName + ', ' + cn.FirstName AS ContactName, s.CarbonCopy, s.Application, s.OrderDate,
s.FollowUpDate, s.ProdStartDate, s.InternalNote, s.SampleNote, s.UpdatedBy, s.DateLastUpdated, cn.EMail AS ContactEmail, s.MfgID,
co.CompanyName, s.ShipVia, cn2.LastName + ', ' + cn2.FirstName AS ContactName2, co2.CompanyName AS Expr1,
cn3.LastName + ', ' + cn3.FirstName AS ContactName3, s.MfgContactID, s.DistributorID, s.DistContactID, s.CustomerContactID,
s.VersionStamp, a.VersionStamp, cn.VersionStamp, co.VersionStamp, cn3.VersionStamp    <-------  Is all this really necessary?
FROM Samples AS s LEFT OUTER JOIN
Associates AS a ON s.OrderedByID = a.AssociateID LEFT OUTER JOIN
Contacts AS cn ON s.CustomerContactID = cn.ContactID INNER JOIN
Companies AS co ON s.MfgID = co.CompanyID LEFT OUTER JOIN
Contacts AS cn3 ON s.DistContactID = cn3.ContactID LEFT OUTER JOIN
Contacts AS cn2 ON s.MfgContactID = cn2.ContactID LEFT OUTER JOIN
Companies AS co2 ON s.DistributorID = co2.CompanyID
WHERE (s.SampleID = @SampleID)
What would be a simple way to update a table that depends on other tables having original version stamps?  Then at runtime how would I enforce it without generating violations when the users were updating, say, the Samples table and just viewing the dependent tables' columns, not changing them.

View 4 Replies View Related

DTS Won&#39;t Transfer DB2 Timestamps

Nov 10, 2000

When i try to load tables from DB2 OS390 Via DTS and DB2 ODBC it
give me an error with the timestamp field. Its (DTS) says the timestamp
field on SQL is marked read only.

Is there anyway around this problem?

View 1 Replies View Related

Populating Database With Timestamps...

Jan 19, 2005

Hi Everyone....

Crazy one here....

I need to populate a table with all the times that
are available in a 24 hour period, down to the 5 minute
interval.

So the table should look like....

id ds (datetime stamp)
--- --------------------------
0 1/1/2005 00:00:00
1 1/1/2005 00:05:00
2 1/1/2005 00:10:00
3 1/1/2005 00:15:00
.........
xx 1/1/2005 23:55:00



Please advise on a way to accomplish this in a script....

thanks
tony

View 14 Replies View Related

Finding Specific Millisecond Timestamps

Mar 18, 2008

I'm trying to filter out timestamps that land exactly at .000 milliseconds. (e.g. 2007-12-05 16:30:50.000) Do I have to convert the timestamp to a string first and then use the LIKE statement? If so, can somebody show me how. I'm pretty green to SQL but know the basics. Any help would be greatly appreciated!

View 2 Replies View Related

Timestamps Causing Write Conflicts

Jul 20, 2005

I have an Access XP ADE application connected to a SQL Server 7.0 SP4database. I have created a timestamp column in the main table.Unfortunately, I am now getting persistent write conflict errors.The order of operations are:1. The application starts and loads the recordset into the form using astored procedure.2. I modify a field and press a save button which uses me.dirty=false toforce a save.3. The field is saved to the database. Using profiler I can observe themodified field being saved. As I would expect, the update statement isusing the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.4. Without navigating off the record, I alter the same field (or adifferent field) and press save again and a write conflict will appear.Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.I thought that the timestamp column value is incremented each time therecord is updated. The ADE application does not appear to be recognizingthe new timestamp value.Any help or advice you could give would be appreciated.ThanksGeorge

View 3 Replies View Related

Adding Timestamps To Complete Database Backup

Mar 19, 2007

Facundo writes "Hi:
How can i add a timestamp to a generated file of a complete database backup, the idea is to archive full database backups using the date to identified it.

Thanks a lot.

FB"

View 1 Replies View Related

SQL Server 2005 Backup Command And Timestamps

Apr 1, 2008



How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?

F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak
The user database dumps each night at 9 PM.

You want to keep 3 days of online backups.

View 9 Replies View Related

Data Types Question - Varbinary And Timestamps

Nov 27, 2006

Greetings once again SSIS friends,

I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).

I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.

Please advise on what data type is suitable.



Thanks for your help in advance.

View 2 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

WHERE NOT IN Not Working

Feb 23, 2008

Original suggestion for my problem was -  Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000.  What am I missing?
using SQL 2000 server 
 SELECT    LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM         LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE     (LinksInfo.L_ID IN           (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip

View 2 Replies View Related

Why Is This Not Working

Mar 25, 2008

Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.

View 1 Replies View Related

How Do I Know If It Is Working?

Jun 17, 2004

Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated

View 1 Replies View Related

Anyone Know Why This Isn't Working...?

Apr 25, 2006

I am using the following C#...


SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute(
"C:xsd.xsd"/* this file below */,
"C:xml.xml"
);


To generate tables in the database using the following xml schema...
(file: xsd.xsd...)



























































































My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.

Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.

Cheers,

Matt.

View 1 Replies View Related

SPs Not Quite Working

May 3, 2002

OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error

Syntax error converting character string to smalldatetime data type.

Any help is very much appreciated

Code follows

CREATE PROCEDURE spBattingAve1
@cid datetime,
@cid1 datetime,
@sid nvarchar(10)
AS

DECLARE @query nvarchar(4000)

SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns,
COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname,
t_batting.player_id,
Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games,
COUNT (t_batting.notout) as nout,
(Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun,
(COUNT(*)-COUNT(dnb)) AS Innings,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton
FROM t_batting
INNER JOIN t_game ON t_batting.game_id = t_game.game_id
INNER JOIN t_player ON t_batting.player_id = t_player.player_id
WHERE t_batting.player_id = t_player.player_id
AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'

GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id
HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0
order by '+@sid+'
DESC'

exec (@query)

View 1 Replies View Related

How Come This Not Working?

Jun 21, 2001

In SQL 7.0, when i use the below query in query analyzer, I got an error message. why?
======================

Set Identity_insert on

UPDATE Table1
SET no = 3
WHERE no = 4

Set Identity_insert off
go


Error message
=============
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'off'.

View 2 Replies View Related

/3GB Not Working

May 1, 2006

Hi,

I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)

Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:

1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB

2) max server memory (MB) = 3000

3) min server memory (MB) = 3000

4) awe enabled = 0

5) @@version = 8.00.2039 which is SQL Server 2000 SP4


Has anyone seen this before? Any ideas on how to troubleshoot this?

View 1 Replies View Related

Min Not Working

Oct 16, 2006

Hi all:
Because me not this functioning the following one query.

simply me not the function respects min, because?, that I am doing badly?

Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime




Thanks.:confused:

View 6 Replies View Related

Why This SP Is Not Working??????????

Mar 2, 2006

hi..why this SP is not working that giving me error like

Server: Msg 2714, Level 16, State 1, Procedure test_E, Line 12
There is already an object named '#emp' in the database.

my one of the developer used this every where... what should i do if i dont wanna to change any of the code...i wanna some how work this SP...

create proc test_E
as

select * into #emp
from (select * from emp) p

select * from #emp

drop table #emp

select * into #emp
from (select * from dept) p

select * from #emp

drop table #emp


T.I.A

Papillon

View 5 Replies View Related

Not Working ......

Apr 11, 2006

select replace(local_notes,char(13),' ') from locals


Not Working ......

View 12 Replies View Related

If...else Not Working

Sep 20, 2006

declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'

select * from @table

if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))

print 'address starts with a number'
else
print 'address starts with a letter'



i want to write a code that prints a msg if the string starts with a number else print another msg.

View 20 Replies View Related

How Do I Know What I'm Working IN

Sep 26, 2007

In SQL2000 Enterprise manager, there is always an indication of WHICH server & database an object is opened from when it has focus.

So when I'm working on the same table in test and production servers I can tell at a glance what it belongs to.

I cannot seem to duplicate this in any way in SQL Manager 2005 Express which could lead to all sorts of trouble and really slows me down.

Have I missed something really simple in setup or use of the product?

TIA,
Richard

View 2 Replies View Related

How It Is Working

Nov 20, 2007

Dear All,
please explain me what is going inside with this query....

selectchar(94 * RAND() + 33)

i saw this in FAQ, combining several same queries to generate random passwords.


i didn't get what is char(94* rand()+33)


thank you very much for your valuble time

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 10 Replies View Related

Between Not Working

Feb 28, 2008

Hi,
can i anybody help the below query for the o/p

function:date_format(getdate(),'mmm-yy') which will return FEB-08

when iam querying the below statement data is not getting fetching .can anybody correcy it??

select * from
Rev_fbp_posted_dim_tb_tmp2
where dbo.Date_Format(fb_voucher_date,'MMM-YY') between NOV-07' and 'DEC-07'

View 1 Replies View Related

AVG Not Working

Mar 28, 2008

I would like to get the average of a field but I am not sure if I am doing it correctly.

SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, AVG(dbo.VIEW2.Den) AS Den4
FROM dbo.VIEW1 CROSS JOIN
dbo.VIEW2
WHERE (dbo.VIEW2.ID = '1')

Here is the error I am getting: Column dbo.View2.Den is invalid in the SELECT list because it is not an aggregate function and there is no Group BY CLause.

Thanks for the help

View 6 Replies View Related

How To Tell If AWE Is Actually Working?

Jul 23, 2005

How can I verify that AWE is actually working? The server is SQL 2000EE on W2KAS with the /pae switch.I've allocated 5000Mb (5Gb) for SQL but Performance monitor says theworking set for the sqlservr process is only 91Mb (it started out ataround 87Mb a couple of days ago and seems to be creeping up)."committed bytes" for the server is about 5.6 Gb out of ~6Gb so there'sa whole pile of memory going somewhere.The error log says "Address Windowing Extensions enabled" (sp_configindicates a "1" for this as well). The "max server memory" item says5000 Mb for its "running" value so within the SQL Server things appearto be correct.The server does seem to be performing better but it's hard to decide ifthat's because of AWE or just the fact that it was rebooted (it hadn'tbeen in a while).- Martin

View 3 Replies View Related

Working Wth XML

Jul 20, 2005

I've built an application that relies pretty heavily on XML to enableflexible creation of data entry forms and storage of the data enteredinto those forms. Currently, it stores data as an XML document in anntext field. This is great because it allows me a great deal offlexibility in creating fields. However, I need to implement searchingand sorting of that data and I don't know how to do this in an efficientmanner.Currently the data is stored in a format similar to this:<form><fieldname>fieldvalue</fieldname><listfieldname><items><item><label>displaytext</label><value>itemvalue</label></item></items></listfieldname></form>Is there any way to perform searches on data like this, i.e. to searchthe "fieldvalue" in <fieldname> or "itemvalue" in <value>?If there isn't a way to do this, I'm willing to re-structure thedatabast architecture, though the solution would still need to allow thesame kind of flexibility that the XML solution does.Any ideas?Thanks,Kelly

View 4 Replies View Related

AWE Working?

Apr 9, 2007

Hi all,



This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why? The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know.



1. Is my AWE setting working?

2. If not, how to fix that?

3. How to confirm how many memory is actaully using by SQL Server?



Thanks,

NIng

View 5 Replies View Related

Look Up Not Working

Apr 24, 2008

I have a source table in which one of column is numeric data type. I have to do a lookup for this column to a table column which is float data type. So I change this float data type to numeric by convert function. You when I execute the package, if I use data viewer I always see that look up column value is always null. Can any one give me some suggestion please?

Thanks...

View 4 Replies View Related

EXCEPT Not Working

Aug 7, 2006

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

View 11 Replies View Related

Query Not Working Right!

Jul 15, 2006

Hello All, I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. The searchString, Type and the Location (where the user who is searching belongs to). When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.For example: Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c. So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters. Any help would be greatfull. Query:
SELECT Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @SR + '%' OR [Client.SSN] LIKE '%' + @SR + '%' OR [Client.LastName] LIKE '%' + @SR + '%' OR [Client.FirstName] LIKE '%' + @SR + '%' OR [Client.MiddleName] LIKE '%' + @SR + '%' )

View 5 Replies View Related

My Query Is Not Working.

Jul 31, 2006

I've gotten spoiled with all these query builders.  Now in SQL server management studio express its gone.  I don't understand whats wrong with their query....any help would be appreciated.

SELECT [Products].myID, [ProductDetails].ShortName
FROM [Products]
INNER JOIN [ProductDetails]
ON [Products].DetailID = [ProductDetails].myID
 
Parse comes back asCommand(s) completed successfully.
Execute comes back asMsg 208, Level 16, State 1, Line 1Invalid object name 'Products'.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved