Change dynamically(via code) the SqlDataSource for a GridView....

Hi,

say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2...

Does anybody know how can I alter programmatically these two sqldatasources in a gridview?

Thanks!!!

Ok,I resolved it,

I had to delete the "DataSourceID="SqlDataSource1" from the gridview directive and add the following code to a button event:

GridView1.DataSource = SqlDataSource1

GridView1.DataBind()

|||

But now my new question is :

How can I see what is the current/previous DataSource of my GridView Control?

Thanks...

|||

GridView1.DataSource will return the data source (as an object)

You can also useGridView1.DataSourceID to obtain the ID of the datasource as a string

For example:

dim TheCurrentDataSource as object

TheCurrentDataSource =GridView1.DataSource

GridView1.DataSource = The NextDataSource

GridView1.DataBind()

HTH

Change database attributes with SQL?

Is there a way to change the password or encryption settings with SQL or do I need to use Compact from code to do so?

I'm trying to work around the issue that USE does not accept a password.

if you have an existing SQL CE 2 or SQL Mobile database and want to change the password or encryption settings, the only option is to run the Compact method on the SqlCeEngine object. If you have forgotten the old password, you are out of luck - there is no way to reset it.

Darren

|||I was afraid of that. Again, we need a bit of help in the SQL Ev SQL. The USE statement needs to accept a Password argument for this engine.sql

Change connection with T-SQL?

Hi,

I know I can right-click in query editor window and choose "Connection->Change Connection", but is there any T-SQL code or system stored proc I can use to dynamically change server connection in the middle of a script?

Thanks,

Dave

The sqlcmd command prompt utility which can also be run in Query Editor in sqlcmd mode.

See SQL Server 2005 Books Online topic for script information

Using the sqlcmd Utility

http://msdn2.microsoft.com/en-us/library/ms180944.aspx

E. Using sqlcmd to execute code on multiple instances

The following code in a file shows a script that connects to two instances. Notice the GO before the connection to the second instance.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

See SQL Server 2005 Books Onlinetopic

Editing SQLCMD Scripts with Query Editor

http://msdn2.microsoft.com/en-gb/library/ms174187.aspx

change column to nullable

hello,
could someone help me out with some code to change a column from not nullable to nullable in a sproc? i need to do this temporarily and then change it back.
thanks!Refer to Books online for ALTER TABLE... ALTER COLUMN topics.|||i have checked BOL... i was just a little confused.

is this the correct way for an already existing column?

ALTER TABLE MyTable ALTER COLUMN myColumn int NULL

then to change it back...

ALTER TABLE MyTable ALTER COLUMN myColumn int NOT NULL

Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

Challenging sql code

I have two tabels : TABLE1 and TABLE2
TABLE 1 has the following Columns
OID
DATE1
ComonField1
Field1

TABLE 2 has the following Columns
OID
DATE2
ComonField1
Field2

What I am willing to achieve is the following:
Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@.2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.

Any help please.Originally posted by Nour
I have two tabels : TABLE1 and TABLE2
TABLE 1 has the following Columns
OID
DATE1
ComonField1
Field1

TABLE 2 has the following Columns
OID
DATE2
ComonField1
Field2

What I am willing to achieve is the following:
Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@.2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.


First of all, there is no way to do that with a single SQL query. And you probably don't want to join them because "there are a lot of field values in common."

What you probably should do is to open a query on each table with the ORDER BY clause so the rows will be sorted the same: by CommonField and by Date. Now you write code that will scan through both tables at the same time... COBOL-style.

The ORDER BY CommonField will cause all of the field values to be together, and since the two queries are sorted the same way you can decide what to do if the fields match, if one's smaller, if the other's smaller, if you're at end-of-table on one or the other or both... about six different cases. For a match, either the dates match or one's smaller or the other's smaller...

This problem can be solved with exactly one sequential pass which goes through both of these two streams at the same time. When all those reels of magnetic-tape were spinning in those science fiction movies, this is exactly what they were doing. In fact, when IBM sold punched card tabulators before computers existed, that's what they were doing, too!

Identically sorted streams of data...|||sundial, what the heck is your SQL Sever background, anyway? Over the last few days you have given some of the worst advice I've seen on this forum.

Nour,

Here is a general solution assuming that the OID fields can't be used to join your two tables. The solution is simpler if it can. This checked out syntactically in Query Analyzer, but without your exact table structure I of course could not test it. Other factors that could come into consideration would be things like duplicate DATE2 values in your second table.

Look this over and let me know if you have any more question.

select DateCompare.*,
table2.*
from
(select table1.OID,
table1.DATE1,
table1.ComonField1,
table1.Field1,
min(datediff(s, table2.DATE2, table1.DATE1)) MinSeconds
from table1
inner join table2 on table1.DATE1 >= table2.DATE2
group by table1.OID,
table1.DATE1,
table1.ComonField1,
table1.Field1) DateCompare
inner join table2 on datediff(s, table2.DATE2, DateCompare.DATE1) = DateCompare.MinSeconds

