Showing posts with label created. Show all posts
Showing posts with label created. Show all posts

Wednesday, March 28, 2012

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job?
Thanks for your help.
Mikael
McA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job?
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job?
> Thanks for your help.
> Mikael
>
|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael
|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job'
Thanks for your help.
MikaelMcA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegroups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job'
Thanks for your help.
MikaelMcA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
--
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegroups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Wednesday, March 21, 2012

Problems with Nulls and SQL UDT's

I created a UDT in C# that is working fine except when I request rows where the type is null.

If I use SELECT Film.ToString() FROM ImageData it works fine, or any property they return Null as expected

but if I use SELECT Film FROM ImageData it produces the error below

An error occurred while executing batch. Error message is: Data is Null. This method or property cannot be called on Null values.

I looked through all the examples in C# and my code matches the pattern for nulls exactly. Can anyone shead some light on what attribute I may have to add or method I should override which is not documented.

This was a bug. Please see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125552

Cheers,

Tuesday, March 20, 2012

Problems with log file size in SQL Server 2005

I've dealing with a newly created database in SQL Server 2005 Developer
Edition.
I'm having many trouble with the size of the log file. This database is
being used in a Merge Replication scenario and I'm currently creating,
deleting publications quite often. Initially I had autogrowth enabled
for the log file (initial size of 20 Mb), with 100 MBytes of maximum
size.
With this configuration, two hours ago, I deleted a publication and I
received this error:
The transaction log for database 'X is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
I have now disabled autogrowth and have been able to delete the
publication, but I understand Merge Replication needs very much space
in the log file for all operations it performs.
Any advice or correct approach on this?
Thanks in advance.
Hi
Do BACKUP LOG file as it described in the BOL
Read 'recovery [SQL Server replication]' topic in the BOL
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141651209.039661.101840@.j33g2000cwa.googlegr oups.com...
> I've dealing with a newly created database in SQL Server 2005 Developer
> Edition.
> I'm having many trouble with the size of the log file. This database is
> being used in a Merge Replication scenario and I'm currently creating,
> deleting publications quite often. Initially I had autogrowth enabled
> for the log file (initial size of 20 Mb), with 100 MBytes of maximum
> size.
> With this configuration, two hours ago, I deleted a publication and I
> received this error:
> The transaction log for database 'X is full. To find out why space in
> the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
> I have now disabled autogrowth and have been able to delete the
> publication, but I understand Merge Replication needs very much space
> in the log file for all operations it performs.
> Any advice or correct approach on this?
> Thanks in advance.
>
|||I'll read it, but at a first glance, what is exactly what I should do?
A backup of the log file every 'X' minutes or something like this?
Thanks very much.
|||I tried doing a backup of the log before attempting to change the
publication's properties. This is what I read from BOL section you
suggested me. And I still have the log file with autogrowth disabled.
I tried to delete the current publication and.......same error
appears, that log file cannot be reused. So finally, in order to start
the Merge Agent again, I have to enable autogrowth for the log file,
which I don't like at all.
I need a concrete solution for this urgently!
|||Lonifasiko wrote:
> I tried doing a backup of the log before attempting to change the
> publication's properties. This is what I read from BOL section you
> suggested me. And I still have the log file with autogrowth disabled.
> I tried to delete the current publication and.......same error
> appears, that log file cannot be reused. So finally, in order to start
> the Merge Agent again, I have to enable autogrowth for the log file,
> which I don't like at all.
> I need a concrete solution for this urgently!
>
What's the problem in having autogrow enabled? If the logfile really
needs the space, it don't help you anywhere to disable the autogrow.
The only thing that can help you, is to backup the logfile (regularly)
like Uri suggested. When you backup the log, it will be truncated which
means you can re-use the space that's occupied by the data that has been
backed up.
If you have some long running transactions though, these can "prevent"
the backup command from freeing up the space and then the space will not
be freed up and the logfile will have to grow.
Regards
Steen
|||Hi,
I now see the unique way is backing up the log file and enabling again
autogrowth.
I've done a manteinance plan that backs up my log file once a day. This
way the log file will be reused? In the wizard, there is no any options
that lets me say "truncate transaction log". And I suppose log file
will continue growing........
When I select the database and then "Back up..." in Management Studio,
the unique log backup I can do is manual. I cannot program it as a DTS
or similar.
Therefore, it's correct the idea of executing a manteinance plan
regularly to prevent log files from being full?
Can you point me in the right direction (exactly what I must do) please
when performing log backups?
Thanks in advance.
|||Lonifasiko wrote:
> Hi,
> I now see the unique way is backing up the log file and enabling again
> autogrowth.
> I've done a manteinance plan that backs up my log file once a day. This
> way the log file will be reused? In the wizard, there is no any options
> that lets me say "truncate transaction log". And I suppose log file
> will continue growing........
> When I select the database and then "Back up..." in Management Studio,
> the unique log backup I can do is manual. I cannot program it as a DTS
> or similar.
> Therefore, it's correct the idea of executing a manteinance plan
> regularly to prevent log files from being full?
> Can you point me in the right direction (exactly what I must do) please
> when performing log backups?
> Thanks in advance.
>
I'm not using Maintenance Plans to backup my database and log files, so
I might not be the right to guide you on that. Instead of using a
maintenance plan though, you can write your on SQL statement that back
up the log (and database if you like) and then schedule this script to
run at a certain interval.
You can look up the syntax for backing up you database and log with SQL
in Books On Line.
The short version of the syntax is :
BACKUP LOG YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.bak'
and
BACKUP DATABASE YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.trn'
If you choose to backup using SQL, it might be a good idea to expand the
script to use a variable for the backupfilename so you can give it new
name every time you back it up -e.g. based on the date.
Regards
Steen
|||OK, I think I'll try next days with the manteinance plan I've written.
If it does not work this way, I'll try with doing it by script.
Sorry to be so repetitive, but back to my worrying log file size:
having log file maximum size limited to 200 MBytes and backing up the
log file regularly, can you surely tell me log file size will never go
over 200 MBytes?
That's exactly what I need. Thanks very much.
|||> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
If you set max size to 200MB, then SQL Server will not autogrow to more than that. But max size
doesn't influence how the file can be recycled. If you have a large transaction or don't do log
backup often enough and have the size limited, then you will end up in error 9002 any time you do a
modification and the log file is full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141806712.812195.315820@.j52g2000cwj.googlegr oups.com...
> OK, I think I'll try next days with the manteinance plan I've written.
> If it does not work this way, I'll try with doing it by script.
> Sorry to be so repetitive, but back to my worrying log file size:
> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
> That's exactly what I need. Thanks very much.
>

