Incorrect Results With T-sql Query In SQL Server 2005
Apr 18, 2006
I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
------------
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365
Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select projectid
from tblMilestones
where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )
------------
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.
In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).
If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
and enabled = 1 "
2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"
3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table
4) Adding: OPTION (FORCE ORDER)
5) OR completely fixing the query (remove redundant loops, etc)
...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.
I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.
Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).
I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha
P.S. Results of Showplan Text:
StmtText
------------------------------
SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
| |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
| | |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
| | | |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
| | | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
|--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
(15 row(s) affected)
StmtText
-----------------------------
SET STATISTICS PROFILE OFF
(1 row(s) affected)
View 6 Replies
ADVERTISEMENT
Jun 4, 2007
I have three tables:
Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…
I need help with a query to display the following:
Category name, # of topics, # of posts
Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562
It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7
Overview: use category id to get count of topics then use the topic id to get the count of posts.
SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName
Thanks in advance
View 6 Replies
View Related
Jan 21, 2008
I am using vs 2005 and sqlserver 2005 to manage a large database. I need to create a series of text files based on the value of one of the columns. Is there an sql command that will pipe the output directly to a file?
Thanks,
Mike
View 1 Replies
View Related
Feb 9, 2007
I am running the following code in my stored procedure and I am not geting the results I need:
set @dbidstr = 'select @dbid = dbid from dbo.databaseoriginalsize'
execute sp_executesql @dbidstr, N'@dbid int output', @dbid output
print @dbid
'select dbid from dbo.databaseoriginalsize' returns values 1-26 but when I run it as a code shown above I only get one value which is 26, 26 times.
What am I doing wrong?
View 3 Replies
View Related
Oct 23, 2006
I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand();
SqlDataReader drReturn = null;
try {
// Set the connection for the command object
cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString);
cmdReader.Connection.Open();
// Set the command type
cmdReader.CommandType = Type;
// Set the command text
cmdReader.CommandText = Sql;
// Set the command timeout
cmdReader.CommandTimeout = _iTimeout;
if (Parameters != null) {
// Set the parameters
for (int i = 0; i < Parameters.Count; i++) {
cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]);
}
}
// Get the return value
drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection);
// Dispose of the command object
cmdReader.Dispose();
cmdReader = null;
} catch (SqlException e) {
this.HandleError(e);
} catch (Exception e) {
this.HandleError(e);
}
// Return the reader
return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason
View 5 Replies
View Related
Apr 25, 2007
I am reposting this to a different forum, becasue I got not response...
Using VS05 SP1 Pro SQL Express€¦
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob
View 3 Replies
View Related
Apr 24, 2006
I'm writing a FTS query which needs to search on two different columns.
E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.
I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?
Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?
Shailesh Patel...
View 3 Replies
View Related
May 15, 2007
I have a smalldatetime column in my sql2000 database table and am sorting ascending on a smalldatetime.
The problem is my results are not in desc order like I want.
Now 5/15/2007 is showing up after 5/9/2007.
I need my dates to sort from the most recent to the latest.
Any help is appreciated.
View 3 Replies
View Related
Jul 11, 2015
SQL Version:Â SQL2014
PROBLEM: The SQL insert trigger code below is returning incorrect results. In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement. For instance the value returne for the Price_BeforeAdj field does not = 20000000? It returns a NULL. See code below.
OFFENDING CODE:
ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I]
  ON [dbo].[budxcWIPAdjustments]
AFTER INSERT AS
BEGIN
 SET NOCOUNT ON;
  UPDATE budxcWIPAdjustments
