Given a query of the form: SELECT <columns> FROM T1 LEFT JOIN T2 ON T1.<column>=T2.<column> does anyone have an example/link where adding a predicate to ON concerning a column(s) from T1: SELECT <columns> FROM T1 LEFT JOIN T2 ON T1.<column>=T2.<column> and ...more >>

Hi, I have a problem that i dont know if it is a real problem. Let me relate it .. --First Sql select top 10 user_id, count(id) from user_transactions where status = 1 and date_done between '2005-11-01' and '2005-11-02' group by user_id order by count(id) desc go -- Second Sql ...more >>

I have a feeling my installation of sql 2k (developer edition) sp4 is bad. I just tried to create a simple dts package using the export wizard. I want to export 1 table to an Excel file. When I click the button to actually create the package, I get "Invalid Class String." The pacakge saves and sa...more >>

Hi, Here is my query: SELECT Owner_Payee AS Expr1, Owner_DateOfPost, MONTH(Owner_DateOfPost) AS Expr2, DAY(Owner_DateOfPost) AS Expr3 FROM AMP_AllMySubordinates_Postings WHERE (Owner_Payee = 4655) Owner_DateOFPost shows dates like 1/12/2005 so on and so forth How can I...more >>

Can I make one query out of this? It gives me the desired result but can it be cleaner. I also need to add another piece with the same criteria but find in query(1) where (t2.ZIp <> T1.OriginZip) and in query(2) where (t2.ZIp <> T1.DestZip). I need the output from those queries to have th...more >>

I am a between a novice and a beginner with SQL and have been able to find the answer to every SQL statement issue I have come across until now. I am trying to figure out how long a record is assigned to a particular user. The table has four columns and looks like this eventdate recordnu...more >>

Hello, Could someone tell me when to use nchar or nvarchar? Thanks, Miguel ...more >>

Hi, I have a view joining two tables. I require all the records from table A(dbo.ProspectDetail) and the records from table B(dbo.ProspectContact) having the highest verison number, but if there is no table B row I want NULLS. If I include the WHERE subquery clause I only get rows where the...more >>

I am wondering how to accomplish getting updates of a table to also update a remote server. I have ServerA and ServerB. ServerB is at another location and is used for redundancy only. I need to keep ServerB "current". Is there an easy way to ensure ServerB gets updates as things change on S...more >>

Hi all, I've run into a peculiar problem generating an xml file. The problem is being caused by the following code: SELECT ....... CASE WHEN (RTRIM(contact.type) in ('Sales Rep', 'PEL', 'Client')) THEN 'NOUNIXID' ELSE invit.contno END, ...... FROM ..... UNION ALL SELECT ...more >>

