How To Use WITH In Sqlserver
Apr 8, 2008
;WITH cte
AS
(
SELECT
Symbol,
(((DATEPART(hour, CAST([Time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([Time] AS DATETIME)) / 5) * 5)) AS [Time],
Price,
ROW_NUMBER() OVER(PARTITION BY (((DATEPART(hour, CAST([Time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([Time] AS DATETIME)) / 5) * 5)) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1,
ROW_NUMBER() OVER(PARTITION BY (((DATEPART(hour, CAST([Time] AS DATETIME)) * 60) + (DATEPART(minute, CAST([Time] AS DATETIME)) / 5) * 5)) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2
FROM
dbo.TimeSales
WHERE
Symbol='MSFT' -- AND [Date] = '20080325'
)
SELECT
Symbol,
LTRIM([Time] / 60) + ':' + LTRIM([Time] % 60) AS [Time],
MAX(CASE WHEN rn_1 = 1 THEN Price ELSE NULL END) AS [Open],
MAX(Price) AS High,
MIN(Price) AS Low,
MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close]
FROM
cte
GROUP BY
Symbol,
[Time]
ORDER BY
[Time];
GO
---------------------------------------------------------------------------------
This is the query which dispalys the data minute wise time from second by second data.
I want to store this query like WITH SSS.
Is this possible to create
View 5 Replies
ADVERTISEMENT
May 10, 2007
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
View 1 Replies
View Related
Sep 20, 2006
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
View 1 Replies
View Related
Apr 18, 2008
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
View 1 Replies
View Related
Jul 29, 2005
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies
View Related
Jun 14, 2006
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
View 1 Replies
View Related
Feb 9, 2006
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
View 1 Replies
View Related
Jun 20, 2007
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
View 1 Replies
View Related
Apr 18, 2007
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
View 7 Replies
View Related
Jul 8, 2006
Hello Expert!
I have 2 Database €“ Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
View 4 Replies
View Related
Dec 29, 2005
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
View 1 Replies
View Related
Jun 6, 2007
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
View 1 Replies
View Related
Mar 29, 2006
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
thanks,
Henk
View 7 Replies
View Related
May 9, 2006
Hi every body,
I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.
John Dang
View 1 Replies
View Related
Mar 8, 2007
When I am trying to insert values into a table which is created using Sql Server.
I am gettin Sql Exception : " String or Binary Data would be truncated.The Statement has been terminated. "
Please help me out to trouble shoot this error.
With regards
Khasim
View 2 Replies
View Related
Jan 17, 2008
Good day ... I need to know how to make a LOB column in sqlserver...
In table creation I definde the column that will receive the binary data as nvarchar(MAX)
the problem now that whenever I try to read it back I get this error
Unable to cast object of type 'System.String' to type 'System.Byte[]'.and when I define the column as varbinary(MAX) it cant real handle large objects
I have a method that will return a DataTable Here's the code that I use to write
DataTable dt = fm.getFileData(fileID);Response.ContentType = dt.Rows[0][4].ToString();Response.BinaryWrite((byte[])dt.Rows[0][2]);
Thanks in advance ...
View 2 Replies
View Related
Mar 6, 2008
what are the minimum tables required to many to many relation ship?
View 2 Replies
View Related
Mar 23, 2002
when i delete the row from VB using transact sql statement
it returns with an error 'TIMEOUT EXPIRED'
View 1 Replies
View Related
Aug 8, 2000
hi,
I have a dynamic table in DB2 which I want it to Sychronize it into SQL Server. I was thinking to Dump the Changes and New data into a flat file and Pull that flat file thru DTS...IS that a right way?
OR
Just Pull changed and new data it with the help of CR DB2 ODBC drivers periodically...
or
Any other way? Pelase Advice the Right Choice
Thanks in Advance for Help
Aakash
View 2 Replies
View Related
Feb 16, 2001
Hi,
I am interested in getting a MCP (for MS SqlServer, Exam #: 70-028),Can someone refer me some good book and sample tests for preparing for this exam.
Thanks
Ellen
View 3 Replies
View Related
Aug 10, 1998
> Hello,
> Is there a possibility for password expiration in SQL 6.5?
> Thank you
View 1 Replies
View Related
Feb 24, 2006
I have a choice of working with either SQLServer or Oracle within my company. I need to advise them which way to go. I need to support a couple of hundred users in a mixed workload environment (OLTP, Batch & Reporting)
The O/S will be Windows 2003 Server. My understanding is that Oracle is the better database technically but SQLServer is cheaper and easier to integrate with other MS technologies. My company will most likely buy into whatever technology will deliver value for money but also be secure and be able to scale.
Any views gratefully received.
View 10 Replies
View Related
Jan 3, 2007
We're starting to think about migrating a 2000 32-bit sql/server to 64-bit (probably 2005). Performance gains are tempting when it can easily be bought, almost a management mantra . I've been doing some reading about the pro's and con's, most of what I've found is pro, very little on the con.
My impression is that the 64-bit version(s) is faster in most case, if not all. It would seem that most of the performance improvement is gained by the heavier equipment and overcome memory limitations. Too me, it looks like the integration of hw/os/sql is made very tight on 64-bit, more than 32-bit, which might be the main reason for the improvements.
Call me skeptical but I'm looking for some more con's and a little less on the "great, fantastic, improved and hoorah" that I've been reading. Anyone out there returned to 32-bit after a 64-bit disaster?
View 2 Replies
View Related
Apr 30, 2008
Hi,
The functional requirement is how to export a sql query or table into foxpro table (*.dbf) via DTS.
It was fine to import .dbf into sql table through DSN.But while exporting the destination connection source was not provided for ODBC DSN
If any one some clue then revert back.
Regrads,
Tarun
View 2 Replies
View Related
May 23, 2008
HI FRIENDS MAY U HELP ME TO SLOVE THIS ISSUE
MY TABLES ARE COR_FIELD AND ADTL_INFO
IN COR_FIELD I HAVE
FIELD_ID FIELD_LABEL
109 GLAZE
120 FINISH
130 DRAWER
AND IN TABLE ADTL_INFO FIELDS ARE
SALES_ORDER_ID FIELD_ID FIELD_VALUE
1 109 NULL
1 120 MAHESH
1 130 RAJESH
2 109 MINNU
2 120 SRINIVAS
2 130 SHIVA
THE QUESTION IS HOW TO SELECT INDIVIDUAL FIELD VALUE FOR ONE PARTICULAR SALES_ORDER
mahi
View 1 Replies
View Related
Jul 26, 2007
Hi All,
i had 1 doubt regarding the between in sql server
when i between in sqlserver it means it includes that date also
for example
select * from tbltest where joindate between '1/2/2002' AND '2/2/2002'
in this case joindate having 2-2-2202 09:30:01.000 is not coming
why is it like this
what is need to add to get 2-2-2202 09:30:01.000 in between
With Regards
Amjath
View 2 Replies
View Related
Jan 16, 2008
I am very new to SQLSERVER. I need to start learing.. Please give some links or any document? I wanted to be a SQL SERVER DB developer(creating tables, index, procedure, functions, triggers, developing queries etc). Please advise..
Thanks
View 4 Replies
View Related
Feb 6, 2008
currrently we have a sql server stored procedure to get data from a sql server db on the same machine. The data is going to be moved to db2 on a unix machine. Is it possible from sql server to connect to db2 to get the data from db2 tables. Please let me know the diffrent methods. will it be slow to acces the data from db2. The db2 is in a diffrent location.
Thanks
G
View 3 Replies
View Related
Jul 23, 2005
I am using a tool called AppDetective to identify database servers andthen to scan them for vulnerabilities.I've just been handed a list of machines and have been asked todetermine which ones are actually MS SQLServer. (AppDetective saysthat all of the systems on the list have MS SQLServer).Does MS SQLServer make use of the same engine/port arrangements forits other applications such as MS Access?I've also heard that the engine (Jett?) is also used in some otherWindows components, which is why the AV systems had such problems withSlammer last year. Is this true?Thanks in advance
View 1 Replies
View Related
Mar 27, 2008
Any working source code / project out there for VC++ 2005?that I can compile and start modifing to do SQL Server 2005 connectionthen do some query?
View 1 Replies
View Related
Jul 20, 2005
Dear all,I am new to this product and am willing to gain some knowledge. Pleaseadvise where to start and any books or free material available on the net.Thanks,Guju
View 2 Replies
View Related
Aug 9, 2007
How i can create an application in windows mobile 6 using C# and SQL Server compact edition?
View 4 Replies
View Related
Jul 13, 2007
hi
i want to add job that runs on a specific time of the day in sqlserver 2005 how?
thanks in advance.
View 3 Replies
View Related