Problems with log file size in SQL Server 2005

I've dealing with a newly created database in SQL Server 2005 Developer
Edition.
I'm having many trouble with the size of the log file. This database is
being used in a Merge Replication scenario and I'm currently creating,
deleting publications quite often. Initially I had autogrowth enabled
for the log file (initial size of 20 Mb), with 100 MBytes of maximum
size.
With this configuration, two hours ago, I deleted a publication and I
received this error:
The transaction log for database 'X is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
I have now disabled autogrowth and have been able to delete the
publication, but I understand Merge Replication needs very much space
in the log file for all operations it performs.
Any advice or correct approach on this?
Thanks in advance.Hi
Do BACKUP LOG file as it described in the BOL
Read 'recovery [SQL Server replication]' topic in the BOL
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141651209.039661.101840@.j33g2000cwa.googlegroups.com...
> I've dealing with a newly created database in SQL Server 2005 Developer
> Edition.
> I'm having many trouble with the size of the log file. This database is
> being used in a Merge Replication scenario and I'm currently creating,
> deleting publications quite often. Initially I had autogrowth enabled
> for the log file (initial size of 20 Mb), with 100 MBytes of maximum
> size.
> With this configuration, two hours ago, I deleted a publication and I
> received this error:
> The transaction log for database 'X is full. To find out why space in
> the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
> I have now disabled autogrowth and have been able to delete the
> publication, but I understand Merge Replication needs very much space
> in the log file for all operations it performs.
> Any advice or correct approach on this?
> Thanks in advance.
>|||I'll read it, but at a first glance, what is exactly what I should do?
A backup of the log file every 'X' minutes or something like this?
Thanks very much.|||I tried doing a backup of the log before attempting to change the
publication's properties. This is what I read from BOL section you
suggested me. And I still have the log file with autogrowth disabled.
I tried to delete the current publication and.......same error
appears, that log file cannot be reused. So finally, in order to start
the Merge Agent again, I have to enable autogrowth for the log file,
which I don't like at all.
I need a concrete solution for this urgently!|||Lonifasiko wrote:
> I tried doing a backup of the log before attempting to change the
> publication's properties. This is what I read from BOL section you
> suggested me. And I still have the log file with autogrowth disabled.
> I tried to delete the current publication and.......same error
> appears, that log file cannot be reused. So finally, in order to start
> the Merge Agent again, I have to enable autogrowth for the log file,
> which I don't like at all.
> I need a concrete solution for this urgently!
>
What's the problem in having autogrow enabled? If the logfile really
needs the space, it don't help you anywhere to disable the autogrow.
The only thing that can help you, is to backup the logfile (regularly)
like Uri suggested. When you backup the log, it will be truncated which
means you can re-use the space that's occupied by the data that has been
backed up.
If you have some long running transactions though, these can "prevent"
the backup command from freeing up the space and then the space will not
be freed up and the logfile will have to grow.
Regards
Steen|||Hi,
I now see the unique way is backing up the log file and enabling again
autogrowth.
I've done a manteinance plan that backs up my log file once a day. This
way the log file will be reused? In the wizard, there is no any options
that lets me say "truncate transaction log". And I suppose log file
will continue growing........
When I select the database and then "Back up..." in Management Studio,
the unique log backup I can do is manual. I cannot program it as a DTS
or similar.
Therefore, it's correct the idea of executing a manteinance plan
regularly to prevent log files from being full?
Can you point me in the right direction (exactly what I must do) please
when performing log backups?
Thanks in advance.|||Lonifasiko wrote:
> Hi,
> I now see the unique way is backing up the log file and enabling again
> autogrowth.
> I've done a manteinance plan that backs up my log file once a day. This
> way the log file will be reused? In the wizard, there is no any options
> that lets me say "truncate transaction log". And I suppose log file
> will continue growing........
> When I select the database and then "Back up..." in Management Studio,
> the unique log backup I can do is manual. I cannot program it as a DTS
> or similar.
> Therefore, it's correct the idea of executing a manteinance plan
> regularly to prevent log files from being full?
> Can you point me in the right direction (exactly what I must do) please
> when performing log backups?
> Thanks in advance.
>
I'm not using Maintenance Plans to backup my database and log files, so
I might not be the right to guide you on that. Instead of using a
maintenance plan though, you can write your on SQL statement that back
up the log (and database if you like) and then schedule this script to
run at a certain interval.
You can look up the syntax for backing up you database and log with SQL
in Books On Line.
The short version of the syntax is :
BACKUP LOG YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.bak'
and
BACKUP DATABASE YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.trn'
If you choose to backup using SQL, it might be a good idea to expand the
script to use a variable for the backupfilename so you can give it new
name every time you back it up -e.g. based on the date.
Regards
Steen|||OK, I think I'll try next days with the manteinance plan I've written.
If it does not work this way, I'll try with doing it by script.
Sorry to be so repetitive, but back to my worrying log file size:
having log file maximum size limited to 200 MBytes and backing up the
log file regularly, can you surely tell me log file size will never go
over 200 MBytes?
That's exactly what I need. Thanks very much.|||> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
If you set max size to 200MB, then SQL Server will not autogrow to more than
that. But max size
doesn't influence how the file can be recycled. If you have a large transact
ion or don't do log
backup often enough and have the size limited, then you will end up in error
9002 any time you do a
modification and the log file is full.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141806712.812195.315820@.j52g2000cwj.googlegroups.com...
> OK, I think I'll try next days with the manteinance plan I've written.
> If it does not work this way, I'll try with doing it by script.
> Sorry to be so repetitive, but back to my worrying log file size:
> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
> That's exactly what I need. Thanks very much.
>

