Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
I am working on a stock price analysis project. Stock prices are stored in SQL Server as tick by tick data (that means in a high frequency) and need to be grouped by minutes in order to display them as as high, low, open and close prices in a candlestick or similar chart.
The fields in the table are:
Time (for every stock price tick)
Price (stock price)
Symbol (of the stock)
A sample time series would look like this:
11:45:01 $22.11 MSFT
11:45:02 $22.10 MSFT
11:45:04 $22.25 MSFT
...
...
...
11:45:58 $22.45 MSFT
11:46:03 $22.22 MSFT
11:46:08 $22.25 MSFT
...
...
...
11:46:59 $22.45 MSFT
The result of the SQL query should be:
Symbol Time Open High Low Close
MSFT 11:45 $22.11 $22.45 $22.10 $22.45
MSFT 11:46 $22.22 $22.45 $22.22 $22.45
This is the SQL statement I came up with but obviously doesn't work:
SELECT DATEPART(Minute,Time), MIN(Price), MAX(Price) FROM dbo.TimeSales WHERE Symbol='MSFT' GROUP BY DATEPART(Minute,Time)
I would appreciate any suggestions.
Thank you!
I'd like some ideas on how to help out my VB staff when it comes to the tick ( ' ) mark being used in front end applications. When users enter data into the front end, and they include tick marks (eg: Sam's Auto Store), this causes trouble in how the app uses that data in a WHERE clause and other places. Does anyone have any slick ideas or experience in handling this. We have experimented with search & replace, double tick marks ( '' ), etc...
View 1 Replies View Related
I am working on a project to import information from one database table to another. This info includes names, addresses, etc. I am running into a challenge whenever one of the names, such as O'Brien, causes problems in the sql statement. Is there a way for me to tell it to ignore the ' in O'Brien?
Thanks,
Brian
Assume you have a table called Tick with 2 columns
(
tickId bigint IDENTITY(1,1)
, price int -- usually money data type, making it int for simplicity
)
I am tasked with creating bars that are 10 units long.
Now the catch is I'm not looking for the tickId where price is >= t1(price) + 10 where t1(price) is the price for the first row where tickId = 1. (it could also be where price <= t1(price) - 10)
Here is sample data:
1, 25
2, 26
3, 23
4, 26
5, 27
6, 30
7, 34
8, 32
9, 30
10, 33
What I am looking for are rows 3 (23) and 7 (34)
Currently I have:
Code:
DECLARE @tickDiff int
SET @tickDiff = 10
DECLARE @r1TickId bigint
[Code].....
This seems to work but it is taking multiple minutes to run for about 50k rows of data (which I created off of the 24 million row table I have just looking at data from today). So it takes ~5 minutes to create the first bar which is not acceptible.
If my logic above seems acceptable are there any indexes you could recommend. Database engine tuning advisor didn't find any.
I'm trying to do the equivalent of an Excel chart "Number of categories between tick-mark labels" on the X-Axis of a SSRS chart. Can't see anyway to do it. I can get it to display differently by doing Label = IIF(somethingistrue, onevalue, anothervalue), but can't see anyway to simply not show the label at all.
Any suggestions.
Thanks
Richard
Is it possible to remove the tick marks that I have circled in red?The chart has data Week by week over a year, and the T1, T2 etc are equivalent to Quarter1, Quarter 2 etcI've got a 3 level grouping, with the top level being Year, then Quarter, then Week. The label for the "Week" grouping is set to blank, and that's why you don't see it here.Removing the Tick marks would make the chart legend more readable in my opinion.
View 2 Replies View Related
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
Any help is appreciated.
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
Any help would be appreciated.
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Any insight to this would be appreciated.
This is probably a simple task but I just don't know how to do it. I need to return a recordset of details with a row of totals for selected columns. Something with a result like this:
Location Attendance Showings
======== ========== ========
JOHNS 210 3
SEREN 116 2
total 326 5
I know I could do this with a stored procedure, but I'd prefer to do it with just one sql statement. I tried compute but because I have a total on more than one column, the returned recordset is actually 3 rows.
Thanks in advance for any suggestions.
Hi everyone,
Is there a way in SQL to include and execute another file? For example, I have file1 and I would like to execute the contents of file2 from file1. How can this be done?
Thanks!
Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:
Transaction table
Emp Trans Tot...
X55677 S 8
X55677 R 2
C22887 S 4
C22887 T 3
F66889 S 9
F66889 R 4
F66889 T 3
Code table
S
R
T
Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...
Emp Trans Tot...
X55677 S 8
X55677 R 2
X55677 T 0
C22887 S 4
C22887 T 3
C22887 R 0
F66889 S 9
F66889 R 4
F66889 T 3
How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.
Thanks for your thoughts.
select *
from dbo.RPT_ContractDetails
where businessname like '%carl's%'
carl's should be a string.
Thanks
Hi, I have the following query stored:
SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)
Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...
As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.
The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.
Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.
I'd get
Prod | Qty | Week
1 23 1
1 12 2
2 10 3
3 22 1
3 15 2
3 12 3
3 4 4
Although this looks fine - what I actually need is:
1 23 1
1 12 2
1 0 3
1 0 3
2 0 1
2 0 2
2 10 3
2 0 4
3 22 1
3 15 2
3 12 3
3 4 4
Does that make sense?
Any ideas on how to do this?
We today have 3 different indexes on a table that look like this
CREATE INDEX IX_01 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX])
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
CREATE INDEX IX_03 ON [TORDLOG] ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [RADNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
I want to combine the index to only 1 index; How do can combine all the include columns to one large? Or shall keep all index. Bit bad on how to handle include columns. Or just create new index with regdat and forget all about include?
CREATE INDEX IX_02 ON TORDLOG ([REGDAT])
OR
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE (ALL COLUMNS)
Hi:I have an application in which I am producing a lists of media outlets based on a number of 6 possible parameters including location, medium, format, scope, language and coverage. I have an interface in which users can select some or all of the possible parameters. Where they don't choose a parameter - e.g. location, I would like the SELECT query to include all locations. I am at the point now of writing 6 X 6 = 36 possible queries to handle the different combinations of possible parameters. This seems a bit excessive and clumsy. I am just learning about stored procedures and was wondering if it is possible to create a procedure that would accept the 6 parameters including ones with null or 0 values and generate a query that would ignore those that were null or zero. Any help in this regard greatly appreciated.Roger Swetnam
View 5 Replies View RelatedHi Guys,
Is it better to create all tables (in store procedure) and then another store procedure to create and apply all relationship between the tables??? , OR
is it better to have each table relationship at the end of the store procedure to create that table and have each table relationship at the end of its create statement???
What are the Advantages and disadvantages of each method?
Thanks,Mehdi
Hi I have a query that return set of records between 2 given timestamps. What I want to do is to include one record before and one record after to the result. For example I have the following dataRow Timestamp1 01/01/20072 15/03/20073 17/04/20074 05/05/20075 10/05/20076 11/06/20077 12/07/2007 My query returns row 3 to 5 for example and I want to include row 2 and 6 to the result as well. How can I do that? Is it possible to do it in 1 query?
View 11 Replies View RelatedHi,
I have a test database and need to to include the aspnet.mdf into my database.
I have generated a script for the aspnet.mdf, but don't know how to run/include this into my own test database.
1. Is there any magical sql-command that do the job in a clean way for me?
2. How can I change the name of aspnet in that auto generated script, if needed at all?
I have: SQL server Express Edition 2005, Visual Web Developer Express Edition 2008
Thank's
/Pepe
I have a table of parts and want to display some statistics from it. The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query: SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)" OnSelecting="sqlPartCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> </SelectParameters>Code-behind: protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; } 2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get. SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)" onselecting="sqlPartYearCount_Selecting"> <SelectParameters> <asp:Parameter Name="PartUserID" /> <asp:Parameter Name="DateUseByID" /> </SelectParameters>Code-behind: protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey; // ? How do I change the date in the field to be in "2008" format and then put that in the sql query? //e.Command.Parameters["@DateUseByID"].Value = currentYear; //currentYear currentDateTime = DateTime.Now; }
View 7 Replies View RelatedI want to retrieve some data in my gridview using a sqldatasource. Here's the idea. My Gridview contains events. A user can subscribe into one of them. When he subscribes, the event must be removed in the gridview. So when there is a subscription from that certain user for that event, it may not appear. Here's my not working code..SELECT Event.EventID,Event.name, Event.LocationID, Event.Date, Event.StatusFROM Shift INNER JOIN Event ON Shift.EventID = Event.EventID INNER JOIN Subscription ON Shift.ShiftID = Subscription.ShiftIDWHERE (Subscription.UserID <> @UserID) Greetz
View 1 Replies View RelatedI used to be able to write a query like this:
select * from foo;
But after re-creating my database using RESTORE, I now have to do this:
select * from myName.foo;
...where myName is either the name of the database or the name of the database owner. That is, it's the name of the database, but I believe it's also the name of the database owner.
How do I go back to being able to just use the table name?
Thanks!
I want to pull ALL users from Table1 and include information that person may have in Table2. As of now, it is only including users that have something in Table2. How do I include everyone?
Thanks
Hi,I have 2 tables created. One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)PersonID | PersonName PersonID | Month | Year | Total Sales================== ==================================ID1 | Sally ID1 | 1 | 2005 | 1000ID2 | David ID2 | 2 | 2005 | 1500Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)I would like the result to be like the following:PersonID | Month | Year | Total Sales===================================ID1 | 1 | 2005 | 1000ID1 | 2 | 2005 | 0ID1 | 3 | 2005 | 0(For month 4 - 12) Total Sales will be 0 too as no records exist in the first placeID2 | 1 | 2005 | 0ID2 | 2 | 2005 | 1500..........
View 2 Replies View RelatedI have a query with 4 parameters:
Name
Location
Employee Number
Officer Code
There are no null values in Name, location and Employee Number. However, all employees do not have an officer code. So in my query I use a where clause that says WHERE OfficerCode LIKE '%'+@Param4+'%'. I use a % for the default value in my ASP.net data control. The only problem is that the only records returned have non-null values in the OfficerCode field. How can I use a wildcard for a default value and return all records (null and non-null)?
I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000
View 2 Replies View RelatedI have this but how can I get all the columns of the row? I only want them distinct per these 2 cols.
SELECT DISTINCT OrdHst.ordernbr, OrdNbr.trans
FROM OrdHst JOIN OrdNbr
ON OrdHst.ordernbr = OrdNbr.ordernbr
ORDER BY 1,2
Could return:
12345 001
12345 AAA
12345 BBB
12345 CCC
12345 PWB
how to include asc near order by
select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + '%'' order by u.' + @columnname
I have about 150 people I would like to send e-mail automatically. Eachperson would get a unique form letter that includes username andpassword I have stored in a SQL table. Is this possible? Helpappreciated. Thanks.Frank*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
when i tried to put an orderby in view...i am getting an error...
we can't have a orderby in view?.
When I include link in e-mail from RS I get
"The report is accessible at the
following address:"
before the link.
Can I change this text?