blindman|||Thanks Guys
I am going to try the SQL script to see if it works with my tables.|||Just a question to blindman
What's the table DateCompare, I am not creating a new table, I willl be updating the TABLE1 by creating two new columns where I will update with the values in the row where the date difference is the minimum.
A clarification:
I am comparing only rows from TABLE1 and TABLE2 which have the same OID et comonField
Thanks|||DateCompare is the name of the subquery, which has to be assigned a name so it can be joined in the outer query. You can give it any name you want as long as you change all the references to it.

blindman

Challenge: Can you optimize this?

This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.

The reason that I want to find records with non-duplicated RegJrnID
values is to create "reversal" records for these such that the reversal
record has identical values for every column except the TaxableAmount
which will contain a negative amount. (see: example data below).

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

/* Example data */

INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)

/* Show what's in the table - just because */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/* Make the results look pretty (optional) */

ORDER BY RegJrnID

/* Housekeeping */

DROP TABLE t1There are many ways to accomplish that. I would start with something
this (untested):

select pos.RegJrnID
from(
select * from t1 where TaxableAmount >0
) pos
left outer join
from(
select * from t1 where TaxableAmount <0
) neg
on pos.RegJrnID = neg.RegJrnID
where neg.RegJrnID is null|||Here's the tested (and slightly modified) version of your code...

SELECT pos.*

FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

WHERE neg.RegJrnID IS NULL

/* Make the results look pretty (optional) */

ORDER BY pos.RegJrnID|||According to the SQL Query analyzer your query is better going head to
head with the original representing 43.43% of the batch and the
original representing 56.57% of the batch.

A 13% improvement!

Thanks!|||On 6 Jun 2006 13:24:54 -0700, octangle wrote:

>This code is attempting to find records that have a RegJrnID that does
>not occur more than one time in the table.
>The reason that I want to find records with non-duplicated RegJrnID
>values is to create "reversal" records for these such that the reversal
>record has identical values for every column except the TaxableAmount
>which will contain a negative amount. (see: example data below).

Hi octangle,

Thanks for providing CREATE TABLE and INSERT statements. This made it
very easy to set up a test DB and fun to find an answer.

What worries me is that there's no primary key in your table. I hope
that you just forgot to include it in the script and that your real
table does have a key!

Here's a much quicker way. Running both your version and my version with
execution plan displayed, yours took 72% and mine 28%. Removing the
ORDER BY changed this to 64% / 36%. Still a nice gain.

SELECT RegJrnID, MAX(InvoiceDate),
MAX(InvoiceNumber), MAX(TaxableAmount)
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1
--ORDER BY RegJrnID

And here's another one, but it's correctness depends on some assumptions
I had to make because you forgot to include the primary key. With ORDER
BY, it's slightly more expensive than the previous version. With the
ORDER BY commented out, it only costs half as much!

SELECT RegJrnID, InvoiceDate,
InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate)
--ORDER BY RegJrnID

(Note - I have compared these queries using the sample data you provided
on a SQL Server 2005 database on my computer. Results will probably vary
on yoour database, especially if your table has indexes, your data
distribution is not like the sample data, and/or you are running another
version of SQL Server. I recommend that you test out the various
suggestions yourself before deciding.)

--
Hugo Kornelis, SQL Server MVP|||Upon further review...

As written in the previous post, records with a TaxableAmount of 0 will
not be found to be reversed... so in an attempt to remedy this I
modified the LEFT OUTER JOIN as follows:

FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

This succeeds at finding the 0 TaxableAmount records... but once a
companion reversal record is inserted into the database both records
(the original and a the reversal) are found on subsequent queries using
this technique... since these records are retrieved by the query as
records to reverse, these get reversed again (thus making a total of 4
instances of the original record - instead of 2 which is all are
needed). And if we repeat the process a 0 TaxableAmount record will
redouble it instances every time the process is run...

Now the questions are...

Can the above LEFT OUTER JOIN be fixed?

OR

Should 0 TaxableAmounts be processed in their own pass with their own
query (yuck)?

OR

Is the original query really better because it works for all
TaxableAmounts despite the fact that its 13% slower...

OR

Is there another option??|||one more question: what if there is only one row with negative amount?

INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)

and there is no corresponding row with positive amount? Nothing in the
posted DDL prevents you from that. In fact, originally I was
considering the query posted by Hugo, but realized it would return that
single row with negative amount and assumed it incorrect. It looks like
there might be no base for my assumption.|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> one more question: what if there is only one row with negative amount?
> INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
> and there is no corresponding row with positive amount? Nothing in the
> posted DDL prevents you from that. In fact, originally I was
> considering the query posted by Hugo, but realized it would return that
> single row with negative amount and assumed it incorrect. It looks like
> there might be no base for my assumption.

