Table A has column X, which is an int made up of the concatenation of columns Y and Z (which are both floats) in table B. I want to join tables A and B in a manner similar to this: select * from tableA a inner join tableB b on a.X = b.cast(concat(cast(b.Y as varchar), cast(b.Z as varchar)) as integer Except that, obviously, my example...
Will queries internal having one or more subquery result in cursors at SQL Server level. In otherwords I am not explicitly using cursors but i have query with a sub query. Now for processing this query will SQL server create cursors internally. ...
Using SQl Server 2000 I have a stored procedure that joins 2 tables and then returns the data. I want this sp to be able to do this for whatever table name I pass into it, otherwise I'll have the exact same code with the exception of the table name 20 or so times in a giant if statement. Basically, how do I use a variable to point to a t...
Below is a simplified version of SQL script I have. print @RowNum always shows 0, rather than the real record number of the first result set. What's wrong? Thank you. declare @i int, @RowNum int set @i=0 while @i<2 begin execute StoredProcedure @i --containing a big select if @i=0 set @RowNum=@@rowcount set @i=@i+1 end print...
How do I fix up this part of my stored procedure? The select will either return 1, 0, or null. If the select returns 1 or 0, I want @override to be set to that value. If it returns null, then I want @override to be set to 1. Something is wrong with my syntax; I am told "incorrect syntax near 'select'" and "incorrect sytax near ')'". ...
This pseduocode (inaccurate SQl) shows what I want to do. update tableA (colA, colB, colC, colD) select b.colA, b.colB, c.colC, c.colD from tableB b left outer join tableC c on b.id = c.id inner join tableA a on c.myNum = a.myNum inner join tableD on a.newId = f.newId where f.imported = 1 How can...
I have a set of students at a high school. The counselors want to divide the students up by last name. Here is the break down: Counselor 1: A to F Counselor 2: G to Hr Counselor 3: Hs to O Counselor 4: P - Z The first one is easy, I just do a: where last_name like '[A-F]%' but the second counselor is giving me grief, because if I ...
I have a query that looks like this: Insert Into tblX (a,b,c) Select x as a, y as b (select top 1 whatever from tblZ as z where z.aID = y.aID order by z.s desc) as c from tblY as y where y.foo = 'bar' AND c <> NULL The problem is that last line. It tells me that c is an invalid column name. using y.c as well, to the same result. ...
I'm creating a robust date table want to know the best way to link to it. The Primary Key Clustered Index will be on the smart date integer key (per Kimball spec) with a name of DateID. Until now I have been running queries against it like so: select Foo.orderdate -- a bunch of fields from Foo ,DTE.FiscalYearName ,DTE.Fiscal...
I need to convert Datetime fields to a specifically formatted INT type. For example, I want 2000-01-01 00:00:00.000 to convert to 20010101. What is the most performant way to make that conversion for comparison in a query? Something like: DATEPART(year, orderdate) * 10000 + DATEPART(month, orderdate) * 100 + DATEPART(day, ord...
In the process of trying to help out an app dev team with performance issues on a SQL 2000 server (from a bunch of Java applications on separate app servers), I ran a SQL trace and discovered that all calls to the database are full of API Server Cursor statements (sp_cursorprepexec, sp_cursorfetch, sp_cursorclose). Looks like they're s...
Hello, While I'm trying to detach a database through Enterprise Manager, it shows the no. of users accessing the database and it didn't allow us to detach without clearing the database connection. Well, I want to know whether the result (no. of users connecting a particular database) could be obtained through a SQL query? If yes, how ...
Hi Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via Oracle SQL Developer. I'm running the procedure to iterate column with new sequence of numbers in Varchar format for those who have null values. But I keep getting error, so a) I may have done a wrong approach b) syntax i...
I'm attempting to create data source views on a terribly designed database which lacks primary and foreign keys. I want to be able to use Data Source Views to work with SSRS Report Models. I cannot create them with the wizard because "Add Related Tables" doesn't work and BIDS cannot determine the link. When I re-open the DSV and attemp...
Hi I'm having trouble with this stored procedure, could you please help. This is error I'm getting - running all this via Oracle Sql developer on SQL Server 2000 hosted elsewhere. Error Error starting at line 1 in command: execute dbo.OF_ASEQ_EH_BROWNBIN 'dbo.EH_Brownbin_Josh','Match', 1 Error report: Incorrect syntax near the key...
Hi I'm migrating a few databases from sql 2000 to sql 2008. while running upgrade advisor I got a message that says that there are objects with the Dump/Load sintax. that is not on use anymore. So I found the following text in a proc Dump DataBase @name to @path With Init Further investigating I discovered that this was a backup and...
I Used SQL 2000 and database have following tables LandParcel (Table Name) BlockID ParcelNo NameofOnwe 11001 1056 Chandana 11001 1078 Sisil 11001 1158 Kumara 11078 105 SK 11078 245 Shantha Actions (Table) Blockid ParcelNo ActionTaken 11001 1056 Received 11001 1078 Received 11001 ...
When the table's rows are changed, these changed rows are written to XML, and let me know that the table has been changed. How can I do this? ...
Assume one fine day an admin comes to the office like he always does to do his admin tasks on Sql Server Databases and to his surprise finds a database missing. He has no clue of who dropped it or was it from an external batch or SQL injection etc ... where do one start an investigation and what are the important parameters/ findings tha...
i have two queries being combined with a UNION ALL1: --Query 1 SELECT Flavor, Color FROM Friends --Query 2 SELECT Flavor, (SELECT TOP 1 Color FROM Rainbows WHERE Rainbows.StrangerID = Strangers.StrangerID ORDER BY Wavelength DESC ) AS Color FROM Strangers Both of which, of course, work fine separately, but ...