Restore Database: below

Showing posts with label below. Show all posts

Thursday, March 29, 2012

Help with cursor and decimal values

The following table when using a cursor gives 0 for decimals where the value
s
are below 1. I am trying to use this to apply a factor and always get a zero
if the factor below 1, as a result my logic fails.
Can some one help me on this?
-- ========== Table & data =============
CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
INSERT INTO TestFactor VALUES (1, 0.25)
INSERT INTO TestFactor VALUES (2, 0.50)
INSERT INTO TestFactor VALUES (3, 0.75)
INSERT INTO TestFactor VALUES (4, 0.125)
INSERT INTO TestFactor VALUES (5, 0.25)
INSERT INTO TestFactor VALUES (6, 2)
INSERT INTO TestFactor VALUES (7, 1)
-- ========== Table & data =============
DECLARE @.Factor decimal
DECLARE my CURSOR
FOR SELECT FactorValue from TestFactor
OPEN my
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
END
CLOSE my
DEALLOCATE my
Thankschange DECLARE @.Factor decimal to
DECLARE @.Factor decimal (5,2) -- same as in the table
http://sqlservercode.blogspot.com/|||Change
DECLARE @.Factor decimal
to
DECLARE @.Factor decimal(5,2)
"Ram" wrote:

> The following table when using a cursor gives 0 for decimals where the val
ues
> are below 1. I am trying to use this to apply a factor and always get a ze
ro
> if the factor below 1, as a result my logic fails.
> Can some one help me on this?
> -- ========== Table & data =============
> CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
> INSERT INTO TestFactor VALUES (1, 0.25)
> INSERT INTO TestFactor VALUES (2, 0.50)
> INSERT INTO TestFactor VALUES (3, 0.75)
> INSERT INTO TestFactor VALUES (4, 0.125)
> INSERT INTO TestFactor VALUES (5, 0.25)
> INSERT INTO TestFactor VALUES (6, 2)
> INSERT INTO TestFactor VALUES (7, 1)
> -- ========== Table & data =============
>
> DECLARE @.Factor decimal
> DECLARE my CURSOR
> FOR SELECT FactorValue from TestFactor
> OPEN my
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> END
> CLOSE my
> DEALLOCATE my
> Thanks
>|||Thanks Mark & SQL
I was going crazy on this. I think I need a vacation
"Mark Williams" wrote:
> Change
> DECLARE @.Factor decimal
> to
> DECLARE @.Factor decimal(5,2)
> "Ram" wrote:
>

Help with complicated query...

Hello All,
I have a simple table that stores messages of different types from
different sources. The definition of the table is shown below. I need
to devise an efficient query to return a "list of the N more recent
messages for a subset of sources within a specified time frame."
create table MessageTbl
(
src nvarchar(50), -- Source of the message
type nvarchar(50), -- Type of the message.
msg nvarchar(1000), -- Text of the message
dt datetime -- When the message was posted
)
We are given the following parameters:
1. declare @.startTime datetime -- Start of the time frame
2. declare @.endTime datetime -- End of the time frame
3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
contains a list of sources/types for which we want to obtain the
messages.
4. N -- How many messages per source/type
If all I wanted was the 20 more recent messages for source1/type1
between @.startTime and @.endTime, I could do something like:
SELECT TOP 20 *
FROM MessageTbl
WHERE (src ='source1') AND ( type='type1')
AND ( dt BETWEEN @.startTime AND @.endTime )
ORDER BY dt DESC
In my case, however, I have a number of pairs (src,type) in the local
table @.myTable. Therefore, what I ultimately want is equivalent to the
UNION of the results of such query for each pair(src,type). Another
thing missing is that I am using a hardcoded value for the TOP clause.
This also varies.
All the solutions that I can think of are very inneficient, cumbersome,
and involve a number of temporary tables. I was wondering if the
experts could lead me to a cleaner query design.
Thank you
- CDOn 25 Oct 2005 14:57:37 -0700, crbd98@.yahoo.com wrote:

>Hello All,
>I have a simple table that stores messages of different types from
>different sources. The definition of the table is shown below. I need
>to devise an efficient query to return a "list of the N more recent
>messages for a subset of sources within a specified time frame."
>create table MessageTbl
>(
> src nvarchar(50), -- Source of the message
> type nvarchar(50), -- Type of the message.
> msg nvarchar(1000), -- Text of the message
> dt datetime -- When the message was posted
> )
>We are given the following parameters:
>1. declare @.startTime datetime -- Start of the time frame
>2. declare @.endTime datetime -- End of the time frame
>3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
>contains a list of sources/types for which we want to obtain the
>messages.
>4. N -- How many messages per source/type
>
>If all I wanted was the 20 more recent messages for source1/type1
>between @.startTime and @.endTime, I could do something like:
>SELECT TOP 20 *
>FROM MessageTbl
>WHERE (src ='source1') AND ( type='type1')
> AND ( dt BETWEEN @.startTime AND @.endTime )
>ORDER BY dt DESC
>In my case, however, I have a number of pairs (src,type) in the local
>table @.myTable. Therefore, what I ultimately want is equivalent to the
>UNION of the results of such query for each pair(src,type). Another
>thing missing is that I am using a hardcoded value for the TOP clause.
>This also varies.
>All the solutions that I can think of are very inneficient, cumbersome,
>and involve a number of temporary tables. I was wondering if the
>experts could lead me to a cleaner query design.
>Thank you
>- CD
Hi CD,
Try if this works:
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your reply. The query that you posted produces the
correct result but it is very expensive. It takes almost 30 seconds
when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
the execution plan and it seems there is a lot of table scanning going
on.
Maybe I have to look into a solution that does not use the join...
Any Ideas?
Thanks
- CD|||Are there any indexes? Your original DDL had no keys and you used a
proprietary table variable.
Also, you might want to fix the data element names. They are horrible.|||Hello Celko,
The table MessageTbl has a compound primary key involving src and type.
There are no other indexes. My local table variable has no keys and no
indexes. Do you think this is a problem? Is there any problem in using
table variables or are you just concerned about the portability of the
code?
Any suggestions?
Thank you
CD
--CELKO-- wrote:
> Are there any indexes? Your original DDL had no keys and you used a
> proprietary table variable.
> Also, you might want to fix the data element names. They are horrible.|||On 25 Oct 2005 16:22:34 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for your reply. The query that you posted produces the
>correct result but it is very expensive. It takes almost 30 seconds
>when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
>the execution plan and it seems there is a lot of table scanning going
>on.
>Maybe I have to look into a solution that does not use the join...
>
>Any Ideas?
Hi CD,
Try if adding this index helps:
CREATE INDEX ProperNameHere
ON MessageTbl (src, type, dt DESC)
On 26 Oct 2005 10:50:55 -0700, crbd98@.yahoo.com wrote:

