I've got a SQL Server 2005 database and a classic ASP front end (don't ask). I'm trying to debug some errors in the IIS log file (error code 80040e14), and I'm not having much joy at the ASP end of things. Is it possible to retrieve the raw SQL which caused the error from SQL Server directly, or failing that set SQL Server to record such...
Hello, I need to create a table with the following structure: calendar week; week start date; week end date which contains all weeks beginning in 2007 until the current week. The special thing is, that when an end of month falls within a week, the week is cut in two slices - one record that has a start date that is the beginning of ...
I want to let my users specify which hours / days they want to be contacted, I though of creating a fixed timetable with the 7 days of the week and let the user specify which hours he has free. I'm having a little trouble figuring out how I would store that info in the database, can anyone help me with a good table design for this situa...
Hi, I'm not sure whether my SQL code and practise here is any good, so hopefully someone could enlighten me. In order to try and separate my DAL from the Business layer, I'm not using an SQLDataSource on the page. Instead, I've created a gridview to display the results and called an SQL command to retrieve results. I have the followin...
When I try the following: declare var_type VARCHAR2(10); begin var_type := 'B'; select case var_type when 'B' then 'Beans' when 'L' then 'Legumes' end from tableOfBeans ; end; I get an error saying ORA-06550: line 4, column 1: PLS-00428: an INTO clause is expected in this SELECT sta...
Hopefully this is a nice quick one to resolve. Here is my .sql file: USE my_db; DELIMITER $$ CREATE PROCEDURE searchLocation(IN argQuery VARCHAR(32), IN argLimit INT) BEGIN SELECT DISTINCT `suburb`, `postcode` FROM `location` WHERE `suburb` LIKE '%argQuery%' OR `postcode` LIKE 'argQuery%' LIMIT argLimit ; END $$ DELIMITER ...
Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created. Create table Teams ( TeamID varCHAR2(4) constraint Teams_TeamID_PK Primary Key, TeamName VARCHAR2(40) ); This is the code I am using to show my constraints. SELECT c...
I am assigned the task by manager to create a DDL script. One of the fields that is required is specified as PIC S9 with length 16, but I don't know whether PIC S9 is supported by Oracle. The DDL script need to be compatible with oracle 10g. What is this PIC S9 data type? Is this supported by Oracle? If yes, can i use the following?...
what is the equivaltent of PIC S9 with lenght 16 in oracle? I want it for storing timestamp ...
I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be bett...
Which, query performance wise, is more effective? Considering T is a table, and PK is the primary key in the table T. Are they different or they are just a matter of choice? select col1, col2 into :var1, :var2 from T where PK = a ...or: EXEC SQL DECLARE aCursor CURSOR FOR select col1, col2 into :var1, :var2 from T where PK = a...
I have a postgres table that looks in part like: Year | Month | ...... (more columns) "2004" | "09" | ...... "2004" | "09" | ...... "2004" | "09" | ...... "2004" | "10" | ...... "2004" | "11" | ...... "2004" | "11" | ...... "2004" | "12" | ...... "2005" | "01" | ...... "2005" | "01" | ...... Yes, these are all strings. Don'...
I am executing ;with cte as ( select 1 as rn, 'name1' as nm union all select rn+1,nm = 'name' + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte and is receiving the error Msg 240, Level 16, State 1, Line 2 Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte". Wh...
Is there a way to create a global variable in SQL Server so it's saved even the server is shut down, so I can use it in functions? Example from what I need: DECLARE @@DefaultValue bit This variable should never be deleted unless I explicityl do so. ...
in an ms access database with two tables TableA MSN PR1 12 A 13 B 11 C 14 X TableB MSN PR2 14 L 12 M 13 O 11 X how can i write an sql query so that PR2 columns gets added to TableA but only after matching the MSN values . so the final TableC would be TableC MSN PR1 PR2 12 A...
i have an ms-access table TableA MSN PR 11 - 13 A 12 Dead 14 B 15 C How can i write an sql query to remove records in "-" and "Dead" occurances in PR collumn. so that query result should be MSN PR 13 A 14 B 15 C any help appreciated ...
I have a table in ms-access with column names A to H TableA A B C D E F G H how can i write a query to select all columns except B and F columns. Query result should be A C D E G H Do we have something like this select * from TableA except B, F ? ...
We recently updated our WPF application to perform its data synchronisation (using sync framework) within a single transaction against our SQL Server 2008 database. Almost straight away this has somehow led to a row being locked in of of the tables preventing all other users from syncing. The thing is that the lock does not seem to be...
I have the following query: SELECT dev.DeviceName, Count(dom.DomainID) AS CountOfDomains FROM tblDevices dev JOIN tblIPNumbers ip ON dev.DeviceName = ip.ServerName JOIN tblDomains dom ON dom.IPNumberID = ip.IPNumberID WHERE dom.PointerTo=0 AND dev.DeviceType='3' AND (dev.[System]='32' OR dev.[Syst...
How can I write a SQL query to replace all occurrences of space in a table with underscore without writing individual statements for each column? ...