Viewing 15 posts - 54,601 through 54,615 (of 59,078 total)
jezemine (10/13/2007)
any DBA that doesn't allow use of xp_cmdshell is likely not going to allow the import of...
October 14, 2007 at 9:32 am
What do you mean by "client side data table", Lowell?
October 13, 2007 at 8:26 pm
When you get what you want, please post your solution. Thanks. 😉
October 13, 2007 at 8:24 pm
Heh... you just need to look it in the eye... 😉
[font="Courier New"]
--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10), Date DATETIME)
 INSERT INTO @yourtable (FromUser, ToUser, Date)
 SELECT 'user1','user2', '20070115' UNION ALL
 SELECT 'user2','user1', '20070116' UNION ALL
 SELECT 'user1','user2', '20070117' UNION ALL
 SELECT 'user1','user3', '20070118' UNION ALL
 SELECT 'user3','user1', '20070118' UNION ALL
 SELECT 'user1','user2', '20070201' UNION ALL
 SELECT 'user2','user1', '20070205' UNION ALL
 SELECT 'user1','user2', '20070301' UNION ALL
 SELECT 'user1','user3', '20070310' UNION ALL
 SELECT 'user3','user1', '20070311'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, d.YrMon, COUNT(*) AS TimesConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, 
                ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser, d.YrMon
[/font]
October 13, 2007 at 8:22 pm
You really think filtering on an OPENROWSET would be faster than just importing the table?
October 13, 2007 at 4:46 pm
This isn't Oracle... no need to slow down the update with correlated subqueries like that... just do a simple join like some of the other posters have.
Recommend you lookup UPDATE...
October 13, 2007 at 4:36 pm
CASE WHEN a.col1 = b.col1 THEN 1 ELSE 0 END
+ CASE WHEN a.col2 = b.col2 THEN 1 ELSE 0 END
+ CASE WHEN a.col3 = b.col3 THEN 1 ELSE 0 END
+...
October 13, 2007 at 4:27 pm
Sure...
[font="Courier New"]--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10))
 INSERT INTO @yourtable (FromUser, ToUser)
 SELECT 'user1','user2' UNION ALL
 SELECT 'user2','user1' UNION ALL
 SELECT 'user1','user2' UNION ALL
 SELECT 'user1','user3' UNION ALL
 SELECT 'user3','user1'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, COUNT(*) AS TimeConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, ToUser
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser[/font]
October 13, 2007 at 4:17 pm
SQL Server has a LEFT and a RIGHT... no need to build a special function to do it as bad as Oracle 😉
October 13, 2007 at 4:04 pm
Sure... A thing called "Books Online" should become your best friend. One way of getting to it is to open Query Analyzer, click on the [Help] button, and select...
October 13, 2007 at 4:00 pm
Could be any of a dozen different problems... you need to post the code you're using and the CREATE statement for the table you are importing to.
October 13, 2007 at 10:56 am
Grant, I'm with you... I see no compelling reason for CLR's. My opinion, for the most part, for them being there is the same as DTS and Cursors... they...
October 13, 2007 at 10:54 am
The easiest way to split limited (up to 4 parts) period-delimited text of this nature is to use PARSENAME. Look it up in Books Online for a full explanation.
Here's...
October 13, 2007 at 10:32 am
This is very nearly a duplicate post...
http://www.sqlservercentral.com/Forums/Topic410280-65-1.aspx
The only thing that means anything about performance for all the timers you have here is this...
Number of TDS packets received 28569 28569
Number of...
October 13, 2007 at 10:20 am
Viewing 15 posts - 54,601 through 54,615 (of 59,078 total)