>Hello Celko,
>The table MessageTbl has a compound primary key involving src and type.
Huh? If there's a compount primary key on src and type, then how can you
find the 20 most recent messages between two moments for a given src and
type? As a result of the primary key, there will be only one message for
each src / type combination!!
But since you apparently have a PRIMARKY KEY that was not included in
your first post, please post the complete CREATE TABLE statement, WITH
all constraints, properties and indexes. My suggestion above might well
be invalidated by your current keys and indexes.

>There are no other indexes. My local table variable has no keys and no
>indexes. Do you think this is a problem?
You might have duplicates in the table variable, which will never
improve performance.
You can't define indexes for a table variable, but you can define
PRIMARY KEY or UNIQUE constraints (and they DO automatically add an
index). In your case, try if adding a PRIMARY KEY (src, type) helps the
performance. And if it doesn't, but doesn't hinder performance either,
then do leave it in.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for the suggestion of creating an index with the src, type,
and dt. That reduced the execution time from 30 sec to less than a
second. My last problem was to parameterize the value for TOP clause. I
created a huge dynamic SQL statement that I use with sp_executesql.
VERY UGLY!!! Do you have any alternative suggestion?
Thanks
Cassiano|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

> Hello Hugo,
> Thank you for the suggestion of creating an index with the src, type,
> and dt. That reduced the execution time from 30 sec to less than a
> second. My last problem was to parameterize the value for TOP clause. I
> created a huge dynamic SQL statement that I use with sp_executesql.
> VERY UGLY!!! Do you have any alternative suggestion?
> Thanks
> Cassiano
Issue a SET ROWCOUNT in the stored procedure:
CREATE PROCEDURE foo (@.nRows int, @.bar varchar(30))
AS
SET ROWCOUNT @.nRows
SELECT * FROM sysobjects where name <> @.bar
SET ROWCOUNT 0
GO
SQL Server specific, but it works, and there's no dynamic SQL or even
recompilation.|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for the suggestion of creating an index with the src, type,
>and dt. That reduced the execution time from 30 sec to less than a
>second. My last problem was to parameterize the value for TOP clause. I
>created a huge dynamic SQL statement that I use with sp_executesql.
>VERY UGLY!!! Do you have any alternative suggestion?
>Thanks
>Cassiano
Hi Cassiano,
The suggestion made by Ross (SET ROWCOUNT) is fine if you wwant to limit
the total number of rows returned by the query. But I seem to recall
that your problem was more complex than that.
Going back in the thread, I see this query I posted a few days ago - is
this the one you are using, and where you want to replace TOP 20 with a
variable number?
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
The easiest answer is to wait a few w

s. TOP @.variable will be
suppported in SQL Server 2000, which will hit the streets in the w

of
November 7th.
Or use the following (which is ANSI standard to boot):
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND (SELECT COUNT(*)
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
AND c.dt <= a.dt) <= 20
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you very much for your reply. This whole discussion has been very
enlightening.
I compared the performance of two variations of the solution:
#1. The last solution that you suggested.
#2. Based on your original solution (the one with hardcoded TOP
clause). The only modification that I made was to create the query
string dynamically to simmulate the effect of a variable TOP clause.
The table has the following indices (in addition to a PK index on a
MsgId column that I did not include in the posting).
CREATE INDEX IX_MessageTbl1
ON MessageTbl (src, type, dt DESC)
and
CREATE INDEX IX_MessageTbl2
ON MessageTbl (dt DESC)
I noticed that #1 was 6 times slower than #2. Although #2 is faster, I
do not like it, because I create the query dynamically. Do you know if
there is any index that I can create or any hint that I can use to
speed-up the query #1.
Thank you
- CDsql

Tuesday, March 27, 2012

Help with CASE in Stored Procedure

hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@.Search_Arg) and the search by(@.Search_by) Either by Login name or by firstname. Under my CASE statement, When the @.Search_by is equal to Username i want to select the user with User_login equals to @.Search_Arg and when the @.Search_by is equal to Firstname it will select all user with User_FirstName equals to @.Search_Arg...Any help please..Thanks in advance!

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN

CASE @.Search_By
WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @.Search_Arg
WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @.Search_Arg
END
END

Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
Incorrect syntax near the keyword 'END'.what you have there is a dynamic search condition. read this for various techniques:

http://www.sommarskog.se/dyn-search.html

in particular, this might suit you:

select col1, col2, col3 from RCPS_UserAccount
where
(User_login = @.user_login or @.user_login is null) and
(User_FirstName = @.user_firstname or @.user_firstname is null)|||Case can return only one value at a time
use of 'Select *' is not allowed in case statement. What you can do is :

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN
SELECT * FROM RCPS_UserAccount
WHERE
CASE
When @.Search_By = 'Username' then
User_login
When @.Search_By = 'Firstname' then
User_FirstName
End
= @.Search_Arg
END
END

...but I think way suggested by jezemine is better than this one.sql

Help with CASE and LIKE

Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@.SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

ERROR:

Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.

STORED PROCEDURE:

USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@.SeacrhArg varchar(20),
@.SearchBy varchar(20),
@.TransType varchar(20),
@.FromDate datetime,
@.Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo

FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID

WHERE
CASE
WHEN @.SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @.SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @.SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @.SeacrhArg AND