Having watched the thread from aside, I think the real problem is that
the data model needs improvement. I would add a bit column "isbalanceentry"
or some such. And of course add a primary key. (InvoiceNumber,
isbalanceentry) looks like a candidate.

Better get the data model in order, before looking at smart queries.

--
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|||On 6 Jun 2006 17:45:58 -0700, Alexander Kuznetsov wrote:

>one more question: what if there is only one row with negative amount?
>INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
>and there is no corresponding row with positive amount? Nothing in the
>posted DDL prevents you from that. In fact, originally I was
>considering the query posted by Hugo, but realized it would return that
>single row with negative amount and assumed it incorrect. It looks like
>there might be no base for my assumption.

Hi Alexander,

I've seenn nothing in the original post that justifies special treatment
of negative amounts. If these should be excluded, then my version can
still be used - just add AND MAX(TaxableAmount) >= 0 to the HAVING
clause.

However, I agree with Erland that a redesign might be a better choice if
something like that is the case.

--
Hugo Kornelis, SQL Server MVP|||Erland Sommarskog wrote:
> Having watched the thread from aside, I think the real problem is that
> the data model needs improvement. I would add a bit column "isbalanceentry"
> or some such. And of course add a primary key. (InvoiceNumber,
> isbalanceentry) looks like a candidate.

I concur that the schema might need some work. I was thinking that a
single nullable column negated_date might be sufficient, so that
instead of inserting one more row one just needs to update negated_date.|||Below is an aggregate script that includes everyone's suggested queries
so far...

Based upon feedback I have beefed up the test records to more
accurately reflect all of the potential scenarios that need to be
handled by this quey.

The original query (Query attempt #1 (Octangle)) generates the desired
result set and therefore is the benchmark of correctness for my
purposes.

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

INSERT INTO t1 VALUES (0, '20060120', '0000033', 0.00)
INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (11, '20060324', '3321110', -1200.16)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (13, '20051127', '1034501', -77.50)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (10, '20060421', '0000033', 0.00)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (12, '20060606', '0000001', 4431.55)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
INSERT INTO t1 VALUES (10, '20060517', '0000033', 0.00)
INSERT INTO t1 VALUES (11, '20060324', '3321110', 1200.16)
INSERT INTO t1 VALUES (12, '20060606', '0000001', -4431.55)

/* Show what's in the table */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

/* Query attempt #1 (Octangle) */

/* Pros: correct */
/* Cons: slow */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/*ORDER BY RegJrnID; * Make the results look pretty (optional) */

/* Query attempt #2 (Alexander) */

/* Pros: faster */
/* Cons: misses 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #3 (Alexander - tweaked by Octangle) */

/* Pros: faster */
/* Cons: finds too many 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #4 (Hugo) */

/* Pros: correct , fastest, returns results in RegJrnID order with
ORDER BY clause */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
"TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

/* Query attempt #5 (Hugo) */

/* Pros: fast */
/* Cons: not correct */

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate
)
/*ORDER BY RegJrnID * Make the results look pretty (optional) */

/* Housekeeping */

DROP TABLE t1

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

Queries as percent of batch (when just executing the queries in the
above script)

Query #1: 22.66% - Correct
Query #2: 19.77%
Query #3: 20.27%
Query #4: 12.63% - Correct
Query #5: 20.67%

