I have a stored procedure that takes no parameters, and it returns two fields. The stored procedure sums up all transactions that are applied to a tenant, and it returns the balance and the id of the tenant. I want to use the record set it returns with a query, and I need to join it's results on the id of the tenant. This is my current...
I'm working with a legacy vb6 product and I've come across a problem whereby I need to get the filename part of a full path from a database table through DAO. I've got no access to VBA functions here so I'm looking specifically for MS Access SQL. I have no way of dropping some extra code after the query. I CAN'T change/refactor the solu...
I've tried searching through search engines,MSDN,etc. but can't anything. Sorry if this has been asked before. Is there any performance difference between using the T-SQL "Between" keyword or using comparison operators? ...
If you submit an update statement to a RDBMS in which the order of the column_name = value mappings don't match the physical layout of the file, does it affect (theoretically) the efficiency of the update operation? I ask mainly out of curiosity, knowing full well that there's probably little effect. ...
I've seen a few web searches and responses to this but it seems they all involve views. How can I adjust a column so that it only allows NULL or a unique value? In my case there's a table with stock items in which only the server items have a serial number. The rest are null. I would like to enforce some kind of control against enterin...
Sql server 2005 service pack 2 version: 9.00.3042.00 All maintenance plans fail with the same error. The details of the error are:- Execute Maintenance Plan Execute maintenance plan. test7 (Error) Messages Execution failed. See the maintenance plan and SQL Server Agent job history logs for details. The advanced information sect...
I have a query that looks like SELECT P.Column1, P.Column2, P.Column3, ... ( SELECT A.ColumnX, A.ColumnY, ... FROM dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A WHERE A.Key = P.Key FOR XML AUTO, TYPE ), ( SELECT B.ColumnX, B.ColumnY, ... FROM ...
I'm loading an XML in SQL using OpenXML while declaring the variable the max i can go up to is 8000 chars : DECLARE @xml_text varchar(8000) Since text, ntext is not allowed to be used with openXML what other alternatives do i have to load the entire XML (over 20000 chars) in SQL ? ...
Does anyone know how to pass a several bytes into a Binary (or varbinary) field using SQL and ideally in ADO (classic) & VBScript (or Visual Basic 6)? I wish to encode 10-20 (maybe more) bytes of data and have this data stored in a SQL db field. (Its not MS-SQLSVR, but I'm hoping that a standard SQL supported format will work!) The byt...
table 1 - pid cid 901 101 901 102 901 103 902 102 902 105 903 105 table 2 - cid 101 102 105 I need the Pid from table1 to show ONLY if ALL the CID's match the CID from Table 2. That means in the case of 901 which has CID 101,102 and 103 should NOT show in select statement cause 103 does not exist in table 2. ...
I have a list of e-mails from one database which I want to check against a list of unsubscribed e-mails from another database. If they exist in both lists, then I don't want them returned. SELECT distinct `payer_email` as `email` FROM `database1`.`paypal_table` WHERE `payer_email` != (SELECT `email` FROM `database2`.`Unsubscribers` ...
I was reading the Maximum Capacity Specifications for SQL Server and I came across the designation of a wide table. It's different from a standard table in that is can have as many as 30,000 rows as opposed to a normal (narrow) table that is limited to the more familiar 1024 columns. I googled for wide table, but nothing seem to come up ...
Sql newbie here. If you run a query that join two tables and one view. If the view is composed of three tables. Does it slow down the query? Is there anyway to speed it up? ...
Hello, I am reading a text file in C# and trying to save it to a SQL database. I am fine except I don't want the first line, which is the names of the columns, included in the import. What's the easiest way to exclude these? The code is like this while (textIn.Peek() != -1) { string row = textIn.ReadLine(); string[] columns = r...
I've always done this back asswards in PHP or ASAP, so I figure it's time to actually learn the proper way to do it in SQL. I have the following 4 tables in a database: Category (Fields: CategoryNumber, Desc) (small table with 15 rows) Media (Fields: MediaID, Desc, CategoryNumber, etc) (huge table with 15,000 rows) Sales (Fiel...
I know you can get the SQL, but it's paramterized without the parameters being visible. Can you get the "full" string, so to speak? ...
I'm curious about what be the best way to model this for optimized performance... not as concerned about real time data integrity I'll continue with the stackoverflow example Question id title Votes id user question A question has many votes For many queries however, we're only concerned with the aggregate number of votes ...
I'm writing a database view to sum up a bunch of records where the value in a date column is within the last 7 days. It looks something like this: CREATE VIEW RecentRecordSum AS SELECT t.ID, SUM(t.SomeValue) AS ValueSum FROM SomeTable t WHERE t.RecordDate >= DATEADD(d,-7,GETDATE()) GROUP BY t.ID Is ...
I have a query where I join a table on itself to find mismatches between a parts height or width. The only problem is that because of this join it will return each miss-match twice for each part. I only want to return one row for each miss-match, not two. Here's the table: tblTagGlass JobID varchar UnitCode varchar PartCode varchar Par...
I'm not sure exactly how to describe what I want to do, so I'll use a contrived example On SQL Server 2005, Say I have a view with rows like this, call it vwGrades: ID AssnDate AssnTxt Sally Ted Bob ----------- ----------------------- ------------- ----------- ----------- ----------- 2999 ...