Wrong Query Results In SQL Server 2000 From VB.NET
Jul 20, 2005
I have a relatively simple SQL Server 2000 database which I am
querying from a VB.NET application. The query is very simple, such as
SELECT * FROM tblSystems. Let's say there are 10 records in the table
tblSystems. I expect to get 10 records from this query, and sometimes
do. But for some reason, I start getting 0 records from my app. If I
run the same query in SQL Server's Query Analyzer, it works fine. If
I reboot my machine, then it seems to work fine for a while, then
starts giving me bad results again. I am running my app on the same
machine as SQL Server, although I can connect to my database remotely
through my app, and I get the same results either way. Anyone seen
anything 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
ADVERTISEMENT
Sep 1, 2004
I am running some query with the following where clause
where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'
It gives the records where DateCreated is of 2004-07-01 21:48:02.377
the default language on the server is British English.
Could anybody please tell me the reason why records from july are also coming
View 1 Replies
View Related
Jul 20, 2005
I'm trying to rebuild from a meltdown. I'm using disk backup files. Icould have sworn that I had SQL Server 2000 SP3 installed on the oldmachine, but maybe not.With SP3 installed, I get a message along the lines that the restorecan't be done because the backup was created using server version134218262 and this server is version 134218488.I've searched the documentation and can't find any reference to thoseversion numbers.In any case, I thought SQL Server 2000 could restore backups createdon any SP level to any SP level.Any ideas?--Regards.Richard.
View 4 Replies
View Related
Sep 10, 2007
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1
Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1
Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1
Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1
Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....
View 6 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
May 21, 2015
I have one query which is pulling Balance sheet amounts from SAP Business One database. The query is giving the correct figures for the rest of the accounts except for the VAT Input refundable account 123600 and VAT Output Payable account 221400. The query sums up totals at Title account level(FatherNum) and the above accounts are the title accounts:
SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))
END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,
T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2,
[code]....
View 8 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
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
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
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
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
Dec 17, 2007
Hi there,I have a strange problem with an application running on Windows 2000connecting to a SQL Server 2000 with Greek collation.Runnin this query on the SQL"SELECT serverproperty('Collation'),databasepropertyex('mydb', 'Collation')"gives me:SQL_Latin1_General_CP1_CI_AS, Greek_CI_AIWhen I run my application on my Windows XP machine I have no problemswith the characters however when I try the same applicaton on aWindows 2000 platform the Greek characters are lost. For example theGreek characters TI appear as OE.Any help is appreciated,regardsBOB
View 2 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
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 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
Aug 25, 2015
If I use msdb..sp_send_dbmail or save query results as text (using sqlcmd) and include the column headers I get the dashed separator line.
e.g.
custID, name
------, ------
1,bob
2,jamesI would like this
custID, name
1, bob
2 ,james
I found this method [URL] ....
::
sqlcmd -E -S (local) -d myDB -W -w 1024 -s "," -i "SELECT * FROM tblCust" | findstr /V /C:- /B > C: emp.csv
Can the same result be achieved sending as attachment with dbmail?
EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query
Change the query to return column headers in resultset
SELECT 'CustID' as f1, 'name' as f2
UNION ALL
SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set
msdb..sp_send_dbmail @query_result_header = 0
View 0 Replies
View Related
Nov 26, 2007
Can anyone please tell me what is wrong with this query:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
In DreamWeaver the bit in bold is greyed out, why?
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
Thanks Joe
View 2 Replies
View Related
Jan 4, 2005
I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?
SELECT
DISTINCT MtgeBroker,
COUNT(CreatedDate) AS [Title Orders for Current Month]
FROM RECalendar
WHERE
(
RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness')
AND
RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974')
)
AND
(
MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE()))
AND
YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE()))
)
GROUP BY MtgeBroker
View 1 Replies
View Related
Mar 15, 2006
Hi all!
I am trying to set up a poll in ASP.NET 2.0, using SQL database. My
problem is that it won't update the database. This is what it looks
like:
(master)
<asp:radiobuttonlist id="vmpoll"
runat="server"
OnSelectedIndexChanged="poll_SelectedIndexChanged">
<asp:listitem>Object1</asp:listitem>
<asp:listitem>Object2</asp:listitem>
<asp:listitem>Object3</asp:listitem>
<asp:listitem>Object4</asp:listitem>
<asp:listitem>Object5</asp:listitem>
</asp:radiobuttonlist>
<br />
<asp:Button ID="btnPoll" runat="server" CommandName="update"
Text="Rösta" />
(master.cs)
protected void poll_SelectedIndexChanged(object sender, EventArgs e)
{
if (vmpoll.SelectedValue != "")
{
string ConnectionString =
System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString;
SqlConnection con = new SqlConnection(ConnectionString);
string sql_update = "UPDATE poll SET votes = votes + 1 WHERE lagnamn =
'" + vmpoll.SelectedValue + "' LIMIT 1;";
Label1.Text = "Du har röstat på " + vmpoll.SelectedValue;
}
else
{
Label1.Text = "Inget värde valt";
}
}
Finally, the database has a table called poll, including the fields
int lagID Pk
string lagnamn
int votes
What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but
it won't update the database. I hope someone will see where I am going wrong here.
Thanks in advance!!
View 5 Replies
View Related
Apr 2, 2006
What is wrong with this query? How can may it work?
UPDATE T1 INNER JOIN T2 ON T1.cID = T2.cID SET T1.PSVP = T2.PSVP, T1.PSVR = T2.PSVR
WHERE (((T1.cID)=123))
View 1 Replies
View Related
Sep 2, 2006
I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:
If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.
For instance, this code:
sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
rs.MoveNext
Loop
Set rs = Nothing
could show the following:
2441-John Doe (10)
2441-John Doe (11)
if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.
I appreciate any help you can give me!
Thanks~
View 1 Replies
View Related
Feb 5, 2004
select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
, SUM(inv_numb, freight_val)
FROM OPCSAHH
WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
LESS THEN = 2003
AND PREPAID COLLECT FLAG = 'C'
What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.
anyone know?
View 7 Replies
View Related
May 19, 2008
THe following query generates, Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ',' errors.
Can anyone tell me what I am missing?
EXECUTE sp_MSforeachtable '
select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user)
from ? as o
where
not exists(select * from users u
where (u.users_id = o.rn_create_user)
)';
View 3 Replies
View Related
Mar 27, 2007
insert into o_country(cname) values(select cname from countries where cid=5)
o_country
----------
Cid int(primary key)
Cname varchar(50)
countries
----------
Cid int(primary key)
Cname varchar(50)
Malathi Rao
View 1 Replies
View Related
Jun 22, 2007
Dear experts,
please let me know where is the mistake in my query
select distinct (select COLUMN003 from TABLE021 where COLUMN001 = t.COLUMN001)
+ '(' +(select COLUMN002 from TABLE021 where COLUMN001 = t.COLUMN001)+ ')' as tablename
(select COLUMN003 from CATABLE009 where COLUMN001 = t.COLUMN003) modulename from TABLE022 t order by tablename
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 4 Replies
View Related