Queries as percent when compared to only the original query (Query #1)

Query #1: 50.00% - Correct
Query #2: 42.58%
Query #3: 43.19%
Query #4: 32.14% - Correct
Query #5: 43.67%

At this point it looks like the clear winner is Query #4 by Hugo!

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

To address some of the observations/comments:

1. Negative transactions are possible - I augmented the test data to
include this case.
2. This is for a commercial product that has numerous existing
customers, I inherited the data model that this table is based upon...
my coding constraits are:
- I cannot add any columns (due to how we version a column change would
force this release to be considered a major release and not a minor
release as desired)
- I should not add any indexes/primary keys/uniqueness constriants for
performance reasons (see below)

The purpose of this table to store processed transaction results. It
needs to be as efficient as possible for insertions, so as to not slow
down the transaction processing engine. Reporting (and reversing groups
of transactions) are secondary concerns and it is acceptable for these
functions to be slower.

I sincerely want to thank everyone who chipped in a comment or
suggestion on this...|||1. If negative transactions are possible, than my query is incorrect.
2. You probably need a much larger set of test data to test different
approaches against. Also I would say there are at lest 2 possible
situations:
- most transactions are already negated.
- most transactions have not been negated yet.
In some cases in different situations different queries are the best. I
would try both and see if one and the same query is the best.

Good luck!|||Alexander Kuznetsov wrote:
> 1. If negative transactions are possible, than my query is incorrect.
> 2. You probably need a much larger set of test data to test different
> approaches against. Also I would say there are at lest 2 possible
> situations:
> - most transactions are already negated.
> - most transactions have not been negated yet.
> In some cases in different situations different queries are the best. I
> would try both and see if one and the same query is the best.
> Good luck!

FYI

The normal situation would be that most transactions are not negated in
this table. Negation would only occur if the billing system was
un-doing a billing run for some technical or business reason...
Therefore negating transactions would be rare, theoretically...

I appreciate the notion of getting better test data - this will
naturally accrue as I implement and test this soultion. I will make
sure to compare performance as this project matures...

Thanks again...!|||/* Query attempt #4 (Hugo) */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

I talked to a few folks around the office and none of us had ever
though to use MAX() to force values out of a query using a GROUP BY
clause...

e.g. if the query were changed to look like this:

SELECT *
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

The following error occurs for each column not mentioned in the GROUP
BY clause: "Column 't1.InvoiceDate' is invalid in the select list
because it is not contained in either an aggregate function or the
GROUP BY clause." So MAX() forces these values to participate in the
result set generated by this query...

My question with this is, "Is this technique safe for all major DBs
(Oracle, SQL Server, DB2 and MySQL) and will it work with all column
types?"|||octangle (idea.vortex@.gmail.com) writes:
> /* Query attempt #4 (Hugo) */
> SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
> as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
> FROM t1
> GROUP BY RegJrnID
> HAVING COUNT(*) = 1
> I talked to a few folks around the office and none of us had ever
> though to use MAX() to force values out of a query using a GROUP BY
> clause...
>...
> My question with this is, "Is this technique safe for all major DBs
> (Oracle, SQL Server, DB2 and MySQL) and will it work with all column
> types?"

Yes, it should on any RDBMS worth the name, as it is very plain standard
SQL.

Then again, MySQL has so many funny quirks, I suspect that one should
never take anything for granted with that engine.

--
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|||octangle (idea.vortex@.gmail.com) writes:
> - I should not add any indexes/primary keys/uniqueness constriants for
> performance reasons (see below)
> The purpose of this table to store processed transaction results. It
> needs to be as efficient as possible for insertions, so as to not slow
> down the transaction processing engine. Reporting (and reversing groups
> of transactions) are secondary concerns and it is acceptable for these
> functions to be slower.

There are good changes that a well-considered clustered index can improve
the performance. Not the least, because you can handle fragmentation better.

In any case, having a table without a primary key in order to save some
cycles on insertion is about criminal in my opinion. What to you when
the same data gets inserted twice? (Don't tell me that it never happens!).
And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
8 with datetime or 4 with smalldatetime. Here's is a second risk for
errors. Wonder how many entries for 20060230 you have...

As for the actual challenge, I prefer to stay out. I don't really want
to contribute to something which is obviously flawed.

--
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|||Erland Sommarskog wrote:
> There are good changes that a well-considered clustered index can improve
> the performance. Not the least, because you can handle fragmentation better.
> In any case, having a table without a primary key in order to save some
> cycles on insertion is about criminal in my opinion. What to you when
> the same data gets inserted twice? (Don't tell me that it never happens!).
> And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
> 8 with datetime or 4 with smalldatetime. Here's is a second risk for
> errors. Wonder how many entries for 20060230 you have...
> As for the actual challenge, I prefer to stay out. I don't really want
> to contribute to something which is obviously flawed.

Thanks for the tip on clustered index usage.

Criminal? A little severe - I'd call it a trade-off - a trade-off made
based upon the project requirements. Our process is a small part in a
larger customer billing cycle and everything we can do to be as small a
percentage of the overall work effort in the processing of every bill
is critical...

As it turns out the same RegJrnID must not be inserted twice. We have
programmatic control over this and if it were to occur there would be a
bug in the software or a serious proceedural issue on the part of the
user. In either case these represent bigger problems than the structure
of this table.

OK, OK - putting a date in a varchar is kludgy... again I simply
inherited this design... I think the big issue here was compatibility
with other RDBMS... We need to support DB2, Oracle, SQL Server and
MySQL with the same code base... So they all support character data
roughly the same... chalk it up as a rookie mistake... but oddly your
example that the table will allow Feb 30th could be seen as a feature
(i.e. we are fault tolerant of bad dates) - ultimately the generation
of the invoice date is controlled be the external application calling
our API - so allowing a bad date to float through to the DB isn't a big
deal to us...

Flawed? Again trade-offs have been made by SQL beginners... Funny thing
is that it seems to work very well for our customers and they seem
happy, so I'd say that flaws are in the eye of the beholder...

Thanks for holding us to a higher standard...

:-)|||octangle (idea.vortex@.gmail.com) writes:
> Criminal? A little severe - I'd call it a trade-off - a trade-off made
> based upon the project requirements.

I would not call it trade-off, only off.

Then again, I'm the coward kind of guy that always wear a safety belt
when I'm driving(*) and all that.

(*) OK, so it happened once I didn't put it on. That was when I was
drive up for my driving license!

--
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|||On 7 Jun 2006 10:54:44 -0700, octangle wrote:

>/* Query attempt #4 (Hugo) */
>/* Pros: correct , fastest, returns results in RegJrnID order with
>ORDER BY clause */
>SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
>MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
>"TaxableAmount"
>FROM t1
>GROUP BY RegJrnID
>HAVING COUNT(*) = 1

Hi octangle,

I don't know what the remark about returning results in RegJrnID order
with ORDER BY means - all queries will return results in that order if
yoou include an ORDER BY. And without the ORDER BY, some of the queries
might return the results in that order some of the time, maybe even
every time during testing, but there's no guarantee that it will remain
so in production. In shhort - if you need a specific order, use an ORDER
BY - always!

>/* Query attempt #5 (Hugo) */
>/* Pros: fast */
>/* Cons: not correct */

Only because the data you originally provided was not enough to show
what column oor combination of columns makes a row unique. And your
reply still doesn't show it, so my next attempt might well be wrong
again :-((

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND ( a.TaxableAmount <> b.TaxableAmount
OR a.InvoiceDate <> b.InvoiceDate )
)

Without the ORDER BY, this is significantly faster than attempt #4 when
tested with your test data. But with a much larger set of test data,
attempt #4 is faster (though this might be different with your data, as
it might be distributed differently).

>2. This is for a commercial product that has numerous existing
>customers, I inherited the data model that this table is based upon...
>my coding constraits are:
>- I cannot add any columns (due to how we version a column change would
>force this release to be considered a major release and not a minor
>release as desired)
>- I should not add any indexes/primary keys/uniqueness constriants for
>performance reasons (see below)

I concur with everything Erlland says about this. And I'd still like to
know which (combination of) column(s) you can use to uniquely identify a
single row.

--
Hugo Kornelis, SQL Server MVP|||Hi There,
If the following is your requirement.
"
This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.
"
Then

Select * from Yourtable where ID in (Select ID from group by ID having
count(1)=1)
-- Give some order by etc as per your requirement
Output:--
9, '20060213', '2130009', 40.01
4, '20060105', '9302221', 612.12
5, '20060105', '0003235', 18.11
7, '20060115', '4234444', 44.52
8, '20060115', '0342222', 95.21
2, '20060101', '5440033', 231.01

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

octangle wrote:
> This code is attempting to find records that have a RegJrnID that does
> not occur more than one time in the table.
> The reason that I want to find records with non-duplicated RegJrnID
> values is to create "reversal" records for these such that the reversal
> record has identical values for every column except the TaxableAmount
> which will contain a negative amount. (see: example data below).
> /* Set up */
> CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
> VARCHAR(20), TaxableAmount DECIMAL(32,8))
> /* Example data */
> INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
> INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
> INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
> INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
> INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
> INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
> INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
> INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
> INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
> INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
> INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
> INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
> /* Show what's in the table - just because */
> SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate
> /* Query for records to reverse */
> SELECT *
> FROM t1 a
> /* Ignore records that have already been reversed */
> WHERE a.RegJrnID != ALL
> /* This subselect finds reversed records (i.e. those that have a
> duplicate RegJrnID) */
> (
> SELECT b.RegJrnID
> FROM t1 b
> GROUP BY b.RegJrnID
> HAVING COUNT(*) > 1
> )
> /* User selection criteria are appended here */
> /* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */
> /* Make the results look pretty (optional) */
> ORDER BY RegJrnID
> /* Housekeeping */
> DROP TABLE t1

Challenge revisisted

Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:

>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

cdosysmail with attachment

With cdosysmail I have gotten my code to work to programatically send emails
out as HTML.
Does anyone know how to tweak the standard CDO example to send the query
results as an attachment?
thanksI strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sample
of using CDO to send an attachment, but please read my first sentence
again).
A
"26point2er" <26point2er@.discussions.microsoft.com> wrote in message
news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
> With cdosysmail I have gotten my code to work to programatically send
> emails
> out as HTML.
> Does anyone know how to tweak the standard CDO example to send the query
> results as an attachment?
> thanks
>|||Thank you for the input. I will post my results here.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
> cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sampl
e
> of using CDO to send an attachment, but please read my first sentence
> again).
> A
>
>
> "26point2er" <26point2er@.discussions.microsoft.com> wrote in message
> news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
>
>

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.From varchar(100) , @.To varchar(100) , @.Subject varchar(100), @.Body
varchar(4000)
/ ****************************************
**
This stored procedure takes the parameters and sends an e-mail. All the mail
configurations are hard-coded in the stored procedure. Comments are added to
the stored procedure where necessary. References to the CDOSYS objects are
at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