Problems with log file size in SQL Server 2005

I've dealing with a newly created database in SQL Server 2005 Developer
Edition.
I'm having many trouble with the size of the log file. This database is
being used in a Merge Replication scenario and I'm currently creating,
deleting publications quite often. Initially I had autogrowth enabled
for the log file (initial size of 20 Mb), with 100 MBytes of maximum
size.
With this configuration, two hours ago, I deleted a publication and I
received this error:
The transaction log for database 'X is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
I have now disabled autogrowth and have been able to delete the
publication, but I understand Merge Replication needs very much space
in the log file for all operations it performs.
Any advice or correct approach on this?
Thanks in advance.Hi
Do BACKUP LOG file as it described in the BOL
Read 'recovery [SQL Server replication]' topic in the BOL
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141651209.039661.101840@.j33g2000cwa.googlegroups.com...
> I've dealing with a newly created database in SQL Server 2005 Developer
> Edition.
> I'm having many trouble with the size of the log file. This database is
> being used in a Merge Replication scenario and I'm currently creating,
> deleting publications quite often. Initially I had autogrowth enabled
> for the log file (initial size of 20 Mb), with 100 MBytes of maximum
> size.
> With this configuration, two hours ago, I deleted a publication and I
> received this error:
> The transaction log for database 'X is full. To find out why space in
> the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Changed database context to 'X'. (Microsoft SQL Server, Error: 9002)
> I have now disabled autogrowth and have been able to delete the
> publication, but I understand Merge Replication needs very much space
> in the log file for all operations it performs.
> Any advice or correct approach on this?
> Thanks in advance.
>|||I'll read it, but at a first glance, what is exactly what I should do?
A backup of the log file every 'X' minutes or something like this?
Thanks very much.|||I tried doing a backup of the log before attempting to change the
publication's properties. This is what I read from BOL section you
suggested me. And I still have the log file with autogrowth disabled.
I tried to delete the current publication and.......same error
appears, that log file cannot be reused. So finally, in order to start
the Merge Agent again, I have to enable autogrowth for the log file,
which I don't like at all.
I need a concrete solution for this urgently!|||Lonifasiko wrote:
> I tried doing a backup of the log before attempting to change the
> publication's properties. This is what I read from BOL section you
> suggested me. And I still have the log file with autogrowth disabled.
> I tried to delete the current publication and.......same error
> appears, that log file cannot be reused. So finally, in order to start
> the Merge Agent again, I have to enable autogrowth for the log file,
> which I don't like at all.
> I need a concrete solution for this urgently!
>
What's the problem in having autogrow enabled? If the logfile really
needs the space, it don't help you anywhere to disable the autogrow.
The only thing that can help you, is to backup the logfile (regularly)
like Uri suggested. When you backup the log, it will be truncated which
means you can re-use the space that's occupied by the data that has been
backed up.
If you have some long running transactions though, these can "prevent"
the backup command from freeing up the space and then the space will not
be freed up and the logfile will have to grow.
Regards
Steen|||Hi,
I now see the unique way is backing up the log file and enabling again
autogrowth.
I've done a manteinance plan that backs up my log file once a day. This
way the log file will be reused? In the wizard, there is no any options
that lets me say "truncate transaction log". And I suppose log file
will continue growing........
When I select the database and then "Back up..." in Management Studio,
the unique log backup I can do is manual. I cannot program it as a DTS
or similar.
Therefore, it's correct the idea of executing a manteinance plan
regularly to prevent log files from being full?
Can you point me in the right direction (exactly what I must do) please
when performing log backups?
Thanks in advance.|||Lonifasiko wrote:
> Hi,
> I now see the unique way is backing up the log file and enabling again
> autogrowth.
> I've done a manteinance plan that backs up my log file once a day. This
> way the log file will be reused? In the wizard, there is no any options
> that lets me say "truncate transaction log". And I suppose log file
> will continue growing........
> When I select the database and then "Back up..." in Management Studio,
> the unique log backup I can do is manual. I cannot program it as a DTS
> or similar.
> Therefore, it's correct the idea of executing a manteinance plan
> regularly to prevent log files from being full?
> Can you point me in the right direction (exactly what I must do) please
> when performing log backups?
> Thanks in advance.
>
I'm not using Maintenance Plans to backup my database and log files, so
I might not be the right to guide you on that. Instead of using a
maintenance plan though, you can write your on SQL statement that back
up the log (and database if you like) and then schedule this script to
run at a certain interval.
You can look up the syntax for backing up you database and log with SQL
in Books On Line.
The short version of the syntax is :
BACKUP LOG YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.bak'
and
BACKUP DATABASE YourDatabaseName TO DISK ='The path to your backup
directory\YourBackupFileName.trn'
If you choose to backup using SQL, it might be a good idea to expand the
script to use a variable for the backupfilename so you can give it new
name every time you back it up -e.g. based on the date.
Regards
Steen|||OK, I think I'll try next days with the manteinance plan I've written.
If it does not work this way, I'll try with doing it by script.
Sorry to be so repetitive, but back to my worrying log file size:
having log file maximum size limited to 200 MBytes and backing up the
log file regularly, can you surely tell me log file size will never go
over 200 MBytes?
That's exactly what I need. Thanks very much.|||> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
If you set max size to 200MB, then SQL Server will not autogrow to more than that. But max size
doesn't influence how the file can be recycled. If you have a large transaction or don't do log
backup often enough and have the size limited, then you will end up in error 9002 any time you do a
modification and the log file is full.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1141806712.812195.315820@.j52g2000cwj.googlegroups.com...
> OK, I think I'll try next days with the manteinance plan I've written.
> If it does not work this way, I'll try with doing it by script.
> Sorry to be so repetitive, but back to my worrying log file size:
> having log file maximum size limited to 200 MBytes and backing up the
> log file regularly, can you surely tell me log file size will never go
> over 200 MBytes?
> That's exactly what I need. Thanks very much.
>