Transac.ItemTrans_DateCreated BETWEEN @.FromDate AND dateadd(day,1,@.Todate) AND
(
(@.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@.TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END

CALL STORED PROCEDURE

USE [RuslinCellPawnShoppeDB]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[sp_Transaction_Search]
@.SeacrhArg = '%man%',
@.SearchBy = 'LastName',
@.TransType = 'Pawned',
@.FromDate = N'9/01/2007 12:00:00 AM',
@.Todate = N'9/6/2007 12:00:00 AM'

SELECT 'Return Value' = @.return_value

GOI suspect Transac.ItemTrans_Status is a varchar and that is where the problem is|||WHERE CASE
WHEN @.SearchBy = 'FirstName' AND Customer.Customer_FirstName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Item ID' THEN CONVERT(VARCHAR, Transac.Item_ID) LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Ticket No' THEN CONVERT(VARCHAR, Transac.Item_TransTicketNo) LIKE @.SeacrhArg THEN 1
ELSE 0
END = 1
AND Transac.ItemTrans_DateCreated >= @.FromDate
AND Transac.ItemTrans_DateCreated < DATEADD(DAY, 1, @.Todate)
AND 1 = CASE
WHEN @.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1 THEN 1
WHEN @.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2 THEN 1
WHEN @.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3 THEN 1
WHEN @.TransType = 'Sold' AND Transac.ItemTrans_Status = 5 THEN 1
ELSE 0
END|||Thanks a loT Peso..But I preferred to use BETWEEN..AND.. for date unless you have there agood reason to use the <>= sign..But anyway thanks alot!|||if you use BETWEEN, you inadvertently get too may records.

BETWEEN '20070101' AND '20071231' will NOT get the records dated "20071231 10:14:23"

BETWEEN '20070101' AND '20080101' will get the records dated "20071231 10:14:23" but ALSO the records dated "20080101 00:00:00".

>= '20070101' <'20080101' will get the records dated "20071231 10:14:23" but NOT the records dated "20080101 00:00:00".

Monday, March 26, 2012

Help with AND and OR query

I am having trouble with the below query. This is attached to a SQLDataAdapter which in turn is connected to a grid view.

@.pram5 is a dropdownlist
all other perameters such as @.nw in the big OR statement are check boxes.

My tables look similar to this:

Company TblComodity TblRegion
---- ----- -----
PK CompanyID PK CommodityID PK RegionID
CompanyName FK CompanyID FK CompanyID
CommodityName North
South, East, etc

What I would am trying to do is have a user slect a commodity which is a distinct value from the comodity table. Then select by tick boxes locations, then in the grid view companies with possible locations and commoditys appear (matching record for commodity name and True values for any one particular location). My problem is even when I select a commodity and leave all tick boxes blank (false) the records still display for the selected commodity- like its only filltering on commodity name. Can anyone help ? I can provide more info if needed

Another little example of the above in case you dont understand.

Say in the dopdown list you choose "Buildings" and out of all the check box values you only choose Scotland, and North the record should still be returned if North is False and Scotland is True.

Here is my query:

SELECT TblCompany.CompanyID, TblCompany.CompanyName, TblRegion.NorthWest, TblRegion.NorthEast, TblRegion.SouthEast, TblRegion.SouthWest,
TblRegion.Scotland, TblRegion.Wales, TblRegion.Midlands, TblRegion.UKNational, TblRegion.EuropOotherThanUK, TblComodity.ComName
FROM TblCompany INNER JOIN
TblRegion ON TblCompany.CompanyID = TblRegion.CompanyID INNER JOIN
TblComodity ON TblCompany.CompanyID = TblComodity.CompanyID AND TblComodity.ComName = @.pram5
WHERE (TblRegion.NorthWest = @.nw) OR
(TblRegion.NorthEast = @.NE) OR
(TblRegion.SouthEast = @.se) OR
(TblRegion.SouthWest = @.sw) OR
(TblRegion.Scotland = @.scot) OR
(TblRegion.Wales = @.wal) OR
(TblRegion.Midlands = @.mid) OR
(TblRegion.EuropOotherThanUK = @.EU) AND (TblRegion.UKNational = @.UKN)

INNER JOIN evaluates if only the condition on both the tables is matched. In your case, you need to use OUTER JOIN (LEFT OR RIGHT) so that the records from left/right table are fetched even if the condition in right/left table fails. In your query change the INNER JOIN to LEFT OUTER JOIN

Thanks

|||

Thank you, I am new to this and use the query designer as I am learning. What you have said has taught me somthing new, We it be possible to modify my query to include your suggestion of the Left Outer Join?

Many thanks,

Adam.

|||

I re-read your question and looks like you have nothing to do with the OUTER JOINS. Try this

SELECT TblCompany.CompanyName, TblComodity.CommodityName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE TblComodity.CommodityName = @.pram5 AND TblRegion.NorthWest = @.nw

In the above query i used only one region. If you want to add more regions use AND

Thanks

|||

Hi,

Thats almost what I want but I need OR's for example if you have a Commodity Name called buildins and you select TRUE values for North East and South and the particular commodity only has a TRUE value for South I still would like the record returned. It would work similar to say a holiday web site where you would choose a country and say select multiple regions and all records for regions would be returned. Do you understand?

|||

Did you try using the query i provided with OR with which you can get the desired results.

Thanks

|||

Your code gives me all results for some strange reason if I make pram5 = 'buildings' and @.nw = 'true'

returns

Company Commodity Northwest

sampleCleaningTruePLCBuildingsFalseVictoriaBuildingsTrueVictoriaCateringTrueVictoriaComputingTrueVictoriaBuildingsTrueyiyuBuildingsFalse

SELECT TblCompany.CompanyName, TblComodity.ComName, TblRegion.NorthWest
FROM TblRegion INNER JOIN
TblComodity ON TblRegion.CompanyID = TblComodity.CompanyID INNER JOIN
TblCompany ON TblComodity.CompanyID = TblCompany.CompanyID
WHERE (TblComodity.ComName = @.pram5) OR
(TblRegion.NorthWest = @.nw)

sql

Friday, March 23, 2012

Help with a substring query

I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.

Things you will need to keep in mind are;

The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.

Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Regards,
Ciarnchudson007@.hotmail.com wrote:
> I need help capturing information from a free text field.
> The 10 examples below contain examples of the information I am trying
> to capture.
> In each cell I am trying to capture the number between 'TranID=' and
> the next '&'.
> So in the fisrt cell I would like to capture 14078800.
> Things you will need to keep in mind are;
> The number is not of fixed length.
> 'TranID' will always precede the number
> The number will always be followed by an '&'
> The '&' sign can occur multiple times in the text.
>
> Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Hint: use LIKE.

Kind regards

robert|||--something like this:
declare @.tranid_position int, @.amp_position int,@.string varchar(8000),
@.rest_of_string varchar(8000), @.Result_string varchar(8000)
set
@.string='Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.P*ostCSDelivery&OtherFlag0par=deliv
'
set @.tranid_position=charindex('&TranID=',@.string)
set @.rest_of_string= substring(@.string,@.tranid_position+8,8000)
set @.amp_position=charindex('&',@.rest_of_string)
set @.Result_string=left(@.rest_of_string,@.amp_position-1)
select @.Result_string|||Here you go...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'

SELECT
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest

You could also look into regular expressions.|||That worked perfectly.
Much appreciated.|||I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example

OtherID=1638256785230&TranID=12345

How do I edit the script to capture these records?

Regards,
Ciarn|||There may be a better way but this should work...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

SELECT
CASE WHEN
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
) = 0
THEN
RIGHT(QueryString,
LEN(QueryString)-
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-6
)
ELSE
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
END AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest|||(chudson007@.hotmail.com) writes:
> I've just encountered a little problam.
> Contrary to the criteria I provided earlier there are cells which end
> with the TranID, like for example
> OtherID=1638256785230&TranID=12345
> How do I edit the script to capture these records?

Here is a query, a little different from figitals:

SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('TranID=', str) + len('TranId='),
len(str))
FROM QueryStringTest) AS a) AS b

By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perfect!
Thanks

Wednesday, March 21, 2012