Hello, I am moving from Microsoft Access to SQL 2005 and I am having problems creating a store procedure. Could someone, please, help me out? I have two database: Content and ContentLocalized <Content> [ContentId] PK [ContentName] <ContentLocalized> [Content...more >>

I have varbinary(8) field update table1 set pc = 256 where id = 1 select * from table1 The result of the select is: 0x00000100 This is correct (although the padding is not as many zeros as varbinary(8)) now I do... update table1 set pc = 281474976710656 where id = 1 select * from table1 ...more >>

I run the following procedure to find data in a table that has job id with a format of year, month, day. The data is 20061129% or whatever the current date is minus two days. I then query with to find data with these job ids and clear it. The only problem I see is that when the month is betwee...more >>

I am having an endless loop occurring in my testing of SqlDependency and hope someone here could help me figure it out. Basically, whenever an update occurs, the OnChanged continues to occur whenever I create a new handler for the event (CPU goes to 80%) . I step through the code and it "shou...more >>

In msssms, mydatabase, Security, Roles, Application Roles: I select my app role, doubleclick & "Application Role Properties" pops up. I select "Securables" from the left hand pane. The right hand pane has an area for Securables (nothing in it) and for Explicit Permissions (also empty). ...more >>

in msssms, I add a new schema & give it an owner. Select "Permissions" in the explorer pane in the "Schema - New" window. Click "Add" in the "Schema - New" window. A window pops up "Select Users or Roles". Nothing about tables, procs, etc. How do I add specific securable objects with sp...more >>

Hello All, I am trying to use a variable @SQL which is set to a string like 'val1','val2','val3'. This variable is used in a query in stored proc as following select * from tblName where column1 in @SQL When I compile the stored proc I get an error "Incorrect syntax near '@SQL'." Is ...more >>

Hi I'm having BIG problems with a legacy database system I've been stupid enough to take on for redevelopment and enhancement. It's SQL 2000 with an Access 2000 front-end. The problem is that the user would like to update all kinds of unique varchar "name of the thing this row is about" co...more >>

I need to generate a report that shows detail for territory, product on hand etc for Last week and prior week in separate columns. My code currently allows me to get the current week data, but I don't know how to add the data from the prior week in a new column How would I approach doing thi...more >>

I'm using the bcp utility to create a text file from a view. The text file is used by a 3rd party program for other processing. bcp installs on the server machine with sql 2k (or in my case MSDE) just fine but of course it's not automatically on any of the client machines. But that's where I n...more >>

i have a transaction log in sql server (2000) that's 1 gigabyte and under full recovery. If i run the following backup log mydatabase with no_log i think what happens is that the log records get deleted and the 1 gigabyte is available for reuse of new transaction logs. this should keep...more >>

I need to generate a report that shows detail for territory, product on hand etc for Last week and prior week in separate columns. My code currently allows me to get the current week data, but I don't know how to add the data from the prior week in a new column How would I approach doing thi...more >>

Is it really true that here is no row length limitation to table rows in sql 2005 where earlier versions had a max length of 8060? -- moondaddy@noemail.noemail ...more >>

Can anyone see anything wrong with this? I can't and I'm getting the error "Incorrect syntax near the keyword 'outer'." when I run it in Query analyser but when it runs in our accounts package then it executes OK. It was generated by the application itself. Thanks select Client.clname1, ...more >>

This is one of those things I can't figure out, but feel like I am missing something very simple.... I have a table where one of the columns contains a number that is 7 or more digits long. The whole number means one thing, but the right 6 digits are used to mean something else. I have a ...more >>

Can anybody please tell me whether there are any disadvantage of SQL joins in terms of space and time? I am in the impression that SQL join takes more time for execution as well as space because database internally builds Cartesian product and then evaluates for condition. Recently I normalized ...more >>

I want to update data from one table to an another. I thought it was possible to use an Update statement with an inner join, but running into an error. Can someone tell me how I can make the following statement work? UPDATE Table1 INNER JOIN Table2 ON Table1.Firstname = Table2.NewFirst...more >>

Hello, I am new to cursors. I have a need to read a table "A" for the supplied column <EmployeeNumber> and grab the value of <EMPAddress> column and update another table "B"'s <EmpAddress> column with the table A's EmpAddress column. There could be more than one in table A so I thought cur...more >>

In Inside SQl Server 2000 it is stated that "If, however, several writes are issued to the same client and the client is not currently reading data from the network, the network cache eventually becomes full and the write blocks" What is this network cache? Is network cache the output buffer ...more >>

How can one determine the date when rows have been updated, deleted, or inserted into a table? I have tried sys.objects and sys.tables but modify_date in these tables only reflect a change in table structure and not data rows. Any help would be appreciated. Thanks. ...more >>

CREATE proc dbo.getnewlogkeys @strtable varchar(255) = '', @strkeyname varchar(255) = '' as set nocount on declare @vkeyvalue int begin select @vkeyvalue=(select keyvalue from dbo.logkey where tablename=@strtable and keyname=@strkeyname ) set @vkeyvalue=@vkeyvalue+1 update dbo....more >>

Hi Looking for advice on creating an advanced search and I would like to know which is considered to be the fastest. A user can enter say 3 words into a search box. I want to be able to split those words and perform a "AND" search or a "OR" search against a table which contains roughy 50k w...more >>

Dear all, I use a UDT to store 2000 float values, each 4 bytes long. If I use ToString() to concatenate this values separated by a whitespace, following exception ist thrown. Meldung 6522, Ebene 16, Status 2, Zeile 1 ..NET Framework-Fehler beim Ausf=FChren der benutzerdefinierten Routine o...more >>

I have a problem I can't figure out. I have 3 tables A, B and C that join the following way: A 1:n B C 1:n B I'd like to show all rows from A and values from B with filter on C. Here some sql that illustrates the problem: -- CODE BEGIN use sandbox IF EXISTS (SELECT * FROM syso...more >>

Field1 has a min length of 50 characters and max length of 70. Is there any differenc in terms of storage size if I declare the field as varchar (100) or varchar (200)?? ...more >>

Hello there I've used before on many of my quries with MAX function as paft of joins. for example SELECT * FROM Customers C JOIN receipts R ON C.CustID = R.CustID JOIN (SELECT CustID , max(ReceiptDate) FROM Receipts GROUP BY CustID) I change it to SELECT * ...more >>