How To Store A Complex Query Result Somewhere And Refresh It Hourly/daily/on Demand?
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
ADVERTISEMENT
Aug 28, 2013
I have SharePoint 2010, which I have uploaded a PowerPivot model onto.
Currently it doesn't seem like I could setup the Data Refresh service to refresh my model more frequent than once a day. The Data Refresh configuration page looks like this:
Which doesn't show an option for anything more frequent than daily.
I have also tried to refresh the model's database directly on the Tabular SSAS instance (which SharePoint is using to store PowerPivot models) via SSIS or XMLA, but I get an error saying the tabular model is in "ReadOnly" mode, which I could potentially bypass (by detaching and re-attaching the model), but thats starting to sound abit too hacky.
Is there any way I could refresh my SharePoint uploaded PowerPivot model more than once daily?
View 6 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Jun 13, 2008
One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.
update tablename
set ResultColumn=exec(QueryCoulmn)
thanks
View 6 Replies
View Related
Nov 23, 2007
Hi,
I'm wondering is it possible to store query result to column directly?
for example I have 2 tables
Cache
----------
Cache_ID (int)
Cache_content (Text)
cache_created (Datetime)
Orders
-------------
Order_ID
Order_amount
Order_Date
Order_Delivered
and the query is
select * from order where order_date = '2007-11-23' and 'Order_delivered='N'
I want the result to be stored in cache table either csv or xml
I'm using sql 2000 server.
any idea?
View 7 Replies
View Related
Dec 19, 2000
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
View 4 Replies
View Related
Aug 20, 2015
When we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?
View 1 Replies
View Related
Dec 12, 2007
How do you write a store procedure to meet the following conditions:
PROXIMITY SERACH PROCEDURE
COMPANY table
PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int
COMPANY_KEYWORD table
PK, FK1 company_id int
PK keyword varchar(100)
The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,
A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.
proximity_search
@search_expression varchar(255)
,@x_locint
,@y_locint
,@max_distanceint
,@max_recordsint
Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT -Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.
Distance can be calculated using the following formula:
Distance = sqrt( square(x1-x2) + square(y1-y2) )
THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be
appropriate.
View 4 Replies
View Related
Dec 12, 2007
How do you write a store procedure to meet the following conditions:
PROXIMITY SERACH PROCEDURE
COMPANY table
PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int
COMPANY_KEYWORD table
PK, FK1 company_id int
PK keyword varchar(100)
The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,
A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.
proximity_search
@search_expression varchar(255)
,@x_locint
,@y_locint
,@max_distanceint
,@max_recordsint
Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT -Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.
Distance can be calculated using the following formula:
Distance = sqrt( square(x1-x2) + square(y1-y2) )
THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be
appropriate.
View 7 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Jan 16, 2008
I have a store procedure that works fine when tested in SQL Management Studio and Visual Studio but when I actually running the page in a browser, it does not work. There is no result generated. Below is my store procedure.1 ALTER PROCEDURE [dbo].[spSearch]
2 -- Add the parameters for the stored procedure here
3 @schoolID int = NULL,
4 @scholarship varchar(250) = NULL,
5 @major varchar(250) = NULL,
6 @requirement varchar(250) = NULL
7 --@debug bit = 0
8 AS
9 BEGIN
10 -- SET NOCOUNT ON added to prevent extra result sets from
11 -- interfering with SELECT statements.
12 SET NOCOUNT ON;
13
14 -- Insert statements for procedure here
15
16 Declare @SQL as NVarchar(4000);
17 Declare @Params as NVarchar(3000);
18 Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = @schoolID';
19 Set @Params = N'@schoolID int,@scholarship varchar(250),@major varchar(250),@requirement varchar(250)'
20
21 If @scholarship IS NOT NULL
22 Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%'''
23 If @major IS NOT NULL
24 Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @major + ''%'''
25 If @requirement IS NOT NULL
26 Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%'''
27 If @requirement IS NOT NULL
28 Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%'''
29 If @requirement IS NOT NULL
30 Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%'''
31 If @requirement IS NOT NULL
32 Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%'''
33 If @requirement IS NOT NULL
34 Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')'
35 --If @debug = 1
36 --PRINT @SQL
37 Exec sp_executesql @SQL, @Params, @schoolID, @scholarship, @major, @requirement
38 END
39
View 27 Replies
View Related
Jul 5, 2001
Hello,
I must get the content of dbcc inputbuffer (nn).
The structure of the result is well described in the BOL.
However, I don't find a way to store the result in temporary table or other stuff.
Thanks in advance.
Regards
Patrick
View 2 Replies
View Related
Aug 24, 2000
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12)
AS
BEGIN
declare @str nvarchar(2000)
declare @a varchar(10)
select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs '
select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c '
select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') "
select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) '
select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")'
/*
exec sp_executesql @str
*/
create table #t (paycode varchar(7), hrs varchar(255))
insert into #t exec sp_executesql @str
select @a = hrs from #t where paycode='ST'
drop table #t
print @a
if i call exec sp_execute @str, it will output this
PAYCODE HRS
------- --------
ST 08: 30
OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure.
HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
Thanks
View 2 Replies
View Related
Sep 3, 2004
hai..
is it possible to store the result the following statement in a table
execute master..xp_cmdshell 'dir'
when i execute this in QA i get the result. but i want to store it in a table
thnks in advance
with regards
Sudar
View 6 Replies
View Related
May 27, 2008
Please help!
Please look at the sample data below; I need help to change the value of data from (Before) - (After)
Before
Station Cycle_Index
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
When Station = 'D_CC' then Cycle_Index suppose to start a new cycle. So my problem is trying to find the next 'D_CC' and store the incremental cycle # in a New_Cycle column.
After
Station Cycle_Index New_Cycle
D_CC 1 1
C_CC 1 1
C_CV 1 1
REST 1 1
D_CC 1 2
C_CC 1 2
C_CV 1 2
REST 1 2
D_CC 1 3
Thanks mucho for your help!
Updated:
Sorry to confuse you by changing the text color!
Case when Station = 'D_CC' then Cycle_Index = Cycle_Index + 1; based on the prior cycle # and continuos to find next 'D_CC' until the end; regardles whatever in between the prior D_CC to next D_CC.
Bottom line is searching the next value from Station = D_CC then cycle # in Cycle_Index column need to be incremented by 1 and stored the new cycle # in New_Cycle column as the actual cycle.
View 5 Replies
View Related
Feb 26, 2007
HI, I'm a simple store procedure that returns a result such as this one:
AM
AM-1
AM-2
AM-n
and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile
select fields from table where id in (execute sp)
how can I make this?
Thanks a lot.
View 4 Replies
View Related
Mar 18, 2008
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
View 7 Replies
View Related
Oct 31, 2007
I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah
View 1 Replies
View Related
Feb 10, 2000
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
View 4 Replies
View Related
Apr 30, 2008
Hi,
Can I take the [index] result from a table and store it in a local var?
For example a table
declare @keyWords(
word varchar(40),
wordLen smallint
)
word len
---- ---
test 4
t 1
e 1
declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords
This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)
View 2 Replies
View Related
Oct 16, 2007
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
View 5 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 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 1 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Feb 29, 2008
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies
View Related
Jul 4, 2012
Code:
SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
View 3 Replies
View Related
Feb 16, 2012
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
View 7 Replies
View Related
Apr 7, 2013
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
View 17 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
Feb 13, 2008
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
View 8 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