help with a simple query

hi all, i was wondering if there is an alternate way of creating a simple
query that'' get the results below, without using temp tables or declaring
any cursors.
Table:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi
A Bob 2/2/2007 notes coke
A Bob 2/2/2007 notes sarsi
Result:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi,coke,sarsi
any suggestions will be appreciated
Thanks a million!No. Use a FAST_FORWARD cursor and table variable/temp table, process
the data on the client side of your code, or use a reporting product
such as Reporting Services or (ack) Crystal to do it.
-Dave
ChiWhiteSox wrote:
> hi all, i was wondering if there is an alternate way of creating a simple
> query that'' get the results below, without using temp tables or declaring
> any cursors.
> Table:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi
> A Bob 2/2/2007 notes coke
> A Bob 2/2/2007 notes sarsi
> Result:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi,coke,sarsi
> any suggestions will be appreciated
> Thanks a million!|||See: http://www.aspfaq.com/show.asp?id=2529
Razvansql

Help with a query

Hello all-

I am stuck with a query problem. I have two tables whose structure is described below.

If the attributename in @.fulltable matches the attributename in @.subtable then compare the correponding value columns and return the rows whose values match. If the attributename does not match, return the row anyway.

set nocount on

declare @.fulltable table
(itemid int,
attributename varchar(100),
value int)

declare @.subtable table
(attributename varchar(100),
value int)

insert into @.fulltable values (1, 'a', 100)
insert into @.fulltable values (1, 'b', 200)
insert into @.fulltable values (1, 'c', 300)
insert into @.fulltable values (2, 'a', 400)
insert into @.fulltable values (2, 'b', 500)

insert into @.subtable values ('a', 100)
insert into @.subtable values ('b', 500)

In the SQL above, my result should return

1 'a' 100 -- Because the value matches
1 'c' 300 -- Because the attributename is not present in @.subtable
2 'b' 500 -- Because the value matches

-- 1 'b' 200 and 2, 'a', 400 should be eliminated as the attributenames in @.fulltable match the attributenames in @.subtable, but the values dont.

I have tried something similar to:

SELECT ft.*
FROM @.fulltable ft
INNER JOIN @.subtable sub
ON ft.value = CASE WHEN ft.attributename = sub.attributename
THEN sub.value
ELSE ft.value
END

As you can see, it does not return the desired result. Please let me know if you need any further explanation of what I am trying to achieve. Any help is greatly appreciated.May be this can be the solution;

Try to write two different queries after this use them together ..

Query q1

SELECT fulltable.attributename, fulltable.value
FROM fulltable INNER JOIN
subtable ON fulltable.attributename = subtable.attributename AND fulltable.value = subtable.value

This will give
a 100
b 500

Query q2

SELECT fulltable.attributename, fulltable.value
FROM fulltable LEFT OUTER JOIN
subtable ON fulltable.attributename = subtable.attributename
WHERE (subtable.attributename IS NULL)

This will result
c 300

Query q3

SELECT * FROM q1 UNION SELECT * FROM q2

This will result
a 100
b 500
c 300

Hope this will solve your problem ..|||Sishe-

Your solution works great. I greatly appreciate your time. There is a little bit more to the problem. Let me explain.

ValueColumnName in the @.subtable gives the name of the column in the @.fulltable with which the value in the @.subtable should be compared. I have tried many different ways but still cannot get the solution to this problem. I greatly appreciate if you could help me with this query.

set nocount on

declare @.fulltable table
(itemid int,
attributename varchar(100),
valueInt int,
valueString varchar(256),
valueFloat float)

declare @.subtable table
(attributename varchar(100),
value varchar(256),
ValueColumnName varchar(100))

insert into @.fulltable values (1, 'a', NULL, 'String1', NULL)
insert into @.fulltable values (1, 'b', 200, NULL, NULL)
insert into @.fulltable values (1, 'c', NULL, NULL, 1.31)
insert into @.fulltable values (2, 'a', NULL, 'String2', NULL)
insert into @.fulltable values (2, 'b', 500, NULL, NULL)

insert into @.subtable values ('a', 'String1', 'ValueString')
insert into @.subtable values ('b', 500, 'ValueInt')

The ouput I am expecting is still similar as before.

1 'a' NULL 'String1' NULL -- Because the value matches
1 'c' NULL NULL 1.31 -- Because the attributename is not present in @.subtable
2 'b' 500 NULL NULL -- Because the value matches|||This should help:

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
s.value = case
when s.ValueColumnName = 'ValueInt' then cast(f.valueInt as varchar(100))
when s.ValueColumnName = 'ValueString' then f.valueString
when s.ValueColumnName = 'ValueFloat' then cast(f.valueFloat as varchar(100))
end
where
s.value is not null

union all

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
f.attributename = s.attributename
where
s.value is null

order by
f.itemid, f.attributename|||manowar did give the solution.. One thing I didn't see in first query is please use "UNION ALL" not "UNION" in first query. Because UNION works like DISTINCT keyword so it may cause wrong result sets.

Monday, March 19, 2012

Help with a Query

Am I able to do something like this? The syntax below is obviously
wrong, I'm looking to see if there is a way to accomplish this.
DECLARE @.DBName varchar(5)
SET @.DBName = 'pubs'
SELECT * FROM @.DBName.dbo.authors
I have a master application and database containing data for several
companies. Each company uses a certain web-based sales software. I have
a local copy of those databases. Each of those databases is identical
in structure, they just contain each company's data.
I want to use stored procedures in my main database to run reports on
the data in each of the databases. I don't want to copy my stored
procedures into each database. Instead I want to call them from the
main database, and within the stored procedure, decide on which
database to run the t-sql.
Thanks!"George" <george.durzi@.gmail.com> wrote in message
news:1134592425.040038.242050@.z14g2000cwz.googlegroups.com...
> Am I able to do something like this? The syntax below is obviously
> wrong, I'm looking to see if there is a way to accomplish this.
> DECLARE @.DBName varchar(5)
> SET @.DBName = 'pubs'
> SELECT * FROM @.DBName.dbo.authors
> I have a master application and database containing data for several
> companies. Each company uses a certain web-based sales software. I have
> a local copy of those databases. Each of those databases is identical
> in structure, they just contain each company's data.
> I want to use stored procedures in my main database to run reports on
> the data in each of the databases. I don't want to copy my stored
> procedures into each database. Instead I want to call them from the
> main database, and within the stored procedure, decide on which
> database to run the t-sql.
> Thanks!
>
You will need to use Dynamic SQL.
Try the following:
EXECUTE ('SELECT * FROM ' + @.DBName + '.dbo.authors')
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, the only issue with that is I actually am gonna be writing a
complex stored procedure.
It looks like dynamic sql is going to have to be the way to go though.|||Before diving into the dynamicity pool and drowning, take look up
"distributed partitioned views" in Books Online.
I'd create an extra database and use it as a central reporting data source.
ML
http://milambda.blogspot.com/