Problems with Linked servers between sql 2000 and sql 2005

Dear all

I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.

But when i try and run a sql statement on the sql 2000 side i get this error:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

If i try and run a sql statment on the sql server 2005 side the procedure works.

Has anyone experienced the same problem?Any advice?

Thank you so much for the help.

Dan

What is the SQL Statement that works/doesn't work?

Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?

|||

Also, if you can you post:

1 - Any errors that you see in the error log

2 - The commands that you used to create the linked server

3 - What type of authentication are you using? Integrated auth?

You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.

Thanks,

Il-Sung.

|||

The select statement i used is just a simple one:

Select * from MyLink.MyDatabase.dbo.Table1

This is working now. It had to do with the way sql server 05 was setup.

But i have a different problem.

When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.

Any suggestions as to why this might not work?

Sincerely

Dan

|||

Hi, Dan

This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...

Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

Good Luck!

Ming.

Problems with Linked servers between sql 2000 and sql 2005

Dear all

I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.

But when i try and run a sql statement on the sql 2000 side i get this error:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

If i try and run a sql statment on the sql server 2005 side the procedure works.

Has anyone experienced the same problem?Any advice?

Thank you so much for the help.

Dan

What is the SQL Statement that works/doesn't work?

Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?

|||

Also, if you can you post:

1 - Any errors that you see in the error log

