Good Morning All. I've been struggling with this issue for a while now, and I can't seem to wrap my head around it. So I have two tables in my Database tblDateTrans CREATE TABLE [dbo].[tblDateTrans]( [Date] [smalldatetime] NOT NULL, ) This table is an external calendar table that contains all the dates from 1/1/2007 - 1/1/201...
Scenario: I need to pull information out of a Visual FoxPro database; however, running large queries against this has a tendency to lock the system up. To resolve this, we put limits in place that cancelled the query if it ran past a certain amount of time, and limited the number of rows it would return. Is there a way to have a query...
Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field. ...
I had am maintaining an query that is as follows: select field_1, field_2 from source_table minus select field_1, field_2 from source_table where status_code in (3, 600); When I looked at this query, I immediately thought, "That's lame. Why not just use a 'NOT IN' and remove the MINUS business. So I re-wrote it as so: select field_...
I set up a database mirroring and then used this connectionstring to connect to it: Data Source={0};Failover Partner={1};Initial Catalog=AdventureWorks; Integrated Security=True; After adding some data into database, I shutdown the principal server, so the mirror server becomes the principal server. I open the connection again...
Hi - I'm working on a DB2 database and trying to get records by effective date. The only catch is the effective date fields are spanned across 4 columns (month, day, century, year). I think I have the date piece figured out in the select but when I add the where clause I'm having problems. (note that I'm using the digits command to pad b...
We are performing a database migration to SQL Server, and to support a legacy app we have defined views on the SQL Server table which present data as the legacy app expects. However, we're now having trouble with INSTEAD OF INSERT triggers defined on those views, when the fields may have default values. I'll try to give an example. A ...
Hi, I wonder if we can implement and fetch data from different datasources into a DataGrid. Let's say for example i have a 3 stored procedures: What I did is drag 3 datasources and configured it in each stored procedures. 1st stored procedures : returns @id and @name 2nd stored procedures : returns @name, @server and @location 3rd stor...
theres single quotes using in $bio = "$user->description"; inserting DB like this; <? $sql = "insert into yyy (id, twitterid, twitterkullanici, tweetsayisi, takipettigi, takipeden, nerden, bio, profilresmi, ismi) values ('', '$id', '$twk', '$tws', '$tkpettigi', '$tkpeden', '$nerden', '$bio', '$img', '$isim')"; $ak = mysql_query("sel...
So if I create a new table in the query editor in SQL Management Studio after hitting refresh on the DB I can see and work with that table. However if I want to run another query referencign that table from withen the query editor it doesn't reconize that table exists. I've tried hitting refresh at the DB level, and the table level but i...
Selecting constants without referring to a table is perfectly legal in an SQL statement: SELECT 1, 2, 3 The result set that the latter returns is a single row containing the values. I was wondering if there is a way to select multiple rows at once using a constant expression, something kind of: SELECT ((1, 2, 3), (4, 5, 6), (7, 8, 9))...
This query originally came from a VB6 program accessing MS Access tables which are linked to external databases through ODBC. It takes about 3:30 to run. Now I've setup a SQL Server 2008 Express box to evaluate how we can migrate to a better database system. So I setup a linked server to the external server (we call it DWPROD) and when ...
I'm trying to build a mailbox where we can group the messages in x. If you put x to 20 you'll see messages 1-20 on the first page, opening the second page will show message 21-40 etc. How do I efficiently query this? The best I could come up with is this: select top 20 * from tbl_messages where tnr_id not in ( select top 40 tnr_i...
Hi, I have got the following join: SELECT l.cFirma AS Lieferant, SUM(la.fEKNetto) AS Verbindlichkeiten, l.kLieferant AS Lieferanten_ID, 100 - gk1.fFaktor * 100 AS Grundkondition, MAX(gk1.dDatum) AS Datum FROM tBestellung b, tArtikel a, tBestellpos p, tLieferant l, tLiefArtikel la, tGrundkondition gk1...
In my Delete trigger I need to know the username of the person deleting the record. Is there a SQL function or variable for that? ...
Hello, I have the following data: ID parentID Text Price 1 Root 2 1 Flowers 3 1 Electro 4 2 Rose 10 5 2 Violet 5 6 4 Red Rose 12 7 3 Television 100 8 3 Radio 70 9 8 Webradio ...
I have a Join SELECT * FROM Y INNER JOIN X ON ISNULL(X.QID, 0) = ISNULL(y.QID, 0) Isnull in a Join like this makes it slow. It's like having a conditional Join. Is there any work around to something like this? I have a lot of records where QID is Null Anyone have a work around that doesn't entail modifying the data ...
In Microsoft SQL Server 2005 or above, I would like to get the first row, and if there is no matching row, then return a row with default values. SELECT TOP 1 ID,Name FROM TableName UNION ALL SELECT 0,'' ORDER BY ID DESC This works, except that it returns two rows if there is data in the table, and 1 row if not. I'd like it to always...
I have been reading the heated debates on composite vs surrogate keys on Stack Overflow and other websites, and even though puzzled about some of the arguments given, I feel I am aware of the pros and cons of each. I feel tempted to go for surrogate keys, but now I have another question. Let me explain my situation. I have a table consi...
I'm working on an analysis application which operates on datasets up to 10,000,000 entries. I'm only writing to this database during initial import, not afterwards. Right now this dataset is stored in SQLite - mostly for speed reasons. Unfortunately, I find the speed disappointing, mostly because SQLite can't use indexes queries like ...