help with a group by query

Hi Could somebody please help

I have the below query:
select frf.EDGE_RECURRENCE_KEY, min(td.sql_date)
from future_revenue_fact frf, EMBEDDED_EDGE_REV_ITEMS eeri, attribution_dimension ad, attribution_units_fact au, time_dimension td
where frf.ATTRIBUTION_TRANSACTION_KEY = ad.ATTRIBUTION_TRANSACTION_KEY
and ad.ATTRIBUTION_ROLE = 'Salesperson'
and ad.ATTR_UNIT_TRANSACTION_KEY = au.ATTR_UNIT_TRANSACTION_KEY
and frf.EDGE_RECURRENCE_KEY = eeri.EMBEDDED_EDGE_ID
and eeri.EMBEDDED_EDGE_VERSION_NO = 0
and frf.REVENUE_RECORD_TIME_KEY = td.TIME_KEY
and frf.REVENUE_TYPE = 'Embedded Edge'
and au.ATTRIBUTION_UNIT_NAME = 'Darren Starr'
group by frf.EDGE_RECURRENCE_KEY

This query works fine, however I need to somehow just return min(td.sql_date) in the select statement and not frf.EDGE_RECURRENCE_KEY as the min(td.sql_date) needs to feed as into another query eg:
select *
from table x
where sql_date in --> here i need to return the min(sql_date) using the first query.

Is there anyway around this, besides using a stored proc??yes, there is any easy way: remove frf.EDGE_RECURRENCE_KEY from both the SELECT and the GROUP BY (i.e. remove the GROUP BY completely)

which table is table x? are there any other tables besides table x in the outer query?|||Or, if you still want the minimum (now in the subquery) to refer to only the rows with an identical frf.EDGE_RECURRENCE_KEY, remove the GROUP BY, but add a correlated WHERE condition:
... AND frf.EDGE_RECURRENCE_KEY = corr.EDGE_RECURRENCE_KEY
where "corr" would be the table alias name for future_revenue_fact in the outer query.

Monday, March 12, 2012

help with @@IDENTITY

hello,

I've got a problem reading the @.@.identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)

comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @.@.IDENTITY AS Ident;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
 rsLastIdent = comm.ExecuteNonQuery()
 Catch ex As Exception
 Response.Write("Exception:")
 Response.Write(ex.ToString)
 End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if

The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...

Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @.@.identity?

Thanks a lot!

timWhat does ExecuteNonQuery return? It is not what you are trying to get at.

Try .ExecuteScalar() and cast the returned value to an int.

Dim rsLastIdent as integer
rsLastIdent=Integer.Parse(comm.executeScalar().ToString())

There are more elegant ways to do this, I expect.|||Douglas,

Thanks for your help!

executeScalar() was the function I needed! It works perfect now!

Regards,
Tim

Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "Your search returned " & intRecordCount & " result(s)
for: "
Response.Write "<blockquote>"
Response.Write "Make"
Response.Write "</blockquote>"
Response.Write ""
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write ""
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
Message posted via http://www.droptable.com
http://support.microsoft.com/kb/174225/en-us
http://groups.google.de/groups?hl=de...07%26rnum%3D11
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.droptable.co m...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "Your search returned " & intRecordCount & "
> result(s)
> for: "
> Response.Write "<blockquote>"
> Response.Write "Make"
> Response.Write "</blockquote>"
> Response.Write ""
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write ""
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.droptable.com

Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "Your search returned " & intRecordCount & " result(s)
for: "
Response.Write "<blockquote>"
Response.Write "Make"
Response.Write "</blockquote>"
Response.Write ""
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write ""
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
Message posted via http://www.droptable.comhttp://support.microsoft.com/kb/174225/en-us
rnum=11&prev=/ groups%3Fq%3DRowset%2Bcannot%2Bbe%2Brest
arted%2Bmovefirst%26start%3D
10%26hl%3Dde%26lr%3D%26selm%3D%2523NwSu4
7ECHA.1732%2540tkmsftngp07%26rnum%3D11[/ur
l]
HTH, Jens Suessmeyer.
[url]http://www.sqlserver2005.de" target="_blank">http://groups.google.de/groups?hl=d...qlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.SQ
droptable.com...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "Your search returned " & intRecordCount & "
> result(s)
> for: "
> Response.Write "<blockquote>"
> Response.Write "Make"
> Response.Write "</blockquote>"
> Response.Write ""
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write ""
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.droptable.com

Help with "Error 80040e18: Rowset cannot be restarted."

Hi everyone,
The code below runs fine in Access, but in migrating to SQL Server I've run
into this error:
"Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset position
cannot be restarted."
The code (ASP) is:
#######
If Request.Form("Make") <> "" then
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_ANALYZE_MAKE"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Request.Form("Make")
set objRS = objCommand.Execute
set objCommand = Nothing
If Not objRS.EOF then
r = objRS.GetRows
End if
If IsArray(r) Then
intRecordCount = UBound(r, 2) + 1
objRS.MoveFirst
End If
Response.Write "Your search returned " & intRecordCount & " result(s)
for: "
Response.Write "<blockquote>"
Response.Write "Make"
Response.Write "</blockquote>"
Response.Write ""
Response.Write "<blockquote>"
Do until objRS.EOF
Response.Write Code Here...
objRS.MoveNext
Loop
Response.Write "</blockquote>"
Response.Write ""
objRS.Close
set objRS = Nothing
End If
#######
Is this a case of needing to utilize (based on the above code) a different
cursor, or...? There sure are a lot of differences in how Access and SQL
Server allow you to do things (beyond 'basic' DB structure), or perhaps how
'forgiving' each of them are.
Anyone with a suggestion or workaround? Thanks.
--
Message posted via http://www.sqlmonster.comhttp://support.microsoft.com/kb/174225/en-us
http://groups.google.de/groups?hl=de&lr=&threadm=%23NwSu47ECHA.1732%40tkmsftngp07&rnum=11&prev=/groups%3Fq%3DRowset%2Bcannot%2Bbe%2Brestarted%2Bmovefirst%26start%3D10%26hl%3Dde%26lr%3D%26selm%3D%2523NwSu47ECHA.1732%2540tkmsftngp07%26rnum%3D11
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2723f97d6dc4470492871a765551178f@.SQLMonster.com...
> Hi everyone,
> The code below runs fine in Access, but in migrating to SQL Server I've
> run
> into this error:
> "Microsoft OLE DB Provider for SQL Server error '80040e18'. Rowset
> position
> cannot be restarted."
> The code (ASP) is:
> #######
> If Request.Form("Make") <> "" then
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_ANALYZE_MAKE"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Request.Form("Make")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> If Not objRS.EOF then
> r = objRS.GetRows
> End if
> If IsArray(r) Then
> intRecordCount = UBound(r, 2) + 1
> objRS.MoveFirst
> End If
> Response.Write "Your search returned " & intRecordCount & "
> result(s)
> for: "
> Response.Write "<blockquote>"
> Response.Write "Make"
> Response.Write "</blockquote>"
> Response.Write ""
> Response.Write "<blockquote>"
> Do until objRS.EOF
> Response.Write Code Here...
> objRS.MoveNext
> Loop
> Response.Write "</blockquote>"
> Response.Write ""
> objRS.Close
> set objRS = Nothing
> End If
> #######
> Is this a case of needing to utilize (based on the above code) a different
> cursor, or...? There sure are a lot of differences in how Access and SQL
> Server allow you to do things (beyond 'basic' DB structure), or perhaps
> how
> 'forgiving' each of them are.
> Anyone with a suggestion or workaround? Thanks.
> --
> Message posted via http://www.sqlmonster.com