CDOSYS email body truncated

I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- Save the configurations to the message object.
exec @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
exec @.hr = sp_OASetProperty @.iMsg, 'To', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).

> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

cdonts send mail T-SQL - IIS SMTP setup

hello,

I would like to send mail from sql server using CDONTS.

I have the sql code for this and it works for internal addresses.

I get an error/warning saying that it cannot relay for external addresses when I try an outside domain.

Is there settings in IIS for the SMTP service that I can change to allow it to relay to the external domains?

I have everything running on the SQL Server, and I do not have access to the exchange server.

ThanksCDONTS sends email to the Local SMTP server. You can specify a "smart host" that the local server can forward any email it can't deliver.

Search the help for the IIS SMTP Virtual Server. It's likely the network blocks any unapproved system from using the smtp port, and it's also likely the approved ones will need to allow your system to relay through them.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

CDO mail attachment is not working

Hi
I have some SQL code below which I'm running on a server DEV_TESTSTAGE2 with
Windows 2000 and SQL Server 7. I'm trying to send an attachment which works
fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
somewhere else on the network it send the email without the attachment and I
get the error message below. I've had a look on the internet but most of the
web peges refer to permissions problems in ASP. In my case I'm using SQL
Server objects and I'm running the query with Windows authentication and my
NT logon id
has access to the file attachement . I've tried running the query using SQL
authentication but I get the same error.
Any help would be gratefully appreciated
-2147024891
Source: CDO.Message.1
Description: Access is denied
declare @.HResult int
declare @.HR int
declare @.iMsg int
declare @.Text varchar(8000)
Declare @.source varchar(255)
Declare @.output varchar(1000)
Declare @.description varchar(500)
declare @.attachfile varchar(1000)
--************* Create the CDO.Message Object ************************
exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
--error handling for failure to create object......
select @.HResult, @.iMsg
--***************Configuring the Message Object ******************
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value','CHOPGBBES001.gb.cannonsgroup.net'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rverport").Value','25'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpus
essl").Value','False'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpco
nnectiontimeout").Value','60'
-- Save the configurations to the message object.
EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
set @.attachfile = '\\flavius\IT\pstat.txt'
--set @.attachfile = 'c:\boot.ini'
EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
RonanRonan
Do you have any permissions issue to the folder/file?
in addition ,take a look at
http://www.sqldev.net/xp/xpsmtp.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
> Hi
> I have some SQL code below which I'm running on a server DEV_TESTSTAGE2
> with
> Windows 2000 and SQL Server 7. I'm trying to send an attachment which
> works
> fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
> somewhere else on the network it send the email without the attachment and
> I
> get the error message below. I've had a look on the internet but most of
> the
> web peges refer to permissions problems in ASP. In my case I'm using SQL
> Server objects and I'm running the query with Windows authentication and
> my
> NT logon id
> has access to the file attachement . I've tried running the query using
> SQL
> authentication but I get the same error.
> Any help would be gratefully appreciated
> -2147024891
> Source: CDO.Message.1
> Description: Access is denied
>
> declare @.HResult int
> declare @.HR int
> declare @.iMsg int
> declare @.Text varchar(8000)
> Declare @.source varchar(255)
> Declare @.output varchar(1000)
> Declare @.description varchar(500)
> declare @.attachfile varchar(1000)
>
> --************* Create the CDO.Message Object ************************
> exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
>
> --error handling for failure to create object......
> select @.HResult, @.iMsg
>
> --***************Configuring the Message Object ******************
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/send
using").Value','2'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
server").Value','CHOPGBBES001.gb.cannonsgroup.net'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
serverport").Value','25'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
usessl").Value','False'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
connectiontimeout").Value','60'
>
> -- Save the configurations to the message object.
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
>
> -- Set the e-mail parameters.
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
> set @.attachfile = '\\flavius\IT\pstat.txt'
> --set @.attachfile = 'c:\boot.ini'
> EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
>
> --
> Ronan|||Hi Uri
Yes I should have mentioned I do have access to the file...it has 'Everyone'
permission
I had a look at the article you sent me...it refers to xp_smtp_sendmail...I
can't find that extended stored procedure in the master database of SQL
Server 2000...I have xp_sendmail but that requires you set up
a MAPI client which I don't want to do I want to send it via SMTP
Ronan
"Uri Dimant" wrote:

