how to change the crystal report database location at runtime in vb.net

Hello,

I am using the following codings to change the databse location at runtime. But i found an error 'invalid field name in formula' in some of the reports. The error occurs when we use the databse fields in 'formula field editor' of crystal reports. let me know the reason why its happen? find and send a solution to solve this problem.

Dim logOnInfo As New TableLogOnInfo()
Dim crtableLogoninfo As New TableLogOnInfo()
Dim crConnectionInfo As New ConnectionInfo()
Dim TableLocation As String

Dim vTable As Table
crConnectionInfo.ServerName = gConnServerName
crConnectionInfo.DatabaseName = gConnDatabase
crConnectionInfo.UserID = gConnUserName
crConnectionInfo.Password = gConnPassword

RptDoc.SetDatabaseLogon(gConnUserName, gConnPassword, gConnServerName, gConnDatabase)

For Each vTable In RptDoc.Database.Tables
crtableLogoninfo = vTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
vTable.ApplyLogOnInfo(crtableLogoninfo)
'' vTable.TestConnectivity()
'' MsgBox(vTable.TestConnectivity)
'' MsgBox(vTable.Fields(0).Name())

vTable.Location = Trim(crConnectionInfo.DatabaseName) & ".dbo." & Trim(vTable.Location.Substring(vTable.Location.LastIndexOf(".") + 1))

Next

thank you .
shanthipl reply

How to change the content?

Hi there !

I'm quite desperate as I can't find a quick method to partially change the content of a field.

I explain:
In a field named 'notes', PART of the content is:
<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>
This content is wrong.

It must be:
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>

How can I make the change with a SQL Query instead of manually make the change row by row (approximately 1500 rows contain the error!!!) considering the fact that abc@.domain.com is different in each of the rows?

Many thanks in advance for any help!

Best regards,

Gerardupdate daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'|||update daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'
Hi !

Thank you for replying. I appreciate.
But, as I'm quite old and learning, I want to really understand the syntax I'll write.

Some questions arise:
1 - Why have we "replace" repeated twice?
2 - The single and double quotes you wrote in your example doesn't correspond to those I put in my example.
3 - What will happen to the email addresses which are differents?

Be sure, professor, I certainly do not want to bother you! :-)

Best regards,

Gerard|||did you try it? what results did it give?|||Yes, I tried... on a copy of the table!
After two attempts, this worked fine:
UPDATE `table_name` SET column_name = REPLACE(column_name, '"', '') WHERE column_name LIKE '%href=\'mailto:"%';

I thank you for your valuable help.

Best.|||all you have done is remove the double quotes

in other words, you've changed this --<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>to this --<a href='mailto:abc@.domain.com'>abc@.domain.com</a>which is not what you asked

you asked for this --<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>|||YES you are right about the kind of quotes but the principle remained the same: remove the extra quotes just before and after the address, no matter if the quotes are single or double. I opted for the single quotes which are more "orthodox"! :-)|||either single or double can be used

see http://www.w3.org/TR/html4/intro/sgmltut.html#attributes

if you look at all the web sites out there (okay, just look at some, you don't really have to look at all of them) you will find that most of them use double quotes

also, the incidence of a single quote required inside a double-quoted string is more frequent than a double quote inside a single-quoted string

:)|||You are right.
I have read about attributes in the page you mentioned.
And I tried to follow your advice and to replace the single quotes with double quotes.

For example, I tried to replace where it is necessary
<a href='mailto:abc@.domain.com'>
with
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">
(Of course, this text isn't the only text contained in the field...)

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '', "") WHERE column_name LIKE '%mailto:\'%';
This query has no effect!

