Storing Daily Cached Query Results In SQL Server
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
ADVERTISEMENT
Mar 28, 2001
Can someone tell me how to store the results of a multi-row query in a text or varchar variable so that I might do something like this:
declare @var1 text
select @var1 = {results of select date, count(*) from testtable2 group by date)
Thanks!
joe
View 2 Replies
View Related
Mar 4, 2008
Hello,
We have some queries that are long and intensive. We have thought about running the queries and storing the data in a text file for lookup from our website.
Example: Our online store only displays items that are in stock so when a user selects a category a query runs and grabs only items that are in stock and then displays them. There could be thousands of items the query needs to sort through before displaying the items that are in stock. What if we ran this query once every hours an stored the results in a txt file? The asp page would then go to the text file to grab the results instead of having to run the query every time a user selects a category. Will this speed up the site by not having to query every time? Would this be a correct way to eliminate queries that run thousands of times a day?
thanks
Andy
View 2 Replies
View Related
Apr 30, 2014
way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them.
Also do you know of any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,
[Code].....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect. I’m thinking the server is using an old cached query plan, but don’t know for sure.
View 3 Replies
View Related
Sep 20, 2007
I want to check the performance of m query and i just want to remove cached query results. Is there any suggestion how can i do this.
I just want to check after each modificatin how much improvement in performance
View 1 Replies
View Related
Apr 29, 2014
Any way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them. Also any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,
c.FirstName + '' '' + c.LastName as Name,
cus.CustomerNumber Code,
c.companyname as "Company Name",
a.Address1,
a.Address2,
[code]....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect.the server is using an old cached query plan, but don’t know for sure.
View 9 Replies
View Related
Apr 18, 2001
I need to manage the number of rows affected by a query.
If I run this script on my database:
SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
the query analyzer gives me back the number of lines affected:
-----------
4,00
(1 row(s) affected)
I need to manage this amount (4,00), so I applied this modification:
DECLARE @NUM_ROWS INT
SET @NUM_ROWS = (SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
)
but it doesn't work!! the SRV returns me this:
Server: Msg 107, Level 16, State 3, Line 3
The column prefix 'TABLE_NAME' does not match with a table name or alias name used in the query.
Do you know another way to store the number of rows affected?
View 2 Replies
View Related
Jul 9, 2014
SQL 2012R2 Std. I have a sql statement to retrieve data in XML format. I have to use EXPLICIT because I have to use tag <CDATA>
I would like to store the XML returned into 1 column containing the XML tags.
For example:
#tmpXML
row 1: <DOC><NET_INFO net_ID=34><NOTES><![CDATA[description]]></NOTES></NET_INFO></DOC>
row 2 <DOC><NET_INFO net_ID=35><NOTES><![CDATA[description2]]></NOTES></NET_INFO></DOC>
select *
into #tmpXML
FROM (SELECT 1 AS Tag
[Code] ....
But I keep getting an error message:
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
I have tried a couple of other ways but continue to get an error message.
View 3 Replies
View Related
Feb 11, 2008
I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:
SET @value = select max(price) from product
but this does not work, can someone tell me how I would go
about in storing the results in a variable.
@value is declared as int
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
Mar 28, 2008
All,
I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...
"Insert exec cannot be nested"
I suspsect this is because I have a Insert Exec statement inside my stored procedure...
Is there any way to come over this issue ?
syntax of my code...
create table #Temp1 (ID int)
insert into #Temp1
EXEC SP1
when I try to run the above code I get the error "Insert exec cannot be nested"
SP syntax :
Create Procedure SP1
as
Begin
create table #Temp2
statements.....
Insert into #temp2
exec SP<Name>
staments.. cont...
END
View 5 Replies
View Related
Jun 4, 2006
I just read something interesting here: http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101
A column type that helds an expression, and in queries returns the results.
That sounds excelent in my database, to save some code in my client applications. E.g adding price totals, and taxes of an order.
I can't find any info about this in later versions of SQL server. Is this not possible anymore?
View 3 Replies
View Related
Apr 6, 2015
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
View 3 Replies
View Related
Nov 1, 2006
We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.
My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.
Please help optimize my slow query!
------ Database schema is:
Column Name
Data Type
Length
Allow Nuls
1
msrepl_tran_version
uniqueidentifier
16
0
0
[time]
GMTtime (int)
4
0
0
msec
int
4
1
0
offset
GMToffset (smallint)
2
0
0
type
nameType (varchar)
15
1
0
host
nameType (varchar)
15
1
0
process
nameType (varchar)
15
1
0
dbName
nameType (varchar)
15
1
0
ptName
nameType (varchar)
15
1
0
description
descType (varchar)
47
1
0
rtuName
nameType (varchar)
15
1
0
groupName
nameType (varchar)
15
1
0
message
msgType (varchar)
131
0
0
fgInt
tinyint
1
1
0
sevInt
tinyint
1
1
0
key1
int
4
1
0
key2
int
4
1
0
spooler
tinyint
1
1
------- The database view eventView that's used by my queries:
SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time]
FROM dbo.summary
------- Fast totals query:
select
(select count(*)
from [event].[dbo].[eventView]) as Events,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1)as Low,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2)as Medium,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3) as High,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4) as Low1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6) as Medium1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8) as High1,
(select count(*)
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9) as High2
------- Slow daily query:
declare @myDay datetime
declare @begDay datetime
declare @myTable
table( myDay datetime,
Events int,
Low int, Medium int,
High int, Low1 int, Medium1 int,
High1 int, High2 int )
select @myDay = getdate()
select @begDay = convert(datetime, '10/01/2006')
while @begDay <= @myDay
begin
insert into @myTable (myDay) values (convert(varchar(20), @begDay, 101))
---------------------------------------------------------------------
update @myTable set Events =
(select count(*) as Events
from [event].[dbo].[eventView]
where
convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set Low =
(select count(*) as Low
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 1
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set Medium =
(select count(*) as Medium
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 2
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set High =
(select count(*) as High
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 3
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set Low1 =
(select count(*) as Low1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 4
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set Medium1 =
(select count(*) as Medium1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 6
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set High1 =
(select count(*) as High1
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 8
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
update @myTable set High2 =
(select count(*) as High2
from [event].[dbo].[eventView]
where type = 'alarm' and sevInt = 9
and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101))
where myDay = convert(varchar(20), @begDay, 101)
---------------------------------------------------------------------
select @begDay = dateadd(day,1,@begDay)
end
select * from @myTable
View 15 Replies
View Related
May 16, 2007
I am trying to setup cached reports so that one of my larger reports doesn't have to be re-run every time someone wants to view it (the data source only updates every 24 hours).
Anyway I made a new data source, set the report to use that, and in that data source I said to use "SQLexampleUserName" as the stored credentials.
Now when I go to run the report it says: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.
This is referenced in the MSKB here:
http://support.microsoft.com/default.aspx/kb/555332
Which makes sense, but now my question is: If I want to used a cached report do I HAVE to allow SQL Server Credentials?
I was using Windows Authentication only up to this point.
View 7 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
Jun 17, 2015
I'm using SSMS 2012. I have multiple queries that are run daily(I also have a few that are run every Monday) that I want to schedule to run at midnight and export to a new xls file on the network drive to be used the next morning. Is there something that I can add to each query to accomplish this or would I have to set something else up to call the queries?
View 3 Replies
View Related
May 11, 2006
We have some reports run quite slow because the queries are complicate and tables are large. So we create a materialized view in Oracle which store the result from the query and refresh it occationally. How to do this in MSSQL? I try the indexed view but seems it has lots of restrictions, our query has sub queries and cross database table joins so can't use the indexed view. Any other object or temp table can be used to cache the report data and be accessed globally by other procedures?
Any suggestions are welcome,
Cheers
View 5 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
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
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
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
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
