I have an article with 70,000 rows in it. I need to run an update script
from the subscriber that will update all 70,00 rows. Is there anyway I can
prevent these changes from being replicated? I realize I can drop the
trigger so the rows are not added to msmerge_contents. I'm looking for a
more automated approach since my subscribers are running MSDE.
TIA
Tina
do a sync with your merge agent, make sure it is not running in continuous
mode. Then when it has completed and shut down, set the -ExchangeType
parameter to Download, then restart your merge agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:OGOALZ56EHA.936@.TK2MSFTNGP12.phx.gbl...
> I have an article with 70,000 rows in it. I need to run an update
script
> from the subscriber that will update all 70,00 rows. Is there anyway I
can
> prevent these changes from being replicated? I realize I can drop the
> trigger so the rows are not added to msmerge_contents. I'm looking for a
> more automated approach since my subscribers are running MSDE.
> TIA
> Tina
>
|||Thanks Hilary, I'll give it a shot.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fqa4$B7EHA.1564@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> do a sync with your merge agent, make sure it is not running in continuous
> mode. Then when it has completed and shut down, set the -ExchangeType
> parameter to Download, then restart your merge agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:OGOALZ56EHA.936@.TK2MSFTNGP12.phx.gbl...
> script
> can
a
>
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Friday, March 23, 2012
Monday, March 19, 2012
Manually run Subscription
I have gathered it is pretty tough to manually run a subscription. I have a
shared schedule with about 50 jobs attached. I need to rerun about half of
them. What is the best way to rerun specific reports in a shared schedule?
I am running SRS 2005 Standard.
Also, are there any fixs to this in a future releases?
Thanks.I'm not sure about shared schedules...
But this is what I do if I need to manually run a subscription. Get the
SubscriptionID and insert it in the WHERE Clause
SELECT 'EXEC msdb..sp_start_job @.job_name=''' + CONVERT(varchar(100),
scheduleid) + ''''
FROM ReportSchedule WITH(NOLOCK)
INNER JOIN Subscriptions WITH(NOLOCK) ON
Subscriptions.SubscriptionId = ReportSchedule.SubscriptionId
WHERE Subscriptions.Subscriptionid = [INSERT YOUR ID HERE]
Then run that resulting EXEC statement that gets outputted. It will start
the SQL Agent job that is associated for that subscription.
"Brian Shannon" wrote:
> I have gathered it is pretty tough to manually run a subscription. I have a
> shared schedule with about 50 jobs attached. I need to rerun about half of
> them. What is the best way to rerun specific reports in a shared schedule?
> I am running SRS 2005 Standard.
> Also, are there any fixs to this in a future releases?
> Thanks.
>
>
shared schedule with about 50 jobs attached. I need to rerun about half of
them. What is the best way to rerun specific reports in a shared schedule?
I am running SRS 2005 Standard.
Also, are there any fixs to this in a future releases?
Thanks.I'm not sure about shared schedules...
But this is what I do if I need to manually run a subscription. Get the
SubscriptionID and insert it in the WHERE Clause
SELECT 'EXEC msdb..sp_start_job @.job_name=''' + CONVERT(varchar(100),
scheduleid) + ''''
FROM ReportSchedule WITH(NOLOCK)
INNER JOIN Subscriptions WITH(NOLOCK) ON
Subscriptions.SubscriptionId = ReportSchedule.SubscriptionId
WHERE Subscriptions.Subscriptionid = [INSERT YOUR ID HERE]
Then run that resulting EXEC statement that gets outputted. It will start
the SQL Agent job that is associated for that subscription.
"Brian Shannon" wrote:
> I have gathered it is pretty tough to manually run a subscription. I have a
> shared schedule with about 50 jobs attached. I need to rerun about half of
> them. What is the best way to rerun specific reports in a shared schedule?
> I am running SRS 2005 Standard.
> Also, are there any fixs to this in a future releases?
> Thanks.
>
>
Manually Grow database files
I'm looking for a way to manually perform the same functionality that
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-Will
Will
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will
|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will
|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-Will
Will
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will
|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will
|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will
Manually Grow database files
I'm looking for a way to manually perform the same functionality that
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-WillWill
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
There's noting inherently "risky" with ALTER DATABASE.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
Imagine you are working in the SQL Server dev team for MS. You have implemented code that expend the
size of a database file. You now have to determine the TSQL command which will invoke your command.
Should it be some DBCC command? Or some ALTER DATABASE? What I'm trying to say is that the command
is just an interface to the functionality within SQL Server. MS are in fact moving away from DBCC
and system stored procedures in favor of DDL.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size?
No, it is the same functionality in the engine in the end. It will actually be perceieved quicker
because you don't have one or several persons waiting for the grow (because you grow before it is
full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-WillWill
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
There's noting inherently "risky" with ALTER DATABASE.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
Imagine you are working in the SQL Server dev team for MS. You have implemented code that expend the
size of a database file. You now have to determine the TSQL command which will invoke your command.
Should it be some DBCC command? Or some ALTER DATABASE? What I'm trying to say is that the command
is just an interface to the functionality within SQL Server. MS are in fact moving away from DBCC
and system stored procedures in favor of DDL.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size?
No, it is the same functionality in the engine in the end. It will actually be perceieved quicker
because you don't have one or several persons waiting for the grow (because you grow before it is
full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang
Manual Installation of Printing ActiveX Control
I have a issue. My users that run RS Reports do not have administrative priviledges to install the ActiveX control required to use the Print function for an RS Report. Currently, the only way they can print the report is to export the report to another format before printing.
Is there a way to manually install the ActiveX Control. I need to install the control on about 200 users, possibly through network logins scripts.
Any help would be appreciated.
Thanks.
did you ever resolve this issue? we're running into the same.
any help is appreciated.
Monday, March 12, 2012
Manual backup not starting
I am trying to run a manual backup of a database in SQL Server 2000, as I've
done many times in the past. This database is over 4 Gb. The backup says
it is in progress, but I never see any "progress". It has been sitting
like this for over an hour, and the machine is only using 1% of it's
resources and has plenty of free memory.
Does anyone have any ideas why this is happening? Thanks.Hi,
Can you please try the below command from Query analyzer to backup the
database.
Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
Replace the dbname and drive letter based on your requirement.
Thanks
Hari
MCDBA
"Bill" <WBeatty@.Prodigy.Net> wrote in message
news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I am trying to run a manual backup of a database in SQL Server 2000, as
I've
> done many times in the past. This database is over 4 Gb. The backup says
> it is in progress, but I never see any "progress". It has been sitting
> like this for over an hour, and the machine is only using 1% of it's
> resources and has plenty of free memory.
> Does anyone have any ideas why this is happening? Thanks.
>|||Hari,
OK, I am currently running the query to backup. It has been running for 7
minutes so far. I'll let you know when I see any messages or it completes.
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Just like the Energizer Bunny....still going and going and going.... 1
hour 6 minutes, still not responding in Query Analyzer. I've never run
across this problem. I've backed this database up many times before with no
problem.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Well....I couldn't take it any more. I rebooted the server, and now
everything is working fine....who knows! Now the backup completed in 2
minutes. I couldn't find any other jobs that were interfering, everything
looked great, but the goofy thing wouldn't back up.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||You may have had an old log or full backup that was blocking the first
backup. That happens sometimes. sp_who2 will show any blocking SPIDs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bill" <wbeatty@.prodigy.net> wrote in message
news:eA21dDw%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Well....I couldn't take it any more. I rebooted the server, and now
> everything is working fine....who knows! Now the backup completed in 2
> minutes. I couldn't find any other jobs that were interfering, everything
> looked great, but the goofy thing wouldn't back up.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
stats=10
as
> says
sitting
>
done many times in the past. This database is over 4 Gb. The backup says
it is in progress, but I never see any "progress". It has been sitting
like this for over an hour, and the machine is only using 1% of it's
resources and has plenty of free memory.
Does anyone have any ideas why this is happening? Thanks.Hi,
Can you please try the below command from Query analyzer to backup the
database.
Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
Replace the dbname and drive letter based on your requirement.
Thanks
Hari
MCDBA
"Bill" <WBeatty@.Prodigy.Net> wrote in message
news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I am trying to run a manual backup of a database in SQL Server 2000, as
I've
> done many times in the past. This database is over 4 Gb. The backup says
> it is in progress, but I never see any "progress". It has been sitting
> like this for over an hour, and the machine is only using 1% of it's
> resources and has plenty of free memory.
> Does anyone have any ideas why this is happening? Thanks.
>|||Hari,
OK, I am currently running the query to backup. It has been running for 7
minutes so far. I'll let you know when I see any messages or it completes.
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Just like the Energizer Bunny....still going and going and going.... 1
hour 6 minutes, still not responding in Query Analyzer. I've never run
across this problem. I've backed this database up many times before with no
problem.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||Well....I couldn't take it any more. I rebooted the server, and now
everything is working fine....who knows! Now the backup completed in 2
minutes. I couldn't find any other jobs that were interfering, everything
looked great, but the goofy thing wouldn't back up.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I've
says
>|||You may have had an old log or full backup that was blocking the first
backup. That happens sometimes. sp_who2 will show any blocking SPIDs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bill" <wbeatty@.prodigy.net> wrote in message
news:eA21dDw%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Well....I couldn't take it any more. I rebooted the server, and now
> everything is working fine....who knows! Now the backup completed in 2
> minutes. I couldn't find any other jobs that were interfering, everything
> looked great, but the goofy thing wouldn't back up.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
stats=10
as
> says
sitting
>
Manual backup not starting
I am trying to run a manual backup of a database in SQL Server 2000, as I've
done many times in the past. This database is over 4 Gb. The backup says
it is in progress, but I never see any "progress". It has been sitting
like this for over an hour, and the machine is only using 1% of it's
resources and has plenty of free memory.
Does anyone have any ideas why this is happening? Thanks.Hi,
Can you please try the below command from Query analyzer to backup the
database.
Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
Replace the dbname and drive letter based on your requirement.
Thanks
Hari
MCDBA
"Bill" <WBeatty@.Prodigy.Net> wrote in message
news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I am trying to run a manual backup of a database in SQL Server 2000, as
I've
> done many times in the past. This database is over 4 Gb. The backup says
> it is in progress, but I never see any "progress". It has been sitting
> like this for over an hour, and the machine is only using 1% of it's
> resources and has plenty of free memory.
> Does anyone have any ideas why this is happening? Thanks.
>|||Hari,
OK, I am currently running the query to backup. It has been running for 7
minutes so far. I'll let you know when I see any messages or it completes.
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||Just like the Energizer Bunny....still going and going and going.... 1
hour 6 minutes, still not responding in Query Analyzer. I've never run
across this problem. I've backed this database up many times before with no
problem.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||Well....I couldn't take it any more. I rebooted the server, and now
everything is working fine....who knows! Now the backup completed in 2
minutes. I couldn't find any other jobs that were interfering, everything
looked great, but the goofy thing wouldn't back up.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||You may have had an old log or full backup that was blocking the first
backup. That happens sometimes. sp_who2 will show any blocking SPIDs.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bill" <wbeatty@.prodigy.net> wrote in message
news:eA21dDw%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Well....I couldn't take it any more. I rebooted the server, and now
> everything is working fine....who knows! Now the backup completed in 2
> minutes. I couldn't find any other jobs that were interfering, everything
> looked great, but the goofy thing wouldn't back up.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Can you please try the below command from Query analyzer to backup the
> > database.
> >
> > Backup database dbname to disk='c:\backup\dbname.bak' with init,
stats=10
> >
> > Replace the dbname and drive letter based on your requirement.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > "Bill" <WBeatty@.Prodigy.Net> wrote in message
> > news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > > I am trying to run a manual backup of a database in SQL Server 2000,
as
> > I've
> > > done many times in the past. This database is over 4 Gb. The backup
> says
> > > it is in progress, but I never see any "progress". It has been
sitting
> > > like this for over an hour, and the machine is only using 1% of it's
> > > resources and has plenty of free memory.
> > >
> > > Does anyone have any ideas why this is happening? Thanks.
> > >
> > >
> >
> >
>
done many times in the past. This database is over 4 Gb. The backup says
it is in progress, but I never see any "progress". It has been sitting
like this for over an hour, and the machine is only using 1% of it's
resources and has plenty of free memory.
Does anyone have any ideas why this is happening? Thanks.Hi,
Can you please try the below command from Query analyzer to backup the
database.
Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
Replace the dbname and drive letter based on your requirement.
Thanks
Hari
MCDBA
"Bill" <WBeatty@.Prodigy.Net> wrote in message
news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> I am trying to run a manual backup of a database in SQL Server 2000, as
I've
> done many times in the past. This database is over 4 Gb. The backup says
> it is in progress, but I never see any "progress". It has been sitting
> like this for over an hour, and the machine is only using 1% of it's
> resources and has plenty of free memory.
> Does anyone have any ideas why this is happening? Thanks.
>|||Hari,
OK, I am currently running the query to backup. It has been running for 7
minutes so far. I'll let you know when I see any messages or it completes.
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||Just like the Energizer Bunny....still going and going and going.... 1
hour 6 minutes, still not responding in Query Analyzer. I've never run
across this problem. I've backed this database up many times before with no
problem.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||Well....I couldn't take it any more. I rebooted the server, and now
everything is working fine....who knows! Now the backup completed in 2
minutes. I couldn't find any other jobs that were interfering, everything
looked great, but the goofy thing wouldn't back up.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you please try the below command from Query analyzer to backup the
> database.
> Backup database dbname to disk='c:\backup\dbname.bak' with init, stats=10
> Replace the dbname and drive letter based on your requirement.
> Thanks
> Hari
> MCDBA
>
>
> "Bill" <WBeatty@.Prodigy.Net> wrote in message
> news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > I am trying to run a manual backup of a database in SQL Server 2000, as
> I've
> > done many times in the past. This database is over 4 Gb. The backup
says
> > it is in progress, but I never see any "progress". It has been sitting
> > like this for over an hour, and the machine is only using 1% of it's
> > resources and has plenty of free memory.
> >
> > Does anyone have any ideas why this is happening? Thanks.
> >
> >
>|||You may have had an old log or full backup that was blocking the first
backup. That happens sometimes. sp_who2 will show any blocking SPIDs.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Bill" <wbeatty@.prodigy.net> wrote in message
news:eA21dDw%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Well....I couldn't take it any more. I rebooted the server, and now
> everything is working fine....who knows! Now the backup completed in 2
> minutes. I couldn't find any other jobs that were interfering, everything
> looked great, but the goofy thing wouldn't back up.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eY0kBPv#DHA.1844@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Can you please try the below command from Query analyzer to backup the
> > database.
> >
> > Backup database dbname to disk='c:\backup\dbname.bak' with init,
stats=10
> >
> > Replace the dbname and drive letter based on your requirement.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > "Bill" <WBeatty@.Prodigy.Net> wrote in message
> > news:eeRyb8u#DHA.3496@.TK2MSFTNGP10.phx.gbl...
> > > I am trying to run a manual backup of a database in SQL Server 2000,
as
> > I've
> > > done many times in the past. This database is over 4 Gb. The backup
> says
> > > it is in progress, but I never see any "progress". It has been
sitting
> > > like this for over an hour, and the machine is only using 1% of it's
> > > resources and has plenty of free memory.
> > >
> > > Does anyone have any ideas why this is happening? Thanks.
> > >
> > >
> >
> >
>
Manipulating Text,nText data types filed in tsql
I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..
EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO
As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".
DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566
So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.
Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.
I would appreciate if any body respond to this.
Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:
sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.
You can even use output parameters by using INSERT-EXEC, as in this example:
CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.
So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:
http://www.sommarskog.se/dynamic_sql.html
|||Thanks, I had to break the query into pieces to get this working.Friday, March 9, 2012
Managing transactions on dual cores
Hi,
I have a question.
Suppose two "Insert" or "update" instructions are run on the same table ..(in case of "Update" same row ...column).. at the same time....
can the two operations run at the same time on dual core/ quad core processors?
What is the role of dual/quad cores in such case..?
Since these processors can run two or four threads simultaneously... how will the sql server execute the two instructions ie (parallel or interleaved).
Or the Insert , Update or Delete instructions are run one after another just using "interleaving"?
I hope that you understand what i am asking...!
or i just confused you.
With regards,
Girish Pawar
I thinking you're misunderstanding parellelism versus sql transaction. When you start a transaction, the system can use 1 or more thread to perform the work. Depending on the what resource (rows, pages, or table) get locked the second transaction will have to wait until the first transaction release the lock. This works the same whether the system has 1 or more core.|||what if i say ... WITH NO LOCK
on my stored procedure..?
Are all the commands in sql server executed in a queue...?
so that the machine being multicore has no affect what so ever?
|||
At very very low level it is not possible, all the actions in sql server is a log based. Which ever first open the log file will write the contents there. There is a very minimal lock should be there for any actions.
Note:
If you try with date value to verify this action you may get wrong info.
Check this thread for more details.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630363&SiteID=1
Wednesday, March 7, 2012
Managing Jobs - server roles
Hello,
What server/permission role can i assign a user so he can use SQL
agent/management and see that jobs have run, created etc without assigning
him system admin. The most restrictive yet allowing him to see the jobs
thanksHi,
A user who is not a member of the sysadmin fixed role can use sp_help_job to
view only the jobs he/she owns.
Thanks
Hari
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:4DAEB3D9-4FA6-41C0-BCF3-E0795BFC1031@.microsoft.com...
> Hello,
> What server/permission role can i assign a user so he can use SQL
> agent/management and see that jobs have run, created etc without assigning
> him system admin. The most restrictive yet allowing him to see the jobs
> thanks
What server/permission role can i assign a user so he can use SQL
agent/management and see that jobs have run, created etc without assigning
him system admin. The most restrictive yet allowing him to see the jobs
thanksHi,
A user who is not a member of the sysadmin fixed role can use sp_help_job to
view only the jobs he/she owns.
Thanks
Hari
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:4DAEB3D9-4FA6-41C0-BCF3-E0795BFC1031@.microsoft.com...
> Hello,
> What server/permission role can i assign a user so he can use SQL
> agent/management and see that jobs have run, created etc without assigning
> him system admin. The most restrictive yet allowing him to see the jobs
> thanks
Managing Excel execution through DTS
We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
Make a connection to database server2
Execute a Process Task to execute EXCEL.EXE that is installed on server3
Pass parameters through the Process Task direct EXCEL.EXE to open a .xls file on server4
When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.
The questions are:
1. Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?
2. And how do we control the security context that executes Excel through this use of automation?
3. Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?Yikes! That is convoluted. Is there any particular reason you are doing things this way?|||Dude -
Are you into masochism? Enjoy sleepless nights?
Seriously - just pull the excel spreadsheet from the server you are reading from, open it on the machine with the dts package, do the manipulation & then copy the final result to wherever its going....
Dont make it any more complicated than it has to be - K.I.S.S.
Simple = Robust.
Cheers,|||Why do you have 4 servers in this scenario when you just want to modify some data on another server than where the DTS-package is situated?
What kind of data does this file contain, and why does it have to be updated via Excel?
Make a connection to database server2
Execute a Process Task to execute EXCEL.EXE that is installed on server3
Pass parameters through the Process Task direct EXCEL.EXE to open a .xls file on server4
When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.
The questions are:
1. Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?
2. And how do we control the security context that executes Excel through this use of automation?
3. Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?Yikes! That is convoluted. Is there any particular reason you are doing things this way?|||Dude -
Are you into masochism? Enjoy sleepless nights?
Seriously - just pull the excel spreadsheet from the server you are reading from, open it on the machine with the dts package, do the manipulation & then copy the final result to wherever its going....
Dont make it any more complicated than it has to be - K.I.S.S.
Simple = Robust.
Cheers,|||Why do you have 4 servers in this scenario when you just want to modify some data on another server than where the DTS-package is situated?
What kind of data does this file contain, and why does it have to be updated via Excel?
Managing DTS Programmatically
Does anyone out there have any tip on managing DTS packages
programmatically?
Specifically, I want to install and run a dts package using tsql through
query anyalyzer.
I know how to run it using the xp_cmdshell, but I don't know how to install
it programatically from a Structured Storage File.You can 'install' the structured storage file like you would any other file
needed by your application. DTS doesn't really care where the file resides.
You might consider creating a folder on the SQL Server specifically for DTS
package files. You could then launch the packages using DTSRUN via
xp_cmdshell from that well-known location. Folder/file creation can be done
manually or as part of a Windows Installer package, depending on your
deployment requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23WYn30uEGHA.524@.TK2MSFTNGP09.phx.gbl...
> Does anyone out there have any tip on managing DTS packages
> programmatically?
> Specifically, I want to install and run a dts package using tsql through
> query anyalyzer.
> I know how to run it using the xp_cmdshell, but I don't know how to
> install
> it programatically from a Structured Storage File.
>
programmatically?
Specifically, I want to install and run a dts package using tsql through
query anyalyzer.
I know how to run it using the xp_cmdshell, but I don't know how to install
it programatically from a Structured Storage File.You can 'install' the structured storage file like you would any other file
needed by your application. DTS doesn't really care where the file resides.
You might consider creating a folder on the SQL Server specifically for DTS
package files. You could then launch the packages using DTSRUN via
xp_cmdshell from that well-known location. Folder/file creation can be done
manually or as part of a Windows Installer package, depending on your
deployment requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23WYn30uEGHA.524@.TK2MSFTNGP09.phx.gbl...
> Does anyone out there have any tip on managing DTS packages
> programmatically?
> Specifically, I want to install and run a dts package using tsql through
> query anyalyzer.
> I know how to run it using the xp_cmdshell, but I don't know how to
> install
> it programatically from a Structured Storage File.
>
Saturday, February 25, 2012
manager
hi I just install sql server express
I also download and run the managment script. but unfortunately manager wont
acces to the sqlserver.
where I can find tutorial /help for my problem?
thanks
Since Express is still in beta, you'll want to check in the Community
Newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
--Brian
(Please reply to the newsgroups only.)
"jamal" <patron@.wi.rr.com> wrote in message
news:3y5Ne.41984$3S5.8305@.tornado.rdc-kc.rr.com...
> hi I just install sql server express
> I also download and run the managment script. but unfortunately manager
> wont acces to the sqlserver.
> where I can find tutorial /help for my problem?
> thanks
>
I also download and run the managment script. but unfortunately manager wont
acces to the sqlserver.
where I can find tutorial /help for my problem?
thanks
Since Express is still in beta, you'll want to check in the Community
Newsgroups:
http://communities.microsoft.com/new...r2005&slcid=us
--Brian
(Please reply to the newsgroups only.)
"jamal" <patron@.wi.rr.com> wrote in message
news:3y5Ne.41984$3S5.8305@.tornado.rdc-kc.rr.com...
> hi I just install sql server express
> I also download and run the managment script. but unfortunately manager
> wont acces to the sqlserver.
> where I can find tutorial /help for my problem?
> thanks
>
Management Studion Express alongside full-blown Management Studio?
Is it possible to run both Sql Server Management Studio: Express and full blown side by side?
I am developing with the full blown product but would like to test Management Studio Express on the same box.
Is this possible?
Thanks
Eric 
Hi !
For a full and realistic test, I would put them not on the same machine, as you can′t be sure in which way the big brother will interfer with the deployed assemblies. Just deploy it on a dedicated test machine to do your tests.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Somehow this is the kind of answer I expected...
Well, I think I'll just do that then.
Thanks
Eric 
Monday, February 20, 2012
Management Studio will not allow me to create a view
Hi there,
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:
> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:
> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:
> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:
> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)
Management Studio scripts for SQL 2000
When I use the generate scripts task in Management Studio to create tables
for a SQL 2000 target, the scripts do not run in SQL 2000.
I selected SQL 2000 compatibility mode. The scripts work fine on a 2000 database
running under SQL 2005 but not on the real thing.
Is there a easy way to generate scripts that run on a SQL 2000 instance?
Darren (Darren@.nospam.nospam) writes:
> When I use the generate scripts task in Management Studio to create tables
> for a SQL 2000 target, the scripts do not run in SQL 2000.
Exactly why do they not run? Because they refer to sys.objects, of because
of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
and the script ran on SQL 2000.
I seem to recall that that there was a problem with scripts for SQL 2000
referring to sys.objects in the RTM release ot SQL 2005, but this was
corrected in SP1. You can find SP1 of SQL 2005 at
http://www.microsoft.com/sql/sp1.mspx.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks. I wasn't aware of SP1.
Erland Sommarskog wrote:
> Darren (Darren@.nospam.nospam) writes:
> Exactly why do they not run? Because they refer to sys.objects, of because
> of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
> and the script ran on SQL 2000.
> I seem to recall that that there was a problem with scripts for SQL 2000
> referring to sys.objects in the RTM release ot SQL 2005, but this was
> corrected in SP1. You can find SP1 of SQL 2005 at
> http://www.microsoft.com/sql/sp1.mspx.
>
>
for a SQL 2000 target, the scripts do not run in SQL 2000.
I selected SQL 2000 compatibility mode. The scripts work fine on a 2000 database
running under SQL 2005 but not on the real thing.
Is there a easy way to generate scripts that run on a SQL 2000 instance?
Darren (Darren@.nospam.nospam) writes:
> When I use the generate scripts task in Management Studio to create tables
> for a SQL 2000 target, the scripts do not run in SQL 2000.
Exactly why do they not run? Because they refer to sys.objects, of because
of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
and the script ran on SQL 2000.
I seem to recall that that there was a problem with scripts for SQL 2000
referring to sys.objects in the RTM release ot SQL 2005, but this was
corrected in SP1. You can find SP1 of SQL 2005 at
http://www.microsoft.com/sql/sp1.mspx.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks. I wasn't aware of SP1.
Erland Sommarskog wrote:
> Darren (Darren@.nospam.nospam) writes:
> Exactly why do they not run? Because they refer to sys.objects, of because
> of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
> and the script ran on SQL 2000.
> I seem to recall that that there was a problem with scripts for SQL 2000
> referring to sys.objects in the RTM release ot SQL 2005, but this was
> corrected in SP1. You can find SP1 of SQL 2005 at
> http://www.microsoft.com/sql/sp1.mspx.
>
>
Subscribe to:
Posts (Atom)