What's wrong?|||REPLACE(column_name, '''', '"')|||Still no effect !
And I perfectly know that more than a thousand rows must be affected...

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:\'%';|||you keep messing up in different places
WHERE column_name LIKE '%mailto:''%'|||Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';
...and nothing happens, right or wrong!|||what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:''%'and what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:"%'this should tell you why

:)|||Both queries return zero !!!!!

Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:
Autres adresses de courriel : <a href='mailto:jr.reverte@.laposte.fr'><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href='mailto:entsoa23@.fr.st'><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

and I want to change it to:
Autres adresses de courriel : <a href="http://links.10026.com/?link=mailto:jr.reverte@.laposte.fr"><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href="http://links.10026.com/?link=mailto:entsoa23@.fr.st"><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

This is driving me quite mad... :-) GRRRR !!!|||i believe you are not copying my exact code, but rather typing it in yourself, and making errors while typing

you must make very sure that you understand the difference between two single quotes in a row:''and a doublequote:"|||Obviously, yes. I know the difference.
And I copied the exact code you posted.

But I found one error.
It isn't
'%mailto:\'%'
but
'%\'mailto:%'
we are looking for.

The position of the quote was wrong.
And this change makes the query returning a number!

Using this change, I unsuccessfully (no row affected) tried to apply the modifying query this way (real names of table and column):
UPDATE `_annuairecopy` SET notes = REPLACE ( notes, '', "") WHERE notes LIKE 'href=%\'mailto:%\'%';
(please report to the REAL content of field I previously posted)|||please stop using those horrible backslashes!!!
UPDATE `_annuairecopy`
SET notes = REPLACE ( notes, '''', '"')
WHERE notes LIKE '%href=''mailto:%'|||Yes indeed, they are horrible.
But I was so tired to get error messages each time I used an even number of single quotes without a backslash!

The query has worked fine !!!
Could you imagine how happy I am?

I thank you for your patience and all the time you spent helping me.

Best from Gerard.

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!
kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!
|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||But no method to change the collation of all field in all table of a
database? if sepcify collation in the join statement, then all sql statement
need to use such method, quite trouble...
I know there is alter column..., but I also know this method can't work on
primary, foreign key, index field..., right?
"MC" wrote:
> Meanwhile, you can specify collation in the join statement. Check for
> COLLATE.
> MC
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> > when I join two table, I got the error "Cannot resolve collation conflict
> > for
> > equal to operation.", I know this is due to the collation is different for
> > two table, so I want to write a script to change all field in all table of
> > a
> > database to use the same collation, how to write that script?
> > Thx!!
>
>|||kei
Have you checked it?
"kei" <kei@.discussions.microsoft.com> wrote in message
news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> But no method to change the collation of all field in all table of a
> database? if sepcify collation in the join statement, then all sql
> statement
> need to use such method, quite trouble...
> I know there is alter column..., but I also know this method can't work on
> primary, foreign key, index field..., right?
> "MC" wrote:
>> Meanwhile, you can specify collation in the join statement. Check for
>> COLLATE.
>> MC
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> > when I join two table, I got the error "Cannot resolve collation
>> > conflict
>> > for
>> > equal to operation.", I know this is due to the collation is different
>> > for
>> > two table, so I want to write a script to change all field in all table
>> > of
>> > a
>> > database to use the same collation, how to write that script?
>> > Thx!!
>>|||I still don't know the answer, could you write more explicit? I check
"Changing Collations" in BOL, and it only teach how to change collation for
one column only, and with limitation that if the column is an index..., then
the method is not work, so could you write the solution more clearly.
Thx!!
"Uri Dimant" wrote:
> kei
> Have you checked it?
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> > But no method to change the collation of all field in all table of a
> > database? if sepcify collation in the join statement, then all sql
> > statement
> > need to use such method, quite trouble...
> > I know there is alter column..., but I also know this method can't work on
> > primary, foreign key, index field..., right?
> >
> > "MC" wrote:
> >
> >> Meanwhile, you can specify collation in the join statement. Check for
> >> COLLATE.
> >>
> >> MC
> >>
> >>
> >> "kei" <kei@.discussions.microsoft.com> wrote in message
> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> >> > when I join two table, I got the error "Cannot resolve collation
> >> > conflict
> >> > for
> >> > equal to operation.", I know this is due to the collation is different
> >> > for
> >> > two table, so I want to write a script to change all field in all table
> >> > of
> >> > a
> >> > database to use the same collation, how to write that script?
> >> > Thx!!
> >>
> >>
> >>
>
>|||You need to alter each column that has text datatype (varchar, char....).
Alternatively, you can script your database in enterprise manager and
specify 7.0 compatibility. That should give you script without collations.
Then you could execute that script and you get empty database. All thats
left for you is transfer data....
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:26C46269-DC86-4102-89A5-C9195EB47A57@.microsoft.com...
>I still don't know the answer, could you write more explicit? I check
> "Changing Collations" in BOL, and it only teach how to change collation
> for
> one column only, and with limitation that if the column is an index...,
> then
> the method is not work, so could you write the solution more clearly.
> Thx!!
> "Uri Dimant" wrote:
>> kei
>> Have you checked it?
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
>> > But no method to change the collation of all field in all table of a
>> > database? if sepcify collation in the join statement, then all sql
>> > statement
>> > need to use such method, quite trouble...
>> > I know there is alter column..., but I also know this method can't work
>> > on
>> > primary, foreign key, index field..., right?
>> >
>> > "MC" wrote:
>> >
>> >> Meanwhile, you can specify collation in the join statement. Check for
>> >> COLLATE.
>> >>
>> >> MC
>> >>
>> >>
>> >> "kei" <kei@.discussions.microsoft.com> wrote in message
>> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> >> > when I join two table, I got the error "Cannot resolve collation
>> >> > conflict
>> >> > for
>> >> > equal to operation.", I know this is due to the collation is
>> >> > different
>> >> > for
>> >> > two table, so I want to write a script to change all field in all
>> >> > table
>> >> > of
>> >> > a
>> >> > database to use the same collation, how to write that script?
>> >> > Thx!!
>> >>
>> >>
>> >>
>>

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict fo
r
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change From field in e-mail sent by SQL Mail?