[Code] ....
View 11 Replies
View Related
Mar 29, 2008
Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
Regards,
Naresh Rohra.
View 2 Replies
View Related
Feb 6, 2007
Hi all,
I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.
I am running Visual Studio 2005 and have created 2 tables:
Orders and OrderLines which are set up in master detail fashion.
The SQL Statement I create in the Query Builder is as follows:
SELECT Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM Orders, OrderLines
WHERE Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address
Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo
Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???
Here are a couple of screenshots which will demonstrate what I mean.
Here is the contents of the Orders and OrderLines tables. Each order has only 1 line item in it.
http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html
When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.
http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html
I hope this explains it better.
Anyone have any ideas???
Thanks in advance
View 13 Replies
View Related
Jan 19, 2007
I am looking for a simple example or guidance on how to move the results of a query from a mysql database to sql server 2005 using SSIS.
It seems rather difficult to do this or at least I am stuggling to figure it out. It was easy to do in DTS, how do you do it in SSIS?
View 3 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
Feb 20, 2008
Hello,I have one simple query joining two tables with left outer join on 3fields and using MIN on two fields. These two tables have lot of dataabout 3 mil in total. I am trying to migrate db from MS Access to SQL2005. I get about 689000 rows in SQL Server, vs 863000 rows in MSAccess.SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.[STATE]), T1.COUNTFROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] ANDT1.[STATE] = T2.[STATE]WHERE T1.[REASON] <'SOMETHING' AND T2.[REASON] <'SOMETHING'GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNTHAVING T1.[MON] <'-' AND T1.[ANUM] <'-'I have about 30 queries to migrate and I am sort of stuck. Does anyone have any idea ?JB
View 4 Replies
View Related
Oct 29, 2001
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Dec 12, 2000
Is there a way to save the results of a query (run in the Query Analyzer) to a text file?
View 4 Replies
View Related
Apr 7, 2006
Hello folks!
I have written a query that I need to save results of. Is there some statement that I can place at the top of my query (like we can do in Oracle through spool command) to specify the directoryfilename to save query results to? Eventually I need to schedule this to run daily via a DTS package.
Please let me know if you've come across this before?
Thanks so much!
-Parul
View 3 Replies
View Related
Jul 9, 2015
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
View 9 Replies
View Related
Jul 20, 2005
I have a relatively simple SQL Server 2000 database which I amquerying from a VB.NET application. The query is very simple, such asSELECT * FROM tblSystems. Let's say there are 10 records in the tabletblSystems. I expect to get 10 records from this query, and sometimesdo. But for some reason, I start getting 0 records from my app. If Irun the same query in SQL Server's Query Analyzer, it works fine. IfI reboot my machine, then it seems to work fine for a while, thenstarts giving me bad results again. I am running my app on the samemachine as SQL Server, although I can connect to my database remotelythrough my app, and I get the same results either way. Anyone seenanything like this? Was this fixed in a service pack (I'm using SP1,and don't want to install a newer one unless it's a known fix)?Thanks,Andrew
View 1 Replies
View Related
Feb 26, 2007
Hello,
I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query;
SELECT * FROM OPENQUERY(Charlie_file, 'SELECT * FROM [Feb$]')
Sometimes, on subsequent runs of the above query, I get the following message;
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file".
There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span?
Thank you for your help!
cdun2
View 1 Replies
View Related
Sep 27, 2006
We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies
View Related
Aug 13, 2006
In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:
CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)
And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST
The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470
To show the subnodes of the root node 2461, the following SQL Statement is used:
SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO
the results:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466
I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:
WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL
SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)
SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST
PARFOLDERNO FOLDERNO
02461
24612463
24612462
24622465
24622466
24632469
24632470
The results are shown again in Oracle 9i and SQL Server 2005 as follwos:
Oracle 9i SQL Server 2005
PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470
How can I get the result with the same sequence in SQL Server 2005?
Thanks!
View 11 Replies
View Related
Oct 12, 2007
easy question:
If I use "results to text" option when executing a query in Ms SQL Server Management Studio null value is inserted as 'NULL' string in the result. Is it possible to have blank ('') string instead of 'NULL' in the result in 2005 version.
It must be possible, but I could not find it in tools/options
thank you in advance
Andras
View 6 Replies
View Related
Mar 7, 2005
I'm working on a reporting tool that could bring back hundreds of thousands of results back at once. I need some way to run the actual query only once a day, and then the reporting tool would just pull back this cached results. To be short, I need to figure out how to do this using a minimum amount of resources. Would a DataView work with something like this? How would I have it update only once a day? I appreciate any advice!
View 2 Replies
View Related
Jul 6, 2014
Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated. Max file size has been 64k -65k. I set the max file size to 104857600 and set @query_no_truncate = 1.
View 9 Replies
View Related
Nov 23, 2007
Hi there
I have a problem with SQL Server Management Studio after insdtall regarding querys result showing!
Problem:
I have a query i run and getting results in a grid view. But the ruslt is somehow corrupted. I guess you´d think its a sql problem but it is not. Because when i take the same query and run it in the old Query Analyzer the result is correct. For better explanation written the results below to show my problem.
In old Query Analyzer (correct)
1,00 60,00
1,00 34,80
1,00 79,20
-1,00 -79,20
In SQL Server Management Studio (corrupt)
1.000 60.00000000
1.000 34.80000000
-1.000 79.20000000
1.000 -79.20000000
What can cause this problem? Do I have to change some settings to make SQL Server Management Studio show the result correctly?
Best Regards
Michael
View 3 Replies
View Related
Aug 3, 2006
I have SQL Server 2005 Standard installed on a Windows XP SP2 PC. I tried to install the SP1 upgrade from http://www.microsoft.com/downloads/details.aspx?familyid=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC&displaylang=en, which is listed as build 2047. I installed the upgrade and each upgraded component reported success, and I rebooted the PC. However, checking the version of my SQL Server as per KB321185 results in version 9.00.2040.00. There are errors at the end of the SQL9_Hotfix_KB913090_sqlrun_sql.msp.log log file as follows :
MSI (s) (54:A8) [15:57:42:059]: Product: Microsoft SQL Server 2005 - Update 'Service Pack 1 for SQL Server Database Services 2005 ENU (KB913090)' installed successfully.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 1728
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Transforming table Error.
MSI (s) (54:A8) [15:57:42:059]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (54:A8) [15:57:42:059]: Product: Microsoft SQL Server 2005 -- Configuration completed successfully.
Attempting to reinstall SP1 resulted in the same errors. I need to fully upgrade to build 2047 so that I can install the cumulative hotfix build 2153. Does anyone have suggestions as to what causes this behavior?
Thanks,
Gary Thorne
HAL Systems
View 8 Replies
View Related
Jul 31, 2015
I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
[Code] ...
Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):
557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)
^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000
^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^
[Code] ....
View 1 Replies
View Related
Feb 28, 2007
Hi all,
Hope this helps someone. My experience is that an interrupted install of SP2 screws things royally, despite signifying success.
My initial attempt at installing SQL Server 2005 SP2 (64-bit) on our dev server resulted in massive corruption of at least one SSIS solution. Without the option to uninstall the SP2, the prospect of a complete reinstall/recovery totally ruined my day.
When I loaded the SSIS solution, all packages returned the following connection based errors "connection manager will not acquire a connection because the package OffLineMode property is TRUE". OffLineMode property was in fact set to the default "False" for all packages. Individual tasks were broken also. I did not search all packages, but FTP and File System tasks were all corrupted. Nor could I successfully create these tasks in a new packages, even within a new solution.
During the install I recived the following error:
Error Log:
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2153
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29506
Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user xxxxx. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
The SP2 install continued and with the exception of database Services, all other components were installed successfully.
A very helpful person had posted this solution:
The problem is that there exists a file in this Data directory that the user running SP1 does not have permissions to modify. The workaround is to figure out which file this is (typically a user created DB or some backup program files) and to temporarily assign permission to that file(s). You can revert back to the intended permissions after running SP1.
Sure enough, there was a data and a log file with permission restricted to a single user. Granting permission to the account doing the install worked.
I ran the SP2 again and the database Services component "succeeded". In fact, as outlined, my SSIS packages were unusable.
I re-ran the SP2 install. No components were selected for update, so I manually rechecked each component and executed. This time the install completed successfully for all components, and much to my relief, SSIS functionality was restored.
Pretty scrappy install package IMHO. There are obviously dependencies between the different components being updated, but if these dependencies, and therefore integrity of the update, are broken, the installer nevertheless reports success.
Cheers, Jeremy
View 8 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Jan 28, 2014
These separate COUNT queries are very fast:
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
View 7 Replies
View Related
Jul 15, 2015
I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.
I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.
View 1 Replies
View Related
