change default database path

Hi:

I'm usingSQL SERVER 2005 EXPRESS

My server name isSERVER1\SQLEXPRESS

When I create a new data base on my server it saves at

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

How Can I change this default path?

Thanks!!

Hi, you can take a look at theStep 3: Point your web.config file at the new SQL Databasesection in this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Change Column Size

Hi

I am using one Publisher with many subscribers for Merge replication of a database.
I wanted to change size one column in a table. So I added a dummy column (through Filter column).
Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column.
On subscriber A, replication is OK. On subscriber B, I get the following message.
--
Initializing
Connecting to Publisher 'REPLIC'
Retrieving publication information
Retrieving subscription information
A column was added to or dropped from the replicated table.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
A column was added to or dropped from the replicated table.
--

I made a new snapshot, reintialized subscription with upload, and tried to sychronize again.
But same message.

Question 1. Is there anyway to make replication?
Question 2. If not, can I at least upload data changes before dropping and making new publication?

Imtaar

is this sql 2005 or sql 2000?

change collation of a type of text column

Hi!
I have to change a complate database collation. After I had changed the
database collation I go through the tables and its columns and make a DDL
command on collated columns :
"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
It work on varchar and char columns but I receive an error message in case
of text columns :
"Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'Ic_DbFields' because it is 'text'."
I use OLEDB.
Enterprise Manager can change the collation on text columns.
What can I do?
I nedd to use SQL-DMO?
thanks for any help :
ImreCheck if you have a full-text index using that column. if so, then you have
to remove it before changing the collation.
AMB
"Imre Ament" wrote:

> Hi!
> I have to change a complate database collation. After I had changed the
> database collation I go through the tables and its columns and make a DDL
> command on collated columns :
> "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
> It work on varchar and char columns but I receive an error message in case
> of text columns :
> "Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'Ic_DbFields' because it is 'text'."
> I use OLEDB.
> Enterprise Manager can change the collation on text columns.
> What can I do?
> I nedd to use SQL-DMO?
> thanks for any help :
> Imre|||I don't think you can alter collation for blob columns.
One option can be to add a new column with desired collation, update the new
column with the value
of the old column, drop the old column and rename the new column. Column ord
er will not be
preserved, of course.
Another option is to create a new table. My guess is that this is what Enter
prise Manager does.
Imre: pressing "save change script" will show you how EM does this. I doubt
it is exposed in DMO, as
the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo
u could have a look, of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
> Check if you have a full-text index using that column. if so, then you hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||You are right. Thanks for the comment.
AMB
"Tibor Karaszi" wrote:

> I don't think you can alter collation for blob columns.
> One option can be to add a new column with desired collation, update the n
ew column with the value
> of the old column, drop the old column and rename the new column. Column o
rder will not be
> preserved, of course.
> Another option is to create a new table. My guess is that this is what Ent
erprise Manager does.
> Imre: pressing "save change script" will show you how EM does this. I doub
t it is exposed in DMO, as
> the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But
you could have a look, of
> course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

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 an exception from a Web service task

Hi!

I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.

But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.

Regards

Geir F

I forget to tell that I want the error description text from the exception that is thrown from the web service task because I want to log the errors. So what I hope is to have a script task that executes when web service fails and to write some code to catch the error description.

Current status on this is that the script task executes when the WS fails (red arrow). Nice. The web service returns an object, say TestOutput variable. When the WS succeeds, I can convert the object into a integer datatype and catch the value. But when it fails, I don't know to catch the error description. When executing the package, I can see the error description on th progress tab, so there must be a way for me to get the error too :-)

Regards

GF

catchin errors occured in sql server with DELPHI

hi
i want to know how can I catch an error occured in sql server with DELPHI
when an error occurs in sql server the number of error and description of th
e
error is sent to any programming language.
for example I want to change the message in my application when the error
occures?
but I don't knoe how?
is there any espesial EVENT HANDLER?Are you asking how to do this in Delphi? If so, you are probably better off
posting this in a Delphi forum.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pooyan_pdm" <pooyanpdm@.discussions.microsoft.com> wrote in message
news:C64FB3F0-1327-4B22-AC22-7F367E9EEE33@.microsoft.com...
> hi
> i want to know how can I catch an error occured in sql server with DELPHI
> when an error occurs in sql server the number of error and description of
> the
> error is sent to any programming language.
> for example I want to change the message in my application when the error
> occures?
> but I don't knoe how?
> is there any espesial EVENT HANDLER?
>