We use SQL Mail to sent e-mail notification to customers. In the From field of the e-mail they see the ugly name of our SQL Server account, under which SQL Mail runs. How can I change that From to something more meaningful for the customers?
SQL Mail doesn't support that.
Xp_smtp_sendmail from www.sqldev.net does, however. And it doesn't rely on MAPI.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:64C50FD1-144B-4746-ADB1-3C3D6FEE6B67@.microsoft.com...
> We use SQL Mail to sent e-mail notification to customers. In the From field of the e-mail they see the ugly
name of our SQL Server account, under which SQL Mail runs. How can I change that From to something more
meaningful for the customers?
|||Thanks, Tibor.
Just one more try. I forgot to mention that SQL Mail works through Exchange Server. Is there any way to set that nickname for the SQL account at Exchange?
|||I don't know. I suggest you check with someone who know exchange :-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9C059E-6CD9-46D7-87B9-CAF7383658C7@.microsoft.com...
> Thanks, Tibor.
> Just one more try. I forgot to mention that SQL Mail works through Exchange Server. Is there any way to set
that nickname for the SQL account at Exchange?

How to change data in a crystal report

Hi all,
I have developed a report where i have field called Required. It has a value either 1 or 0 in database. Now I want to display 'Required' if the value is 1 and 'Not Required' if the value is 0. Plz help me to do so...
Thanks in Advance..
PrashanthHi all,
I came to know that this problem can be sorted out by writing a formula. But I dont know how to write a formula. Anybody help me...
IIF (({HY_PDC_PARAMETER_SET_DTL.REQUIRED}= '1'),'Required' ,'NotRequired' )
When I write so its displayin Required for all rows...

Thanks in Advance,
Prashanth.M|||Hi

there's nothing wrong with the formula u have written,
but if the field is numeric type then you don't need the quotes and if it is a bit data type then you need to check the field with true or false.

You can also try the formula in this way :

if {table.Required}=1 then 'Required' else 'Not Required'

how to change color of a field if it is duplicate..........

hi guys
how can i change the fore color of a field value if there is a duplicate of it.
thanks in advance but only for those who help me (Lolz) ;)
Sillytry like
in format editor in the font tab write a formula on color [x+2]

IF (field1) = PREVIOUS(field1) THEN
RGB(255,0,0)
ELSE
RGB(0,0,0)

Gragi|||hi grag

thanks for ur reply but problem in my case is that
the duplicate value can reside any where in the list.

How to change an existing UID to autonumbering?