2 - The commands that you used to create the linked server

3 - What type of authentication are you using? Integrated auth?

You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.

Thanks,

Il-Sung.

|||

The select statement i used is just a simple one:

Select * from MyLink.MyDatabase.dbo.Table1

This is working now. It had to do with the way sql server 05 was setup.

But i have a different problem.

When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.

Any suggestions as to why this might not work?

Sincerely

Dan

|||

Hi, Dan

This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...

Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

Good Luck!

Ming.

Problems with Linked servers between sql 2000 and sql 2005

Dear all

I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.

But when i try and run a sql statement on the sql 2000 side i get this error:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

If i try and run a sql statment on the sql server 2005 side the procedure works.

Has anyone experienced the same problem?Any advice?

Thank you so much for the help.

Dan

What is the SQL Statement that works/doesn't work?

Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?

|||

Also, if you can you post:

1 - Any errors that you see in the error log

2 - The commands that you used to create the linked server

3 - What type of authentication are you using? Integrated auth?

You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.

Thanks,

Il-Sung.

|||

The select statement i used is just a simple one:

Select * from MyLink.MyDatabase.dbo.Table1

This is working now. It had to do with the way sql server 05 was setup.

But i have a different problem.

When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.

Any suggestions as to why this might not work?

Sincerely

Dan

|||

Hi, Dan

This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...

Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

Good Luck!

Ming.