Help wit Query to list duplicates

I am trying to create a list of those cases where a specific field value was
duplicate with a set of rows. The query I am trying is shown below (it is
listing all rows instead of just those where there is a duplicate value in
the field Eqp1Voc.
ID is an unique key for each row.
What did I overlook?
Wayne
========================================
=
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores A JOIN DCScores B
ON A.ID <> B.ID
AND A.Eqp1Voc = B.Eqp1Voc)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
========================================
=> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
(SELECT B.ID
FROM DCScores
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Thanks for the quick response but that doesn't return any rows (and I know
there are duplicates in that field!) With your suggestion, the query now
looks like this:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores B
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238nw0CEQFHA.688@.TK2MSFTNGP14.phx.gbl...
> (SELECT B.ID
> FROM DCScores
> Group by B.ID
> HAVING COUNT(Eqp1Voc) >1)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Sorry for that missed up something...
Here it goes:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE Eqp1Voc IN
(SELECT Eqp1Voc
FROM DCScores
Group by Eqp1Voc
HAVING COUNT(Eqp1Voc) > 1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Jens.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Jens;
Thanks again but with that change, it returns all rows, not just those with
a duplicate value?
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23FGm0YEQFHA.204@.TK2MSFTNGP15.phx.gbl...
> Sorry for that missed up something...
> Here it goes:
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE Eqp1Voc IN
> (SELECT Eqp1Voc
> FROM DCScores
> Group by Eqp1Voc
> HAVING COUNT(Eqp1Voc) > 1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> Jens.
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Try this:
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
Group By Eqp1Voc
Having Count(*) > 1)
WHERE CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
"Wayne Wengert" wrote:

> I am trying to create a list of those cases where a specific field value w
as
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>
>|||Thanks for the suggestion but that yields an error: "Incorrect syntax near
the keyword 'WHERE'."
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> Try this:
> SELECT UnitName, Eqp1Judge,
> Eqp1Voc
> FROM DCScores S
> Where Exists
> (Select * From DCScores
> Where Eqp1Voc = S.Eqp1Voc
> Group By Eqp1Voc
> Having Count(*) > 1)
> WHERE CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> "Wayne Wengert" wrote:
>
was
is
in|||Replace this "WHERE CircuitID" WITH "AND CircuitID"
Jens Smeyer.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Thanks for the suggestion but that yields an error: "Incorrect syntax near
> the keyword 'WHERE'."
> Wayne
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> was
> is
> in
>|||Jens;
I took that advice and also realized that the Where clause needs to be
applied to that inner select. I changed it to the following and now it
works.
I appreciate all the help.
==================================
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Group By Eqp1Voc
Having Count(*) > 1)
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
==================================
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KCoZCFQFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Replace this "WHERE CircuitID" WITH "AND CircuitID"
> Jens Smeyer.
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
near
(it
value
>

Help w/sum pls

Hi,
I'd like to add the results of T1 and T2 in the query below but I just
cannot see to get it rt.
I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
What's the trick?
TIA
Mike
DROP TABLE #testtb
CREATE TABLE #testtb (day_in datetime, day_out datetime)
INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
SELECT day_in, day_out,
CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 END
AS T1,
CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 END
AS T2
FROM #testtb
Results should be:
2 periods meet the condition in T1
1 period meet the conditions in T2
Therefore:
Sum of T1 = 2
Sum of T2 = 1Mike
I'm

. I got the output four rows as
t1 t2
1 1
1 0
0 1
0 0
What is supposed to be? I think for the t2 should be 2 periods as well ,
isn't?
"Mike_B" <nospam@.yahoo.com> wrote in message
news:eL%23JBQUWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'd like to add the results of T1 and T2 in the query below but I just
> cannot see to get it rt.
> I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
> What's the trick?
> TIA
> Mike
> DROP TABLE #testtb
> CREATE TABLE #testtb (day_in datetime, day_out datetime)
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
>
> SELECT day_in, day_out,
> CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 END
> AS T1,
> CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 END
> AS T2
> FROM #testtb
> --
> Results should be:
> 2 periods meet the condition in T1
> 1 period meet the conditions in T2
> Therefore:
> Sum of T1 = 2
> Sum of T2 = 1
>|||Is this what you want?
Note that BETWEEN is inclusive so the second between should
be from 4 to 7.
SELECT
SUM(CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1
ELSE 0 END)
AS T1,
SUM(CASE WHEN datediff(day, day_in, day_out) between 4 and 7 THEN 1
ELSE 0 END)
AS T2
FROM #testtb|||Hi Mike,
First I think you have a logical bug. The second line you insert to the
table, has a difference of 3 days, which means it is evaluated both by T1 an
d
T2 thouse cousing the sum results of them to be T1=2 and T2=2.
Second:
you can get the sum only if you do not return the dates (otherwise you will
get a sum only for dates that are alike both for the in_day and the out_day.
the query should be like this:
SELECT
SUM(CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0
END)
AS T1,
SUM(CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0
END)
AS T2
FROM #testtb
GuyBar
"Mike_B" wrote:

> Hi,
> I'd like to add the results of T1 and T2 in the query below but I just
> cannot see to get it rt.
> I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
> What's the trick?
> TIA
> Mike
> DROP TABLE #testtb
> CREATE TABLE #testtb (day_in datetime, day_out datetime)
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
>
> SELECT day_in, day_out,
> CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 EN
D
> AS T1,
> CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 EN
D
> AS T2
> FROM #testtb
> --
> Results should be:
> 2 periods meet the condition in T1
> 1 period meet the conditions in T2
> Therefore:
> Sum of T1 = 2
> Sum of T2 = 1
>
>|||Between includes both start and end limit.
so fot t2 it should be 4 and 7.
Regards
Amish Shah

Friday, March 9, 2012

Help using custom VB6 Function in Crystal

I have a function for a date format in VB6 that converts to a Long Integer. I then use the opposing function below to convert to a readable date. Can anyone help me implement the function in Crystal 10.

Public Function fDateLong(plngDate As Long) ' Gets Date from DB format YYYYMMDD (DB date is Long) ,
Dim sDate, sYYYY, sMM, sDD As String
sDate = Trim(CStr(plngDate)) ' trim a Converted Long Date
sYYYY = Left(sDate, 4) ' get YYYY from left
sMM = Mid(sDate, 5, 2) 'get MM from middle
sDD = Right(sDate, 2) ' get DD from right
sDate = sMM & "/" & sDD & "/" & sYYYY ' reassemble
fDateLong = CDate(sDate) ' Convert to Date

End Function

Right now my date is reporting as " 20060612". Any help in implementing this function in Crystal would be appreciated.What is your expected output?|||I would like to take it from the DB format (long) 20060615 to a date format like either June 15 2006 or even 6-15-2006 (or 15-06-2006).

Thanks for you response.|||Create a formula having this code and drag that in the report

Numbervar y:=0;
Numbervar m:=0;
Numbervar da:=0;

y:=Tonumber(left(replace(totext(20060615 ),",",""),4));
m:=Tonumber(mid(replace(totext(20060615 ),",",""),5,2));
da:=Tonumber(mid(replace(totext(20060615 ),",",""),7,2));

monthname(m)+" "+totext(da,0)+ " "+replace(totext(y,0),",","")|||Madhi,
Appreciate the help but I still have no bloody where to put this?
Can you advise?|||As I told you create new formula. Put that code. Save it. Drag it to the details section|||Madhi,

Great stuff; problem solved.

Thanks very much

Friday, February 24, 2012

Help sending email

My code below works fine when run from my pc (changed all the values for
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.

I have SMTP running, but there is no outlook installed.

Can someone please advise what I am missing.
Thanks
Bob

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "send@.test.com"
objEmail.To = "receive@.test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

set objEmail = nothingHi B

One thing you might try is change

> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
to
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = "2"

quotes around the 2.

I spent several hours a week ago trying the very same thing and that made
all the difference.

Also is \\server\test.csv accessible from the server you are running this
on?

Here is the full text of the DTS Package I wrote. Note that I don't think
all the fields you included are necessary.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
email_alert "dchristo@.yahoo.com", "George_Bush@.whitehouse.gov","Test
Subject", "Test Body"
Main = DTSTaskExecResult_Success
End Function

Sub email_alert(strTo, strFrom, strSubject, strBody)
Dim iConf 'As CDO.Configuration
Dim imsg 'As CDO.Message
Dim flds

Set imsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set flds = iConf.Fields

'The http://schemas.microsoft.com/cdo/configuration/ namespace defines
the majority of fields used to set configurations for various CDO objects.
We set and update the following three fields (SendUsing, SMTP_SERVER, and
TimeOut) of the Configuration object:

With flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
"2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp-server.mn.rr.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With

Set imsg.Configuration = iConf
imsg.To = strTo
imsg.From = strFrom
imsg.Subject = strSubject
imsg.TextBody = strBody
imsg.AddAttachment "c:\log\myfile.txt"
imsg.Send
End Sub
--
-Dick Christoph

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
> installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Follow-up to my original post below.

Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?

At the moment, I type the email address separated by a semi-colon.

TIA~

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Hi B,

Well not directly but you could create an ADODB Command, Connection and
Recordset and use the command to return you a recordset from the Database
that would have 1 or many email addresses that you could concatenate
together and stick in the objEmail.To field.

--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:c4mdnfssT45ImT7ZnZ2dnUVZ_r-dnZ2d@.rcn.net...
> Follow-up to my original post below.
> Is it possible for the "objEmail.To" to lookup the values from a sqlserver
> table?
> At the moment, I type the email address separated by a semi-colon.
> TIA~
>
> "B" <no_spam@.no_spam.com> wrote in message
> news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
>> My code below works fine when run from my pc (changed all the values for
>> obvious reasons). The code is placed inside a DTS task via VBS
>> scripting.
>> But when I try to run directly from the server where sqlserver is
> installed,
>> the script fails.
>>
>> I have SMTP running, but there is no outlook installed.
>>
>> Can someone please advise what I am missing.
>> Thanks
>> Bob
>>
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "send@.test.com"
>> objEmail.To = "receive@.test.com"
>> objEmail.Subject = "TEST SUBJECT"
>> objEmail.AddAttachment "\\server\test.csv"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusing") = 2
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserver") = "SERVER_NAME"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpauthenticate") = 1
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusername") = "username"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendpassword") = "userpwd"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>> set objEmail = nothing
>>
>>

Sunday, February 19, 2012

Help requested : Finding the 6 closest points

Hi all,

I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.

I have two tables (points and station), presented in form of SQL codes below. I’d like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 – D6) is the distance of panels P1 – P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula:

