|
thank's God it workes by Between
select * from MessageAccepted where convert(varchar(10),AcceptedDateTime,103) between '22/10/2007'and '23/10/2007'
but i have a Question
if i made the query as above it return 3 record
and when i change in convert method just only the varchar size to 8 as example its return to me 2 record
can u tell me the reasion please
Kareem Elhosseny
|
|
|
|
|
Why you need to change varchar length? Can't be length 15 or you need length 8?
I Love T-SQL
|
|
|
|
|
no i just try but it work without datatype size because its an optional parametr in convert method
here is my account kareem_elhosseny@hotmail.com
you can add me in your contact list
i hope to be friends to exchange experiance
thank's alot my brother
Kareem Elhosseny
|
|
|
|
|
Yes, you can let it without specify size of varchar, but it is more usable if you specify size.
My add is s_voca@hotmail.com
I Love T-SQL
|
|
|
|
|
I would strongly recommend that you use the following convert statement for your dates when doing comparisons:
convert(char(8),getdate(),112))
as it will return your dates in ISO format which means that comparisons will be valid every time.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
We have an application where we do not want to close the cursors after a commit.
I am testing the app with SQL Server 2005 SP2 on Windows XP Professional with ODBC and SQLNativeClient
I have done the following
- The server option "Close cursor on COMMIT" is not enabled
- The database option 'cursor close on commit' is not enabled
- In the connection code of my VC++ 2005 app I wrote
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_PRESERVE_CURSORS, (SQLPOINTER)SQL_PC_ON , NULL);
SQLSetConnectAttr(*phDbConnect, SQL_AUTOCOMMIT , (SQLPOINTER)SQL_AUTOCOMMIT_OFF, NULL);
In spite of these configurations SQL Server closes the cursors when a commit is done. What should I do?
I also tried the following ExecuteSql bevor executing a query:
ExecuteSql("SET CURSOR_CLOSE_ON_COMMIT OFF");
The reason, why I need open cursors after a commit is very simple:
What I do is
1) getting all rows from table y
SELECT xxx FROM y
2) fetching over each row from y
while (FETCH)
3) updating table z inside the fetch of table y
UPDATE z SET xxxx WHERE....
4) committing the changes made to table z
COMMIT
5) while-end
On 4) SQL Server closes also the cursor for the SELECT statement from 1)  ...
Thank you very much
Mat
|
|
|
|
|
Right off the bat it seems like a rather complex way of updating some rows (cursors are usually a bad way of doing such things, but sometimes needed), but that aside....
is there any need to wrap it in transaction and thus committing after each update of Z?
And if you want it in an transaction - wouldn't it be better to only committing after you're done updating all rows of z?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
Hello!
Is there any way to check if Referential Integrity is implemented in Stored-Procedures?
Not a single relationship is defined on DB tables!!!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
What do you want to check?
Referential integrity is on table level, so I'm unsure what you are trying to accomplish?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
In normal circumstances, Referencial Integrity (RI) is used on table-level, by defining relationships FKs and PKs....but in special circumstances RI can be implemented at Stored-Procedure (SP) level.
Example:
In proper LDM:
table1.col1 = PK
table2.col1 = PK
table2.col2 = FK
Relationship created and its done!
In this special case:
If table1 and table2 are to linked, in SPs where-ever table1.col1 is inserted or changed or updated, table2.col2 is also changed!
The problem of this approach is, we have to keep all the relationships intact manually and there is no record of which table is related to which table!!!
So the problem is, if we have such system implementation and we dont know what relationships exist (offcourse we didnt implement the system) how can we find it out?
Can we do that finding the update on each table in each SP code?
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
If this is SQL server, you could use sp_depends on each table to get a list of the stored procedures that reference each table, but still a lot of manual work to determine if the reference is RI or not.
No design documents I guess.
|
|
|
|
|
Thanks for the reply! Very right, no documentation!
Can you please drill-down on manual work? Like what sort of work!  I am willing to do that but I am out of ideas (reasonable ideas i.e.)!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi all,
Is there anyway to start Syschronizing process from SQL Query ?
I'm using SQL 2000 Merge replication and Synchronizing time is 11:00 AM (Once A Day).
I'm looking for some ways to use SQL query to do synchronizing.
Can somebody help me , please ?
Thanks
Richard
|
|
|
|
|
SQL Server has system stored procedures which allow you to script anything you need to in order to automate replication: http://msdn2.microsoft.com/en-us/library/ms147302.aspx[^]
It's all in BOL. The above link is a good place to start, it even has a script in it which starts merge replication. But I suggest you read through the docs to determine exactly the settings you want to use.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Dear Mark,
Thanks a lot. That's exactly what I am looking for.
Thanks again.
Cheers !
Richard
|
|
|
|
|
Anybody who worked on sql server reporting, can you please tell me how to improve performance of any report.
Thanks & Regards
Mishra
|
|
|
|
|
Your question is a little too broad for anyone to be able to answer. The performance of your reports is directly coupled to the performance of the queries used to supply the data for your reports. So the improve the performance of any report you could tune your query.
If you've already tuned your query look into the administration options which allow you to schedule the report to run every so often and cache the output, for example you could have it run every night. As long as you don't need real-time data, which most reports don't absolutely need, then your users will not have to wait so long for the report and it won't bring down your database when you have multiple users running the same report.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
I m using this SP in SQL 2005 and Getting this error plz help me
Warning: Null value is eliminated by an aggregate or other SET operation
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[systek_salesRpt]
@username varchar(50) = NULL,
@RoleID int = NULL,
@ItemTitle varchar(50) = NULL,
@jumpid int
AS
BEGIN
CREATE TABLE #saletemp
(
SubscriptionID int,
Noofsales int,
subscriberID int ,
OwnerID int,
Rolename varchar(50),
Type varchar(50),
Itemid int,
Totalprice float,
Commission float,
RoleID int,
ItemTitle varchar(50),
CreatedDate DateTime,
Username varchar(50)
)
INSERT INTO #saletemp(SubscriptionID,Noofsales,subscriberID,OwnerID,Rolename,Type,Itemid,Totalprice,Commission,RoleID,ItemTitle,CreatedDate,UserName)
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(D.VideoID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
D.VideoTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'videos')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='videos')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
INNER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,B.CreatedDate,H.UserName
UNION ALL
------------------------------------------------------------------series-------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(E.SeriesId,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
E.seriesTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'series')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='series')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,E.seriesTitle,B.CreatedDate,H.userName
UNION ALL
-----------------------------------------------------------group id----------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(F.GroupID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
F.GroupName AS ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'Groups')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='Groups')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,C.RoleID,A.RoleID,F.GroupName,B.CreatedDate,H.username
----------------------------------------------------removing duplicate subscrptionid---------------------------------------------------------
DECLARE @Count int
DECLARE @SubsCriptionID AS int
DECLARE vendor_cursor CURSOR
FOR
SELECT SubscriptionID,Count(SubscriptionID) - 1 FROM #saletemp Where RoleName <> 'smartIcast' GROUP BY SubscriptionID HAVING Count(subscriptionid) > 1
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #saletemp WHERE #saletemp.SubscriptionID = @SubsCriptionID
SET ROWCOUNT 0
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
----------------------------------------------------------------------------------------------------------------------------------------------
IF @jumpid = 0
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
END
IF @jumpid = 1
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
Where
RoleID = @RoleID
OR
ItemTitle LIKE '%'+ @ItemTitle + '%'
OR
username LIKE '%' + @username + '%'
END
DROP TABLE #saletemp
END
|
|
|
|
|
hyderalishah wrote: Warning: Null value is eliminated by an aggregate or other SET operation
This is a warning, notan error. It is because you are doing calculations on columns which can be null. It is warning you that those values (i.e. the null) will not be included. For sum(), count() and so on this is nt usually important, but it will be gor avg etc.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I created a stored procedure in MySQL which returned one row in the result set. Since there is only one row in the table, this it what was supposed to happen. I had to add anouther column to the table, which I did without any problem. Then I modified the SELECT...FROM...WHERE query to include the column, no errors so it should be working correctly. The problem is that now it is returning 25 rows in the result set, all containing the same information. If I add another row to the table, it returns 50 rows - 25 copies of each row in the table.
How is that possible?
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
Can we see your query?
I Love T-SQL
|
|
|
|
|
|
WHERE locations.id = _locationID
This _locationID is parameter? If it is not parameter then must be table and u must specify column like :
WHERE locations.id = _locationID.Column
Also can u add in WHERE clause column destiny with any condition?
I Love T-SQL
|
|
|
|
|
Sorry I had to delete that mess. The 5 WHERE id compares and 5 SELECT IF() were the cause of the 25 returns - 5 x 5. The solution I came up with was to LEFT JOIN the series of required rows and remove the comparisons from the WHERE.
FROM locations,
loc_staticdata,
loc_staticdata_waters AS sw
LEFT JOIN textures AS d1 ON sw.surfaceTextureID = d1.id
LEFT JOIN textures AS d2 ON sw.shoreTextureID = d2.id
LEFT JOIN textures AS d3 ON sw.envMapOverTextureID = d3.id
LEFT JOIN textures AS d4 ON sw.envMapUnderTextureID = d4.id
LEFT JOIN textures AS d5 ON sw.specularMapTextureID = d5.id
WHERE locations.id = _locationID
AND locations.staticDataID = loc_staticdata.id
AND sw.staticDataID = loc_staticdata.id;
I am not sure if it is the best solution, but it works.
Thanks for the attempt. 
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
I am glad that u found solution, the most important is to work your query, the performance gonna be second with which we have to lookup...
Happy query-ing... 
I Love T-SQL
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