Hullo!

Doubt it matters too much, but I'll just start off by saying that I'm using C# ASP.NET 2.0 in Visual Studio 2005.

I currently have a UID field entitled "ID", and I'd like to make it autonumbering so that users can input stuff into it through a simple web interface and not have to worry about the user's ID.

Anyone know how I could go about doing this? I can't seem to find a way through VS2005's GUI, and I can't seem to find anything on the internet about doing it through an SQL statement.

Thanks!I may have already figured my own problem out - I just have to test something really quick. If it works, I'll come back and tell ya what I did.

How To Change All Records in a Single Field?

Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer=92s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone=92s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!Create a view and show them the results of the view.
CREATE VIEW dbo.foo
AS
SELECT col1, col2, AccountNumber = 'foo', col3, ...
FROM dbo.Original_Table;
<alvinstraight38@.hotmail.com> wrote in message
news:d2e508c2-4c8e-4627-90dc-5b7e38dabb91@.e39g2000hsf.googlegroups.com...
Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer?s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone?s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!|||UPDATE ThatTable
SET SomeColumn = 'NewValue'
Since there is no WHERE clause to limit the effect, every row in
ThatTable will be updated.
The problem with changing a customer's account number that way is that
it is likely to violate a UNIQUE constraint, as well as appearing in
many tables. It is likely to be used for JOINs.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 06:18:42 -0700 (PDT),
"alvinstraight38@.hotmail.com" <alvinstraight38@.hotmail.com> wrote:
>Hey guys,
>I am in a bit of a pickle. I have to do a software training today,
>and my training database is hosed. I have a production database, but
>I do not want the users to actually see a customer?s live data on the
>screen. I want to make a global alternation to all records on a
>single field such as change everyone?s account number to a specific
>value.
>Is there a way to do this in SQL 2005?
>Thanks!

How to change a field's type?

Hi,
How could I change the field type through T-SQL?
I have tried Using the ALTER:
ALTER TABLE tblName ALTER COLUMN myID int
It didn't work...

And also, how could you rename a fieldname?
cheers,
Paul June A. DomagThe syntax you have listed is correct, although you may need to specify NULL/NOT NULL:
Alter Table dbo.SomeTable
Alter Column SomeField Varchar(101) NULL
For the rename funtion, use sp_rename you can find the syntax in books online...
Edit: If you are getting an error, please post the details of the error so we can dig a little deeper...
|||Hi,

I tried placing a NOT NULL statement and still it doesn't work...

ALTER TABLE myTable ALTER COLUMN myCol int NOT NULL