Distance = ( (X₁ – X₂)² + (Y₁ - Y₂)²)^0.5. As the sample, distance between station ‘A’ and panel ‘P19-04W’ is = ((737606.383 - 737599.964)² + (9548850.844 - 9548856.856)²)^0.5 = 8.79.

The expected result of the work is presented in the table below:

Panel distance Station P1 P2 P3 P4 P5 P6 D1 D2 D3 D4 D5 D6 A P19-04W P19-06E P19-05E P19-05W P19-03W P19-07E 8.79 12.00 13.54 19.02 21.00 27.43 B P19-07W P19-09E P19-08E P19-06W P19-08W P19-07E 9.50 11.58 12.92 21.15 24.85 29.11 C P19-11E P19-10W P19-12E P19-09W P19-10E P19-11W 8.45 11.42 16.06 16.38 23.15 25.30

Table 1:

create table 1 (

Panels varchar(20),

X_Coord float,

Y_Coord float

)

set nocount on

insert into 1 values('P19-03E','737640.722','9548882.875')

insert into 1 values('P19-04E','737630.166','9548868.3')

insert into 1 values('P19-05E','737619.611','9548853.726')

insert into 1 values('P19-06E','737609.054','9548839.15')

insert into 1 values('P19-07E','737598.495','9548824.571')

insert into 1 values('P19-08E','737587.941','9548809.998')

insert into 1 values('P19-09E','737577.386','9548795.425')

insert into 1 values('P19-10E','737563.359','9548776.163')

insert into 1 values('P19-11E','737552.795','9548761.578')

insert into 1 values('P19-12E','737542.256','9548746.919')

insert into 1 values('P19-13E','737531.701','9548732.345')