Problems with Linked servers between sql 2000 and sql 2005

Dear all

I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.

But when i try and run a sql statement on the sql 2000 side i get this error:

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.

If i try and run a swl statment on the sql server 2005 side the procedure works.

Has anyone experienced the same problem?Any advice?

Thank you so much for the help.

Dan

Try also posting to the SQL Server forum at http://forums.microsoft.com/msdn/showforum.aspx?forumid=87&siteid=1

|||

Thnax for the advice.I am just about to copy this post on there.

Much apreciated

Monday, March 12, 2012

Problems with installing SQL Server 2000

I am trying to install SQL Server 2000 from the MSDN DVDs on Windows Server 2003 Web Edition.

When I click on Install Database Server, the "Created with Demoshield" box comes up and then disappears. Nothing else happens. I have also tried to install by running from ENT\x86\SETUP\SETUPSQL.EXE. This gives me a message saying "Windows cannot open this program since it has been disabled".

I tried a Google search for that error message, and found two results, this at http://dbforums.com/arch/4/2003/9/925380 :

"hi,

Try installing as an administrator."

I am trying to install as an administrator.

The other was in German, but was only a list of error codes anyway.

Does anyone know how I can get this to work? I am supposed to have IIS6 up and running before my boss gets back on Monday, and I can't even get SQL installed, which is a prerequisite for IIS.

ANY help would be greatly appreciated.You are going to need Standard or Enterprise version of Windows 2003 Server... Web Edition is exactly that, a stripped down version for web server use, not for application server use (such as SQL Server)

http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp

only thing you can run on the Web Edition is MSDE

also... SQL Server shouldn't be a prerequisite for IIS... unless you have an application that requires IIS AND SQL Server, it is not a requirement for IIS alone|||Thanks, I will do that.

Friday, March 9, 2012

Problems with express

I recently downloaded 2005 express. I am able to create a new database fine. But how do I take an existing database (created elsewhere) and make changes to it? For example, I want to make a copy of my mdf on my website and manipulate the data and design on express, then copy it back to the ftp server. What is the easiest way?

If this is a limitation of 'Express', can I do this with 2005 'developers' editon that costs $49??

Help please.You can backup a database from SQL 2000, then restore the database onto SQL 2005, but I don't know of any way to go back reliably. The SQL 2005 database engine supports (in many cases demands) structure changes that SQL 2000 can't support.

-PatP|||Easiest way is to make changes to a database where it sits, without moving it all about. What sort of changes are you talking about? Is this in order to create a development environment of some sort?

Problems with executing ssis package by job

Hi All,
I have created ssis package and am trying to execute it using sql
server agent job.
The problem is, that the job failes to find or load the package. It
tries to run it from the server side and failes to find it. I don't
really understand how can I publish the package on the server from the
client side.
Thanks in advance.Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||amish wrote:
> Hi
> Have you saved your package in SQL Server ?
> from file menu in Microsoft Visual Studio select Save copy of package
> as and save your package in SQL Server.
> or
> Connect to intergation service in Management studio and import package.
>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Regards
> Amish Shah
Hi, thanks for replying,
Yes I tried both things, what I get is
Executed as user: CLUSTERNET\admin. The package could not be loaded.
The step failed.
Yours Qwert

Problems with ER in msde.

Hi, I have a database that was created on sql 2000 standard edition. The
system requiered to use remote servers so we decided to use msde.
Checking the information that is in the database we discover that there
is some information that is incorrectly.
For example, we have two tables: table1 and table1details, this has ER
defined between them but there is information that is on the
table1details that doesnt exists on table1.
This means that the ER is not working. We try to delete information in
QA but it wasn't posible.
Do somebody know if there is a problem with msde and ER created on the
database.
or do somebody know if ER is not respected when bulk insert data inserts
data into databases'
Thanks a lot for your help.
*** Sent via Developersdex http://www.examnotes.net ***Hi Maria
If the Foreign Key was created with the NOCHECK option existing data would
not be checked to see that it is valid
From BOL:
"WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or is not validated against a
newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified,
WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for
re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against
existing data, use WITH NOCHECK. This is not recommended except in rare
cases. The new constraint will be evaluated in all future updates. Any
constraint violations suppressed by WITH NOCHECK when the constraint is adde
d
may cause future updates to fail if they update rows with data that does not
comply with the constraint.
Constraints defined WITH NOCHECK are not considered by the query optimizer.
These constraints are ignored until all such constraints are re-enabled usin
g
ALTER TABLE table CHECK CONSTRAINT ALL."
It is also possible to load data with BULK INSERT/BCP that ignores constrain
ts
From BOL:
"CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the bulk
copy operation. By default, constraints are ignored. Note that the MAX_ERROR
S
option does not apply to constraint checking."
HTH
John
"MariaGuzman" wrote:

> Hi, I have a database that was created on sql 2000 standard edition. The
> system requiered to use remote servers so we decided to use msde.
> Checking the information that is in the database we discover that there
> is some information that is incorrectly.
> For example, we have two tables: table1 and table1details, this has ER
> defined between them but there is information that is on the
> table1details that doesn4t exists on table1.
> This means that the ER is not working. We try to delete information in
> QA but it wasn't posible.
> Do somebody know if there is a problem with msde and ER created on the
> database.
> or do somebody know if ER is not respected when bulk insert data inserts
> data into databases'
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Problems with DTS package

I am working on sql server 2000.

here is my question.

How can I update a table from the data in a text file.

I created a DTS package to do this job. I am able to insert the data from text file to a table perfectly. But What I want is to update the existing row instead of inserting a new row.

Here is sample data:

text file:

col1 col2

1 A

2 B

data in the table:

Col1 Col2

1 C

2 B

Now I want to update the first row for which the col2 is not matching. How can i do this. I wrote a activex script (for transformation) to update but it alwasy trys to insert rather than update, Here is the script.

Function Main()
if DTSDestination("Col1") = DTSSource("Col001") and DTSDestination("Col002") <> DTSSource("Col002") then
update DTSDestination
set DTSDestination("Col002") = DTSSource("Col002")
end if
Main = DTSTransformStat_OK
End Function

Thanks in advance

The script code that you posted should actually error out. It contains VB code with TSQL code. In any case, it looks like you should use a staging table approach and simply insert all the rows into server first & then use DMLs. This is the most efficient way. If you do it from the DTS package you are doing row-by-row processing and performance will suffer. Below are steps

1. Create staging table on server that contains same structure as destination table

2. Create a SP that does the following:

begin

update your_table

set Col2 = s.Col2, Col3 = s.Col3, ...

from staging_table as s

where s.Col1 = your_table.Col1

insert into your_table (Col1, Col2, Col3, ....)

select s.Col1, s.Col2, s.Col3, ...

from staging_table as s

where not exists(

select * from your_table as t

where t.key_col1 = s.key_col1

and ....

)

truncate staging_table

end

Now, modify your DTS package to do the following:

1. Bulk insert into the staging table

2. Call SP to update data from staging to main table

|||Thanks a lot for your advice

Saturday, February 25, 2012

Problems with atached database ...

Hi NG, first sorry for my bad english.
I have created a VB project with uses al local SQL2005Express database and a public SQL2005Express database over the net.
I have added the public server using a stored procedure via 'sp_addlinkedserver' and the public server is set to mixed auth mode.
If I run my project in VisualStudio, everthing works fine, no problems to use functions which get theire data from the public server.
But if i use ClickOnce to instal my application on another pc, first everythink looks fine, sqlexpress was instaled and the application too.
If I start the application I was able to use all functions which connect to the local, attached database, but if I call a function which use the public server over the 'sp_addlinkedserver', I get the following error messsage :

' could not log in user ' ',no trusted sqlconnection for this user ' (sorry, self-translated)

but if I check the connectionstring of the addlinked server he is correct with both, username and pw.
Please could someone be so nice and explain it to me ?
Sincerely,
Oliver Petri.

hi Oliver,

SQLExpress installs by default allowing trusted connections only.. you can modify that behaviour both at install time, providing the SECURITYMODE=SQL parameter to the install wizard or enabling the relative setting if in UI mode, or later, at "run time".. you can use SQL Server Management Studio Express for that.. select the server's node, rx click and access the server's properties.. in the "security" page, modify the "server authentication" to "SQL Server and Windows authentication mode".. restart the instance..

regards