> Ronan
> Do you have any permissions issue to the folder/file?
> in addition ,take a look at
> http://www.sqldev.net/xp/xpsmtp.htm
>
>
> "Ronan" <Ronan@.discussions.microsoft.com> wrote in message
> news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
>
>|||I have used the xp_smtpsendmail extended stored procedure. It works well,
but you will have to download the .ddl ..google it, and you'll find it. I've
used it to send reports generated as text files to business managers in the
past with great success...you will have to copy it to your sql\bin folder,
and registewr it inside sql, to get it to work...easy to do.
"Ronan" wrote:
> Hi Uri
> Yes I should have mentioned I do have access to the file...it has 'Everyon
e'
> permission
> I had a look at the article you sent me...it refers to xp_smtp_sendmail...
I
> can't find that extended stored procedure in the master database of SQL
> Server 2000...I have xp_sendmail but that requires you set up
> a MAPI client which I don't want to do I want to send it via SMTP
> --
> Ronan
>
> "Uri Dimant" wrote:
>|||Hi Tom
I downloaded and installed xp_smtp_sendmail and again it works fine for
attachements
which are on the machine where xp_smtp_sendmail is registered but it
still doesn't seem
to work when the attachment is on a different machine. I think I'll just
have to put my attachments
on the machine where xp_smtp_sendmail is registered as a work around.
Thanks for your help.
Regards
Ronan
Ronan
"Tom Mongold" wrote:
> I have used the xp_smtpsendmail extended stored procedure. It works well,
> but you will have to download the .ddl ..google it, and you'll find it. I'
ve
> used it to send reports generated as text files to business managers in th
e
> past with great success...you will have to copy it to your sql\bin folder
,
> and registewr it inside sql, to get it to work...easy to do.
> "Ronan" wrote:
>