insert into 1 values('P19-14E','737521.146','9548717.772')

insert into 1 values('P19-03W','737610.519','9548871.43')

insert into 1 values('P19-04W','737599.964','9548856.856')

insert into 1 values('P19-05W','737589.404','9548842.275')

insert into 1 values('P19-06W','737578.849','9548827.702')

insert into 1 values('P19-07W','737568.294','9548813.128')

insert into 1 values('P19-08W','737554.274','9548793.77')

insert into 1 values('P19-09W','737543.718','9548779.195')

insert into 1 values('P19-10W','737533.157','9548764.614')

insert into 1 values('P19-11W','737522.603','9548750.041')

set nocount off

Table 2:

create table 2 (

Station varchar(20),

X_Coord float,

Y_Coord float

)

set nocount on

insert into 2 values('A','737606.383','9548850.844')

insert into 2 values('B','737575.41','9548806.838')

insert into 2 values('C','737544.437','9548762.832')

set nocount off

Thanks alot in advance!

Thanks for the DDL and sample data. Try:

Code Snippet

use tempdb

create table dbo.t1 (

Panels varchar(20),

X_Coord float,

Y_Coord float

)

set nocount on

insert into dbo.t1 values('P19-03E','737640.722','9548882.875')

insert into dbo.t1 values('P19-04E','737630.166','9548868.3')

insert into dbo.t1 values('P19-05E','737619.611','9548853.726')

insert into dbo.t1 values('P19-06E','737609.054','9548839.15')

insert into dbo.t1 values('P19-07E','737598.495','9548824.571')

insert into dbo.t1 values('P19-08E','737587.941','9548809.998')

insert into dbo.t1 values('P19-09E','737577.386','9548795.425')

insert into dbo.t1 values('P19-10E','737563.359','9548776.163')

insert into dbo.t1 values('P19-11E','737552.795','9548761.578')

insert into dbo.t1 values('P19-12E','737542.256','9548746.919')

insert into dbo.t1 values('P19-13E','737531.701','9548732.345')

insert into dbo.t1 values('P19-14E','737521.146','9548717.772')

insert into dbo.t1 values('P19-03W','737610.519','9548871.43')

insert into dbo.t1 values('P19-04W','737599.964','9548856.856')

insert into dbo.t1 values('P19-05W','737589.404','9548842.275')

insert into dbo.t1 values('P19-06W','737578.849','9548827.702')

insert into dbo.t1 values('P19-07W','737568.294','9548813.128')

insert into dbo.t1 values('P19-08W','737554.274','9548793.77')

insert into dbo.t1 values('P19-09W','737543.718','9548779.195')

insert into dbo.t1 values('P19-10W','737533.157','9548764.614')

insert into dbo.t1 values('P19-11W','737522.603','9548750.041')

set nocount off

create table dbo.t2 (

Station varchar(20),

X_Coord float,

Y_Coord float

)

set nocount on

insert into dbo.t2 values('A','737606.383','9548850.844')

insert into dbo.t2 values('B','737575.41','9548806.838')

insert into dbo.t2 values('C','737544.437','9548762.832')

set nocount off

;with dist

(

select

b.Station,

a.Panels,

cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)) as distance,

row_number() over(partition by b.Station order by cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)), a.Panels) as rn

from

dbo.t1 as a

cross join

dbo.t2 as b

pvt_dist

(

select

Station,

[1] as d1,

[2] as d2,

[3] as d3,

[4] as d4,

[5] as d5,

[6] as d6

from

(select Station, rn, distance from dist where rn < 7) as d

pivot

(

min(distance)

for rn in ([1], [2], [3], [4], [5], [6])

) as pvt

pvt_panels

(

select

Station,

[1] as p1,

[2] as p2,

[3] as p3,

[4] as p4,

[5] as p5,

[6] as p6

from

(select Station, rn, Panels from dist where rn < 7) as d

pivot

(

min(Panels)

for rn in ([1], [2], [3], [4], [5], [6])

) as pvt

)

select

coalesce(d.Station, p.Station) as Station,

p.p1,

p.p2,

p.p3,

p.p4,

p.p5,

p.p6,

d.d1,

d.d2,

d.d3,

d.d4,

d.d5,

d.d6

from

pvt_dist as d

full join

pvt_panels as p

on d.Station = p.Station

order by

coalesce(d.Station, p.Station)

drop table dbo.t1, dbo.t2

AMB|||

Hi Hunchback,

Greatly appreciate your help...!
The query above works perfectly when the creation of both tables is part of the long SQL code. But, if I delete the table creation codes (do not need to create the tables since they are already available in database) and just take the last part of your SQL code in a new query page for running; I got an error. The result is : an error mentioning incorrect syntax near 'pivot'. It also suggests me to set the compatibility level of the database. I'm confused.....

Can you please suggest me the right procedure so that I can take and run only the half part of the SQL Querry (without the creating table’s part)? Table t1 and t2 are already available in database. Thanks alot!

The partial query that I use for running:

;with dist

(

select

b.Station,

a.Panels,

cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)) as distance,

.... and so on...

Others are also welcome to give your thought. Thanks

|||

Can you tell us which version of SQL Server are you using and the compatibility level of your db, please?

AMB

|||

Hi Hunchback,

I am using SQL 2005, but am not really sure about the compatibillity level of my SQL server. How can I checked it?

|||

to check the compatibility level of the database, you can use:

use master

select compatibility_level

from sys.databases

where [name] = 'db_name'

90 - SS 2005

AMB

|||

Hi Hunchback,

Thanks for the suggestion. Here is the database name and compatability level:

DOZ_Convg_Modified, 80

I look forward to having your further assistance. Thanks

|||

You can run this to set your database to sql2k5 compatibility.

Code Snippet

exec sp_dbcmptlevel 'DOZ_Convg_Modified',90

Thursday, March 29, 2012

Help with cursor and decimal values

Help with complicated query...

Tuesday, March 27, 2012

Help with CASE in Stored Procedure

Help with CASE and LIKE

Monday, March 26, 2012

Help with AND and OR query

Friday, March 23, 2012

Help with a substring query

Wednesday, March 21, 2012

help with a simple query

Help with a query

Monday, March 19, 2012

Help with a Query

help with a group by query

Monday, March 12, 2012

help with @@IDENTITY

Help with "Error 80040e18: Rowset cannot be restarted."

Help with "Error 80040e18: Rowset cannot be restarted."

Help with "Error 80040e18: Rowset cannot be restarted."

Help wit Query to list duplicates

Help w/sum pls

Friday, March 9, 2012

Help using custom VB6 Function in Crystal

Friday, February 24, 2012

Help sending email

Sunday, February 19, 2012

Help requested : Finding the 6 closest points

Restore Database

Blog Archive

About Me