|||Thx for your answer, but that is not the problem. As I post '....the public server is set to mixed auth mode.' I have done this for both servers.
Sincerely,
Oliver Petri.

Problems with accessing database remotely

I'm writing an web app in VS2005.

I've created a product.mdf database through VS2005's Server Explorer (to my knowledge, I'm not using Sql Express). (Side question. The .mdf files are a stand alone xml structure used as a database, correct?).

When I execute locally, everything works great. But when I moved my code to where it is being hosted (remote computer). It's throwing the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This is my connection string:

<addname="MyConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Product.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

What is my problem?

There is no SQL Server 2005 Express running on the remote server.

Jos

|||

Actually, you ARE using SQL Express. The Visual Studio environment does a great job in hiding this - but behind the scenes a SQL Express database engine is running on your local machine (you can see it in the Services.msc explorer). Your web application is using the web.config connection string tofind this SQL Express database.

As the previous reply says, the error message is due to the application trying to find a SQL Express database engine running on the remote computer and serving a database called Product.mdf.

The solution to your problem may be to install SQL Express on the remote computer. You will then need to copy (attach) the Product.mdf database to the remote database server.

However, if your "remote computer" is actually a hosting provider - they may not (and most don't) support SQL Express - and may insist that you use SQL Server Enterprise instead, which is a far more robust option anyway.

|||

Why I got the error message?

The error message you got was due to the using of SQL Express in your local (development) machine.

Ok, how to solve this issue?

It is easy. Eiteher use SQL Express on your production (e.g. hosting server) [NOT Recommended] or do the following:

Attach your .mdf file to a database (e.g. new database) in the sql server of the hosting company.
Change your ConnectionString to be as following:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>

YourSqlServerName: Type the name of the sql server as provided by the hosting company.

MyDatabase: The database you are using which reside in the hosting company sql server.

Note: The above ConnectionString is used with "Windows Authentication", for the "Mixed Mode" use the following ConnectionString:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;User ID=MyUserID; Password:MyStrongPassword"providerName="System.Data.SqlClient"/>

problems whit SQLServer and JDeveloper

I'm trying to open a new DB conection in JDeveloper 9. I've created a DB in SQL Server 2000 with one user called mta_ds.
When i look in JDeveloper the connection show me 3 users for that DB (dbo, guest and mta_ds), but with the only user that i can see the tables of the db is with dbo. both dbo and mta_ds had the same permisions and roles. So.. why i can't see the tables for mta_ds?? i've used one driver open source: Driver name = net.sourceforge.jtds.jdbc.Driver
url = jdbc:jtds://Tauros:1433/DB_Visa_MoraTemprana
Could be this the problem?

Pleaseeee Somebody help meeee!!!
THANK's A LOT!!!!Run SP_HELPROTECT from query analyzer and see the privileges for individual roles/users.

Monday, February 20, 2012

Problems using Linked Server.

Hi, I have a process that execute a query that is created on a linked
server. This process is scheduled in a job. Every Time the job runs
report this error:
Executed as user: domain\servicesaccount. OLE DB provider 'SQLOLEDB'
reported an error. [SQLSTATE 42000] (Error 7399) OLE DB error trace
[OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned
0x80004005: ]. [SQLSTATE 01000] (Error 7300) [SQLSTATE 01000] (Error
7312). The step failed.
Do some body know how can I solve the problem?
Thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***Error 0x80004005 is access denied so you'd want to start by
checking the permissions for the login accounts used in the
security mapping for the linked server.
-Sue
On Mon, 23 Jan 2006 07:25:51 -0800, MariaGuzman
<marisa@.devdex.com> wrote:
>Hi, I have a process that execute a query that is created on a linked
>server. This process is scheduled in a job. Every Time the job runs
>report this error:
>Executed as user: domain\servicesaccount. OLE DB provider 'SQLOLEDB'
>reported an error. [SQLSTATE 42000] (Error 7399) OLE DB error trace
>[OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned
>0x80004005: ]. [SQLSTATE 01000] (Error 7300) [SQLSTATE 01000] (Error
>7312). The step failed.
>Do some body know how can I solve the problem?
>Thanks a lot for your help.
>*** Sent via Developersdex http://www.developersdex.com ***