I have a query stack that uses local variables that I declare & set at the top. That stack takes a minute & runs the processor at 100%. If I substitute actual values in place of the variables the stack runs in 10 seconds! Why!?.. I need it to run that fast with variables!
-Brian Flynn
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
As a burgeoning SQL developer I have never really understood the need for SQL Server / Enterprise Manager to show us the whole dbo.Table name thing. What is dbo, and why do we need to know deal with it?With that said, in my SQL 2005 Express database all of my project tables for my project management demo were named guard.pgUsers for example and not dbo.pgUsers.How come? Why did they get named different on their own?
View 2 Replies View RelatedHello All,
Below carry takes too much time while execution
Select
'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END,
Best_Bid_Data.Bid_Customer,
Best_Bid_Data.Bid_Size,
Best_Bid_Data.Bid_Price,
Best_Bid_Data.Bid_Order_Id,
Best_Bid_Data.Bid_Order_Version,
Best_Bid_Data.Bid_ProductId,
Best_Bid_Data.Bid_TraderId,
Best_Bid_Data.Bid_BrokerId,
Best_Bid_Data.Bid_Reference,
Best_Bid_Data.Bid_Indicative,
Best_Bid_Data.Bid_Park,
Best_Offer_Data.Offer_Customer,
Best_Offer_Data.Offer_Size,
Best_Offer_Data.Offer_Price,
Best_Offer_Data.Offer_Order_Id,
Best_Offer_Data.Offer_Order_Version,
Best_Offer_Data.Offer_ProductId,
Best_Offer_Data.Offer_TraderId,
Best_Offer_Data.Offer_BrokerId,
Best_Offer_Data.Offer_Reference,
Best_Offer_Data.Offer_Indicative,
Best_Offer_Data.Offer_Park
from
(
Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version,
ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId,
Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select max(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Bid_Data
full outer join
(
Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version,
ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId,
Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select min(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Offer_Data
ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
Can any one please help me?
Thanks
Prashant
After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.
I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.
Am I F&%ked?
Any help in sorting this out will be rewarded with much kudos and thanks
I try to do:
DECLARE
@ThisMinute DateTime,
@ThisDay DateTime
SET@ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET@ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')
SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
PortalID=0
GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC
Which works fine, but when I do:
DECLARE
@ThisMinute DateTime,
@ThisDay DateTime
SET@ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET@ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')
SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
PortalID=0
AND
Expiredate <> Null
GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC
it doesn't return anything....
How can I change that
The secret to creativity is knowing how to hide your sources. (Einstein)
Hi,I have a corporate database with about 60 different tables that spansmanufacturing, accounting, marketing, etc.It is possible, but unwieldy, to establish a relationship for eachtable in the entire database through critical fields like customer_idor product_id.But should I do that?My question is: Is there such a thing as too many relationships? CanI establish referential integrity via relationships with criticaltables like Accounting, but leave the rest unconnected and simply useJOINS in my business code?Thanks,HC
View 4 Replies View RelatedHey all,I have a datagrid with populated by this query: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')I have paging, sorting and selection enabled.Now I am looking for a way to use a wild card as a placeholder for the table name in my select statements so I can use the valued selected from the datagrid.Example : SELECT * FROM %TABLENAME%TIAWOOHOO! my first post.
View 2 Replies View Relatedi got this stored procedure.
i tried to modify it and now its giving me this scaler error.
Msg 137, Level 15, State 2, Procedure insertuser, Line 4
Must declare the scalar variable "@seller_id".
USE [DBCars]
GO
/****** Object: StoredProcedure [dbo].[insertuser] Script Date: 05/23/2008 20:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insertuser]
(@Make nchar(10),@Model nchar(10),@City nchar(10),@SellerID varchar(50),@MileAge nchar(10),@Year_Model int)
as
insert into tbcar values(@make,@model,@city,@seller_id,@mileage,@year_model);
I'm have a stored procedure that iterates through a list of numbers and adds an item for each number (user id) some of these ids are duplicates which is fine even necessary for the first part of my query but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:
SET @Note_Buffer = @UserID -- @Note_Buffer being some kind of array?
IF @Note_Buffer = @UserID -- If its been added to the buffer we dont execute sp
BEGIN
Do Nothing here
END
ELSE
BEGIN
EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID
END
I know this would never work because it would always be false since I just added the same userid to the buffer that I want to add. But I think you see my problem. I know it should be an easy one but my TSQL is limited. I've posted the whole sp. Hope someone can help.
CREATE PROCEDURE spInsertAssignedNotesByList
@FK_UserIDList NVARCHAR(4000) = NULL,
@FK_NoteIDList NVARCHAR(4000) = NULL,
@By_Who INT,
@UserID INT
AS
SET NOCOUNT ON
DECLARE @Length INT
DECLARE @Note_Length INT
DECLARE @Note_Buffer INT
DECLARE @FirstUserIDWord NVARCHAR(4000)
DECLARE @FirstNoteIDWord NVARCHAR(4000)
DECLARE @FK_UserID INT
DECLARE @FK_NoteID INT
SELECT @Length = DATALENGTH(@FK_UserIDList )
SELECT @Note_Length = DATALENGTH(@FK_NoteIDList )
DECLARE @TempFK_NoteIDList NVARCHAR(4000) --= NULL
DECLARE @Temp_NoteLength INT
SET @TempFK_NoteIDList = @FK_NoteIDList
SET @Temp_NoteLength = DATALENGTH(@FK_NoteIDList )
-- IF @Length > @Note_Length -- If we have more users than notes
BEGIN
WHILE @Length > 0
BEGIN
IF @Length > 0
EXECUTE @Length = PopFirstWord @FK_UserIDList OUTPUT, @FirstUserIDWord OUTPUT
SELECT @FK_UserID = CONVERT(INT, @FirstUserIDWord)
IF @Length > 0
BEGIN
SET @FK_NoteIDList = @TempFK_NoteIDList
SET @Note_Length = @Temp_NoteLength
WHILE @Note_Length > 0
BEGIN
EXECUTE @Note_Length = PopFirstWord @FK_NoteIDList OUTPUT, @FirstNoteIDWord OUTPUT
SELECT @FK_NoteID = CONVERT(INT, @FirstNoteIDWord)
IF @Note_Length > 0
EXECUTE spInsertAssignedNoteDetail @FK_UserID, @FK_NoteID
SET @Note_Buffer = @UserID
EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID, @By_Who, @UserID -- NEW HERE
END
END
END
END
--------------------------------------------------
GO
I have this field in a table.
NAME
--------------------
Johnson, Bill P.
I need to cut it up into this in another table:
LAST_NAME FIRST_NAME MID_INIT
-----------------------------------------------------
Johnson Bill P
I can't use SUBSTRING command because the length of this field will change with every row.
Is there a way I can tell it to truncate past or prior to the comma or spaces?
Thanks!
Lance
Hello all,
I'm working on an ASP.NET with a SQL server for database. Some of the tables, for example, contain information such as different types of Fabrics (silk, cotton, etc..) . I'd like to have this table localizable (English and French for instance). Is this possible ? Is there an equivalent of resource files in SQL server ?
Or do I have to do this manually ? (have 2 separate fields in the table for those 2 locales)
This should be so easy.
All I want to do is have a cumalative counter that counts from 1 to whatever.
any ideas how i can do this?
I have a DTS Package that I am running from a command line via .bat file. Does anyone know if there is a command to have the command window minimized or running in the background? I used the /Rep N command but that still leaves the window open until the package has executed.
Thanks!
Hi ALl,
When I open the VarDecimal option in our SAP R3P system, I found the space used is increased.
Before turn on the option:
Total space:528315.31MB
Fee Sapce:0.00MB
After trun on the option:
Total Space:733815.31MB
Free Space:119006.31MB
it is very interestion, anyone has this experience?
thanks.
in MySql:
SELECT * FROM table LIMIT 5,10
How to do this in MicroSoftSql?
In a previous post, someone helped me with creating stored procedures, and I am grateful because I am transitioning from the Access World.
Anyway, I get an error at .ExecuteNonQuery in visual studio 2005 when I run the following code:
Dim strsql As String Dim strconn As String strsql = "sp_Roster" strconn = "server=xxxx; user=xxxx; pwd=xxxx; database=xxxx;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = classid End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With
The error was: Error converting data type char to int.
The stored procedure in sql server was as follows
CREATE PROCEDURE sp_Roster -- Add the parameters for the stored procedure here@TeacherID varchar(50),@ClassID int,@sID int,@sLastName varchar(50),@sFirstName varchar(50),@sMiddleName varchar(50),@Student varchar(50),@Password varchar(50)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO
The error also says "sqlexception was unhandled by user code.
This is strange because this code worked perfectly when connecting to Access and when I used oledb.
So how is it the code's problem? Is the stored procedure causing the error or the code. Can someone please help. thanks.
<Edited by Dinakar Nethi>
Please mask your useird/pwd info in the connection string when posting to a public forum like this
</Edit>
I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1 to Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx
View 4 Replies View RelatedEvery morning our sql server runs very slowly which means our log on
page times out on a simple query. If we stop and start the sql server
everything runs fine for the rest of the day until the follwing
morning. the server is not used out of business hours excpet for a few
very small and simple jobs to delete records, These all run to
completion. Any help with this would be much aprreciated! Thanks.
Rob
Hi ..
i am SqL beginner. i having trouble output what i want from table.
table contain 3 columns
________________________________
|(names)|(item)|(location)|
1.| Jimmy | pizza| TX |
2.| Joe | ball | CA |
3.| Joe | ball | WA |
4.| Jim | shoes| AZ |
________________________________
i try to select all records out from this table. but column 2 and 3 contain same information in names and item only different is location. how can distinct one of them?? and display like the below, please advise.
|(names)|(item)|
1.| Jimmy | pizza|
2.| Joe | ball |
3.| Jim | shoes|
________________________________
My thread titles need work, I know. :o
Ok, lets say I've got:
tblDocuments
id INT PK
documentName VARCHAR
tblUsers
id INT PK
userName VARCHAR
tblDocumentApprovals
userID INT
documentID INT
approvalDate DATETIME
If I want to get a list of documents, and the users who've signed them off (if any), I'd do something like:
SELECT [tblDocuments].[documentName], [tblUsers].[userName ], [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
LEFT JOIN [tblDocumentApprovals] ON [tblDocumentApprovals].[documentID] = [tblDocuments.id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblDocumentApprovals].[userID]
...which is lovely. Except - I don't want a row returned for each user that's signed it off. I want one row for each document, with a field containing a list of the people who've signed it off.
I know that it's bad design. I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.
How do you do it?
Hi,
I have seen many examples at MSDN library related to SQL Querries in all queries one thing is same the way they use tables in there queries. BUT wht is this really i am not getting this, can anyone tell me.. the code and the problem is as follows:
Code Snippet
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
ON (ProductVendor.VendorID = Vendor.VendorID)In the above code u see "Purchasing.ProductVendor", I want to ask that wht is this
Purchasing Stands for, If we suppose that purxchasing is the database name then also
normally we use database name as Purchasing.dbo.ProductVendor BUT I am not getting that
what is this,
Please if someone knows then explain it to me,
Thanks.
Hi, experts here,
I found a very strange problem with the OLE DB connection in SSIS package though.
What happened was when I set the connection type to be OLE DB, whenever I declare a package variable it still gives me the error saying: please declare the scalar variable.
Once I changed the connection type to be ADO.NET, then it works fine.
Why is that? Anything wrong with OLE DB connection?
Looking forward to hearing from you for any ideas on it.
Best regards,
...we understand that perhaps some xp's might come with the product, but for sp's that we author ourselves, are they two different things in 2005, and if so, why would one be chosen over the other?
View 1 Replies View RelatedDear Anyone,
View 6 Replies View RelatedI have a table which contains suppliers that can supply certain articles.
There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
As a alternative you can use priority. Each supplier has given priority where
0 means "most preferred" and any other means "less preferred"
What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.
Abbreviated description of the table, called AL.
SuppNo Article Arthuvudavt Prio
10101 A-10 NULL 0
10202 A-10 1 0
10303 A-10 NULL 1
10101 B-10 NULL 0
10202 B-10 NULL 0
10303 B-10 NULL 1
10101 C-10 NULL 1
10202 C-10 NULL 0
10303 C-10 NULL 1
In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.
Is there any way this could be done?
The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.
Thanks in advance
Jonas
Sorry for the poor formatting, I didn't have the time to make it right.
1) I have the Beta Preview of Roger Wolter's book on the subject. Has anything major changed from the beta version to the full version (of the book as well as the product)?
2) I want to build a front-end that queues requests for processing that is ultimately done by a standalone console application. The console app knows nothing about SQL Server 2005, so the database will have to fork/execute this app, pass it arguments on its command line, and pick up the result (written to either standard output or standard error) when it finishes. Is this possible with Service Broker? If so, how?
TIA
Andrew
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
My asp.net application is attached with SQL database, I record only time in my SQL database , field type is nvarchar. Now I want to do qurey by time and pull the result , but datatype of field in nvarchar, query does not giving me right result.
some one tell me how Do I do query so I can get proper result.
thanks maxmax
I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time
Can some one show me sample SQL query so I can pull informatin on particular day between two times
thank you
maxmax
How do I find all the records within a time range when all the datetime's are on different days?
Example: Show me all the order placed between 12:00 and 15:00 in May
hi,
how can i define a simple time-out exceed when running a simple select query like
select a from atable where a > 1
and define if query doesn't give results in 5 sec, it should be stopped.