What is wrong with my statement?
BTW, the myCol field is numeric (I don't know if it matters)

ALSO, is there another alternative in renaming the field? Coz Im trying to achieve this in SQLServerCE and there is no sp_rename function in SQLServerCE...
cheers,
Paul June A. Domag|||Hey Paul,
I'm not familiar with SQL Server CE, so not sure if the T-SQL is any different.
In the absence of sp_rename, the following should work
-- Create the renamed column
Alter Table myTable with Check Add newColname varchar(100) not null
Go
-- copy the data to the new column
Update myTable set newColumn = oldColumn
Go
-- delete the old column
Alter Table myTable Drop Column oldColumn
Go
For the Alter statement that isn't working, can you please post the error message that you receive?
Thanks,
Tyler|||Hi,
Whew! Why didn't I think about that? tsk....
Thank you very much! You really made my day...

Here's the error message that I keep on getting in SQLServerCE:

FAILED: ALTER TABLE CustomerAddresses
ALTER COLUMN IDNew int
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 7
Param. 2: 0
Param. 3: IDNew
Param. 4:
Param. 5:

Cheers,
Paul June A. Domag|||Hi,
In addition to that, how could I modify a field to make it an IDENTITY column?
I tried:
ALTER TABLE mytable ALTER COLUMN IDNew IDENTITY

It generates an error saying that my field could not be made identity column after it was made...

BTW, the column that im trying to rename is an IDENTITY column, so by using the solution that you have provided, I need to restore the Autonumbering function on my field...
cheers,
Paul June A. Domag|||Hi Paul,
I think that Identity may be the issue. I suppose for an easy answer you could use the same method to change the column type, i.e., create a new column, copy the values and delete the orginal column.
To restore the numbering just do it when you declare the column:
Alter Table SomeTable
With Check Add
MyColumn INT IDENTITY (x, y) NOT NULL
Where x is the SEED value (i.e., what the next number will start at) and y is the INCREMENT (usually 1 to increment by one)
So if you where to declare the column as IDENTITY (1500, 1) the next record inserted would automatically have a value of 1500, etc.
Let me know if that makes sense.
Thanks!
Tyler|||Hi,
Sorry, but it didn't work. It seems that Identity columns couldn't be updated...
I used your steps:

ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
UPDATE t SET myNewCol = ID ' Error Here
ALTER TABLE t DROP COLUMN ID

BTW, my ID field (w/c is the identity column) is not entirely sequential. What I mean is, there have been some deletions and the numbering is somewhat jumbled. eg 1,2,3,8,11,13,21,22,25...

In this case I can't even use the new Identity Column that I would create coz the newly created identity would be sequential and thus would break my relationship with other tables...

Any other bright ideas?
cheers,
Paul June A. Domag|||Hi Paul
I think you cannot update an Identity column unless you set the identity insert on
ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
go
SET IDENTITY_INSERT t ON
UPDATE t SET myNewCol = ID ' Error Here
SET IDENTITY_INSERT t OFF
ALTER TABLE t DROP COLUMN ID
GO|||Hi,
Im currently using SQLServerCE and I doubt that this function/variable exists... Im trying to do this purely in SQL Statement...
Any Other Bright Ideas?
cheers,
Paul June A. Domag|||With a column set to Identity you cannot insert values unless your basically turn off the Identity feature, which is done as Eisa states.
If this doesn't work in SQL CE then we'll need to find a SQL CE Expert to answer this question.
Have you tried turning off the IDENTITY_INSERT? Also, make sure you insert GO statements after each line.
|||Hi,
Sad to say, it didn't work. I searched the BOL of SQLServerCE and the IDENTITY_INSERT switch isn't available. Guess, I'll have to find other means to solve this problem...

Thanks a lot Tyler Free and Eisa for your ideas and comments...
cheers,
Paul June A. Domag

How to cast a numeric database field to character

Hello.
I have a report and need to concatenate two numeric database fields, a month
and a year, into a string with a slash (/) between them and put it on the
report header.
How can I do this?
Thanks in advance,
MikeUse CStr(Month) & "/" & CStr(Year)
"MikeL" wrote:
> Hello.
> I have a report and need to concatenate two numeric database fields, a month
> and a year, into a string with a slash (/) between them and put it on the
> report header.
> How can I do this?
> Thanks in advance,
> Mike
>
>

how to call IFilters in ASP.NET

Hello friends i need to extract the content before its gets uploaded to
a BLOB Field in my database. Now I tried using OFFICE XP PIA'S. but due
to license issues and other technical difficulties and other reasons, i
had to abandon that idea, Now i have to use IFilters and I cant get any
idea as to where to start from and where to end. I have learned the
structure of the Ifilters but i am not able to implement well for my
web based application I urgently require help...
Thanks in advance
Kunal Ramesh Lalwani
Software Developer
Fahm Softwares
India
Filtdump -b Mydoc.doc > Mydoc.txt work well. You mgiht want to talk to your
MS sales folks about licensing issues about using this in production.
What sort of docs are you converting however?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kunallalwani@.gmail.com> wrote in message
news:1129281908.857040.187530@.g43g2000cwa.googlegr oups.com...
> Hello friends i need to extract the content before its gets uploaded to
> a BLOB Field in my database. Now I tried using OFFICE XP PIA'S. but due
> to license issues and other technical difficulties and other reasons, i
> had to abandon that idea, Now i have to use IFilters and I cant get any
> idea as to where to start from and where to end. I have learned the
> structure of the Ifilters but i am not able to implement well for my
> web based application I urgently require help...
> Thanks in advance
> Kunal Ramesh Lalwani
> Software Developer
> Fahm Softwares
> India
>
|||Hi Hilary,
I think that it is a command and as far as i am known with asp.net, it
wont let run any of the shell commands.
|||Yes you are correct - my mistake. What sort of a doc is it, and why do you
wish to render it as text?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kunal" <kunallalwani@.gmail.com> wrote in message
news:1129289278.890621.222690@.g49g2000cwa.googlegr oups.com...
> Hi Hilary,
> I think that it is a command and as far as i am known with asp.net, it
> wont let run any of the shell commands.
>
|||Actually we are uploading different kinds of doc and we need to store
its text also in the database for search purposes, hence finally the
path of the file upload will be dynamic ( Provided by the user of the
system), and i have confirmed that we cannot run any kind of shell
commands in web server, due to security issues and rights. So can you
help me with a work around..
|||No, there is no way that I know of to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"kunal" <kunallalwani@.gmail.com> wrote in message
news:1129294138.272494.170330@.g47g2000cwa.googlegr oups.com...
> Actually we are uploading different kinds of doc and we need to store
> its text also in the database for search purposes, hence finally the
> path of the file upload will be dynamic ( Provided by the user of the
> system), and i have confirmed that we cannot run any kind of shell
> commands in web server, due to security issues and rights. So can you
> help me with a work around..
>
|||Hi Hillary i found an interesting link i hope you would like it...
http://robgarrett.com/Blogs/software...01/11/442.aspx
Just check it out...

How to call and pass parameters to Subreport using AWC.RS.Extensio

I am using AWC.RS.Extension to develop both my main and subreport. The
subreport links to the main report through a main report field.
I can pass the xml to the main report by @.DataSource parameter and it works
fine. I also can call the subreport directly (as if it is the main report)
by passing the xml to it through @.DataSource as well.
I don't know how to code both the main and subreport together and pass
parameters to them through their @.DataSource!
Please help!I'm not familiar with exactly what you are doing here but it seems like
@.Datasource is a report parameter in both the main and subreport. So, put
the subreport in the main report. Right click on the subreport, properties,
parameters and set the parameter for the subreport to the parameter from the
main report. This allows you to chain together a parameter from the main and
pass it on to the subreport.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trancomp" <Trancomp@.discussions.microsoft.com> wrote in message
news:0918BA5D-7456-4B9E-980F-EAECF2A7407F@.microsoft.com...
> I am using AWC.RS.Extension to develop both my main and subreport. The
> subreport links to the main report through a main report field.
> I can pass the xml to the main report by @.DataSource parameter and it
works
> fine. I also can call the subreport directly (as if it is the main
report)
> by passing the xml to it through @.DataSource as well.
> I don't know how to code both the main and subreport together and pass
> parameters to them through their @.DataSource!
> Please help!
>|||Bruce, thanks for your reply, I already know how to do the report and
subreport the 'normal' way just like you described.
I am using the data extension from AWC.RS.Extension , I retrieve all the
data first in my code and convert the data to XML stream and then pass the
XML as datasource to the report and subreport. I know how to call the main
report and pass XML as datasource to it, I just don't know how to code the
call to the subreport and pass data to it as XML.
Thanks,
"Bruce L-C [MVP]" wrote:
> I'm not familiar with exactly what you are doing here but it seems like
> @.Datasource is a report parameter in both the main and subreport. So, put
> the subreport in the main report. Right click on the subreport, properties,
> parameters and set the parameter for the subreport to the parameter from the
> main report. This allows you to chain together a parameter from the main and
> pass it on to the subreport.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Trancomp" <Trancomp@.discussions.microsoft.com> wrote in message
> news:0918BA5D-7456-4B9E-980F-EAECF2A7407F@.microsoft.com...
> > I am using AWC.RS.Extension to develop both my main and subreport. The
> > subreport links to the main report through a main report field.
> >
> > I can pass the xml to the main report by @.DataSource parameter and it
> works
> > fine. I also can call the subreport directly (as if it is the main
> report)
> > by passing the xml to it through @.DataSource as well.
> >
> > I don't know how to code both the main and subreport together and pass
> > parameters to them through their @.DataSource!
> >
> > Please help!
> >
>
>

How to call a stored procedure from a function

Hi
The idea is to generate the sequence for some of the fields in the table.
Since the identity property sets the sequence to only one of the field in
the table, decided to have a UDF that would create a new sequence value
generated for the fields.
But when the function is called i get the error
"Only functions and extended stored procedures can be executed from within a
function." Please suggest
The below table will hold the names of the fields that would require the
sequence to be generated and the last value generated updated by the stored
procedure.
CREATE table SEQ_GENERATOR_TBL
( seq_name varchar(50) not null,
last_value bigint default 0 not null);
GO
insert into SEQ_GENERATOR_TBL(seq_name)
values('SEQ_IS_GLOBAL_IDENTIFIER');
CREATE function SEQ_GENERATOR_FUNC
(@.p_seq_name varchar(50))
RETURNS bigint
AS
BEGIN
DECLARE @.ret_next_value bigint
SET @.ret_next_value = (select last_value+1 as next_value
from SEQ_GENERATOR_TBL
WHERE seq_name = @.p_seq_name);
EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
RETURN @.ret_next_value;
END
GO
CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
@.p_seq_name varchar(50),
@.p_curr_value bigint
AS
BEGIN
BEGIN TRANSACTION;
UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
WHERE seq_name = @.p_seq_name;
COMMIT TRANSACTION;
RETURN;
END
GOJP
Do you expect the same sequence as the IDENTITY property is set to? Have you
considered using computed column?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:CD9841BA-3ED6-4140-BC3D-3588FC522226@.microsoft.com...
> Hi
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from within
> a
> function." Please suggest
> The below table will hold the names of the fields that would require the
> sequence to be generated and the last value generated updated by the
> stored
> procedure.
> CREATE table SEQ_GENERATOR_TBL
> ( seq_name varchar(50) not null,
> last_value bigint default 0 not null);
> GO
> insert into SEQ_GENERATOR_TBL(seq_name)
> values('SEQ_IS_GLOBAL_IDENTIFIER');
> CREATE function SEQ_GENERATOR_FUNC
> (@.p_seq_name varchar(50))
> RETURNS bigint
> AS
> BEGIN
> DECLARE @.ret_next_value bigint
> SET @.ret_next_value = (select last_value+1 as next_value
> from SEQ_GENERATOR_TBL
> WHERE seq_name = @.p_seq_name);
> EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
> RETURN @.ret_next_value;
> END
> GO
> CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
> @.p_seq_name varchar(50),
> @.p_curr_value bigint
> AS
> BEGIN
> BEGIN TRANSACTION;
> UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
> WHERE seq_name = @.p_seq_name;
> COMMIT TRANSACTION;
> RETURN;
> END
> GO
>|||Where do you want to show the data?
If you use reports do the numbering there
Madhivanan|||JP (JP@.discussions.microsoft.com) writes:
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from
> within a function." Please suggest
Rework and redesign. A function must not change database state, why
updates are not permitted, and neither calls to stored procedure as
they could do about anything.
Itzik Ben-Gan discussed a couple of solution in his column T-SQL Black
Belt in SQL Server Magazine a couple of issues back.
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

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

how to call a batch file from trigger

Hi,
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
Sunil
Hi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi,
I have a requirement to invoke a Java application and update the remote MySQ
L database, when a particular field in MSSQL Server 2000 is updated. my idea
is to write a trigger on AFTER update of field and from trigger invoke a ba
tch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger
or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote mysql instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to mysql is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100
,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a Java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run Java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

how to call a batch file from trigger

Hi
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run java application
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated
Thanks In advance
SunilHi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil

How to calculate category value - perhaps use subqueries?

Hello all - I am trying to create a view that will calculate a category fiel
d
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
--
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
--
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.On Tue, 4 Oct 2005 10:59:03 -0700, Hellman wrote:
(snip)
Hi Hellman,
I just posted a reply to your question in the .mseq group.
Please post your questions to one group only. And if you really feel
that a question fits two groups, use the crossposting ability of your
software to post one message to both groups at once, so that others will
see if there's already a reply in the other group, and we have all
reactions in one thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)