Catching SQL Exceptions for ConnStrings in Web.Config

Hi,

I have a connection string in my web.config - to which I then refer to in my code through all my controls, they're all databound to it.

Anyway - how do I catch any errors - such as when I want to view the website on a train, if I'm working on it.

I don't want it to crash and burn [the site, not the train] - if I dont have access to the sql server.

How can I wrap it in a try block!?- How do i then deal with controls which refer to the connection string?

One solution I thought of - is to programmatically set all the databinding - and not just with the GUI. As that way I can wrap everything in a try{}catch{} block.

Any other - site-wide way of doing this?

Thank you,

R

If it's a connstrings error, then one way would be to try them when the application starts up.

If you are looking for error handling during the select/insert/delete events of a sqldatasource, then check the errors in the selected/inserted/deleted events, and set e.errorhandled property to true (or not if you want the default error handling). Of course, that's not site wide.

|||

i want to capture an error at web.config stage.

so if you have something like this in your web.config:

<add name="DataStoreConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=mehDB;Integrated Security=True;uid=submit;pwd=" providerName="System.Data.SqlClient"/>

then have it produce an error on the site - and not just prevent the website from loading. as the database component althogh significant, only is necessary for logged in users - so not to affect users that are just browsing.

how do i catch the error at such an early stage?

thank you.

Catch MSSQL triggers in VB6

Hi
Is there a way to run VB code when a trigger is executed ? Maybe to define a VB event that will occur when a trigger is executed ??
I've tried googling...
Thanks,
Inon.The simplest way to run VB code from a trigger is to create a character mode executable (one with no GUI component). The trigger can start this running on the server using master.dbo.xp_cmdshell calls.

While it is possible to contrive an example where the server launches an application on the client, it is neither easy nor practical. I personally wouldn't try it.

-PatP

Cast problems with CLR

hi,

i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException:
at UserDefinedFunctions.FillRow(Object obj, String& PID)
.

Below is my code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections;

public partial class UserDefinedFunctions
{
const string SELECT =
@."SELECT pointData.PID
FROM pointData
INNER JOIN pointDevices ON pointData.PID = pointDevices.PID
WHERE pointDevices.UUID = @.UUID AND
DATEADDED >= @.DATE_START AND
DATEADDED <= @.DATE_STOP
ORDER BY DATEADDED";

[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read, TableDefinition="PID nvarchar(50)")]
public static IEnumerable createJourney(SqlString UUID, DateTime DATE_START,DateTime DATE_STOP, SqlDouble JOURNEY_DELAY, SqlDouble JOURNEY_DISTANCE)
{
ArrayList RAW_PID_LIST = new ArrayList();

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();

SqlCommand command = new SqlCommand(SELECT,conn);

command.Parameters.AddWithValue("@.UUID",UUID);
command.Parameters.AddWithValue("@.DATE_START",DATE_START);
command.Parameters.AddWithValue("@.DATE_STOP",DATE_STOP);

SqlDataReader reader = command.ExecuteReader();

using (reader)
{
while (reader.Read())
{
RAW_PID_LIST.Add(reader[0]);
}
}
}

return RAW_PID_LIST;
}

private static void FillRow(Object obj, out string PID)
{
object[] row = (object[])obj;

PID = (string)row[0];
}
};

could someone give me a clue as to why i might be getting this error please.

thank you.

FillRow gets only one object at a time. So, you should try this one:

private static void FillRow(Object obj, out string PID)
{
PID = (string)obj;
}

Cast as Decimal

myTable in the below code examples resides in a linked Visual FoxPro
database. myTable contains a field called myDecimalField as well as several
others exactly like it and are of Decimal (9,1) not null type.
All the other fields select fine in SQL, but myDecimalField gives the ERROR
below when I SELECT it.
Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
VarChar CAST and I get the same ERROR below.
Can someone help me with syntax in CODE 2 and convert myDecimalField into a
DECIMAL format so I can retain the fields decimals and numberic type?
**********************
CODE 1 (works):
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField FROM myTable
CODE 2 ( doesn't work):
SELECT myIdField, CONVERT(DECIMAL(18, 4),
CAST(myDecimalField AS VARCHAR(55))) AS myDecimalField FROM myTable
ERROR:
OLE DB error trace [OLE/DB Provider 'VFPOLEDB'
IRowset::GetData returned 0x80040e21: Data status returned from the
provider: [COLUMN_NAME=myDecimalField
STATUS=DBSTATUS_E_UNAVAILABLE]].
Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column
'[VFPOLEDB].myDecimalField' from the OLE DB provider 'VFPOLEDB'. The
provider cannot determine the value for this column.Scott Bailey (sbailey@.mileslumber.com) writes:
> myTable in the below code examples resides in a linked Visual FoxPro
> database. myTable contains a field called myDecimalField as well as
> several others exactly like it and are of Decimal (9,1) not null type.
> All the other fields select fine in SQL, but myDecimalField gives the
> ERROR below when I SELECT it.
> Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
> and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
> VarChar CAST and I get the same ERROR below.
> Can someone help me with syntax in CODE 2 and convert myDecimalField
> into a DECIMAL format so I can retain the fields decimals and numberic
> type?
The situation certainly looks spooky, but the root problem is obviously
a problem with FoxPro, or the FoxPro provider. I would guess that there
some rows where myDecimalField has some illegal value.
Assuming that myTable has an column called id, of which the lowest value
is 1, and the highest is 100, you could do
SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
If that gives the error, narrow down the interval to 1 AND 25 and so on.
Of course it's a good idea to look at the data from FoxPro as well.
If you want an SQL Server solution, you would have to bounce the data
over a temp table, so the conversion from varchar takes place in
SQL Server.
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|||Couple of things:
1. The error occurs on all records so i know it's not bad data, plus there's
another field with same problem.
2. Could you provide some syntax example of creating a temp table with the
varchar conversion and transferring it as you suggested? I've never used a
temp table before.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9BCADA85Yazorman@.127.0.0.1...
> Scott Bailey (sbailey@.mileslumber.com) writes:
> The situation certainly looks spooky, but the root problem is obviously
> a problem with FoxPro, or the FoxPro provider. I would guess that there
> some rows where myDecimalField has some illegal value.
> Assuming that myTable has an column called id, of which the lowest value
> is 1, and the highest is 100, you could do
> SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
> If that gives the error, narrow down the interval to 1 AND 25 and so on.
> Of course it's a good idea to look at the data from FoxPro as well.
> If you want an SQL Server solution, you would have to bounce the data
> over a temp table, so the conversion from varchar takes place in
> SQL Server.
>
> --
> 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|||scott (sbailey@.mileslumber.com) writes:
> 1. The error occurs on all records so i know it's not bad data, plus
> there's another field with same problem.
Weird. But I'm not a Foxpro person, so I have no idea of what could
be going on.

> 2. Could you provide some syntax example of creating a temp table with the
> varchar conversion and transferring it as you suggested? I've never used a
> temp table before.
CREATE TABLE #spookydecimal (id int NOT NULL,
decvalue varchar(55) NULL)
-- Add other columns as needed.
INSERT #spookydecimal(id, decvalue)
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField
FROM myTable
SELECT id, decvalue, case(decvalue as decimal(18,4))
FROM #spoookydecimal
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|||thanks.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9DB71F9EE0Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Weird. But I'm not a Foxpro person, so I have no idea of what could
> be going on.
>
> CREATE TABLE #spookydecimal (id int NOT NULL,
> decvalue varchar(55) NULL)
> -- Add other columns as needed.
> INSERT #spookydecimal(id, decvalue)
> SELECT myIdField, CAST(myDecimalField AS
> VARCHAR(55)) AS myDecimalField
> FROM myTable
> SELECT id, decvalue, case(decvalue as decimal(18,4))
> FROM #spoookydecimal
> --
> 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