Thursday, March 29, 2012
Duplicate SQL Database
it can be used for testing.
Can anyone give me any hints or tips on how to do this.
Thankshi ellie,
you can take help of "backup database"/"restore database" commands. backup y
our source
database using "backup database" command and restore it on the other databas
es with "restore
database" command. you can also also use system stored procedures sp_detach_
db and
sp_attach_db. Use sp_detach_db procedure to detach the data/transaction log
files from the
source database (database will not be available.) and attach the required da
tabases using
sp_attach_db stored procedure.
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi Vishal
I have tried to backup the database (which I can do!) but when it comes to
restoring it, I can't restore it as a new database. I can restore it to a
different sql server using the same name (and same backup file location etc)
but struggle to restore to the same server with a different database name.
Do I need to change the logical file name and the physical file name to
match the new database name?
Thanks
Ellie
"Vishal Parkar" <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote in message
news:%23aI$7mYEEHA.3716@.TK2MSFTNGP10.phx.gbl...
> hi ellie,
> you can take help of "backup database"/"restore database" commands. backup
your source
> database using "backup database" command and restore it on the other
databases with "restore
> database" command. you can also also use system stored procedures
sp_detach_db and
> sp_attach_db. Use sp_detach_db procedure to detach the data/transaction
log files from the
> source database (database will not be available.) and attach the required
databases using
> sp_attach_db stored procedure.
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>|||hi ellie,
you will have to consider "with move" option while running "restore database
" command. Refer
to following topic in books online (this illustrates an example as well)
"How to restore a database with a new name (Transact-SQL)"
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi,
Add on Vishals post, perform these steps
1. Execute the below command from Query analyzer
Restore filelistonly from disk='c:\mssql\backup\dbname.bak'
The above command will give you the details of Logical name and Physical
name
2. Execute the below command from Query Analyzer
restore database <new_dbname> from disk='D:\mssql\backup\dbname.bak'
with
move 'logical_dataname' to 'c:\mssql\data\dbname_new.mdf',
move 'logical_logname' to 'c:\mssql\data\dbname_new.ldf',stats=10
The logical_dataname and Logical_logname should be taken based on the output
of Restore filelistonly command (Step 1)
Note:
Replace dbname , directory name, physical file name based on ur requirement.
Thanks
Hari
MCDBA
"Vishal Parkar" <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote in message
news:OmWg54YEEHA.3180@.TK2MSFTNGP12.phx.gbl...
> hi ellie,
> you will have to consider "with move" option while running "restore
database" command. Refer
> to following topic in books online (this illustrates an example as well)
> "How to restore a database with a new name (Transact-SQL)"
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>|||In addition to all the other posts,
Yes you must use a different name for the database and the files ( but they
do not have to match).
Hari showed you how to do this using T-SQL, you may also do this using the
Restore Database dialog box, Simply change the filenames on the right side
of the dialog box..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ellie" <ellieb@.solutionsforaccounting.co.uk> wrote in message
news:u2WEJlYEEHA.2088@.TK2MSFTNGP10.phx.gbl...
> I am trying to duplicate a SQL Server Database using Enterprise Manager,
so
> it can be used for testing.
> Can anyone give me any hints or tips on how to do this.
> Thanks
>sql
Monday, March 26, 2012
Duplicate instances
NT)â' , in addition to the default instance, â' (local) (Windows NT)â'. This
second instance was not installed, It just appeared. The MSSQL directory
indicates the presence of only one instance. Databases, and security
configurations are identical in structure and information between the two
instances. Has any one seen this happen? Would you recommend deleting the
â'Ghostâ' instance. Any ideas welcome. Thanks to everyone for being there to
help. SQL 2000 SP4.coenzyme wrote:
> On one box Enterprise Manager is indicating a second instance â' . (Windows
> NT)â' , in addition to the default instance, â' (local) (Windows NT)â'. This
> second instance was not installed, It just appeared. The MSSQL directory
> indicates the presence of only one instance. Databases, and security
> configurations are identical in structure and information between the two
> instances. Has any one seen this happen? Would you recommend deleting the
> â'Ghostâ' instance. Any ideas welcome. Thanks to everyone for being there to
> help. SQL 2000 SP4.
>
You can safely delete either one - registrations in EM have no effect on
the actual instances that are running.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy. It's a real comfort to know there's experienced people like you
who are willing to help.
"Tracy McKibben" wrote:
> coenzyme wrote:
> > On one box Enterprise Manager is indicating a second instance â' . (Windows
> > NT)â' , in addition to the default instance, â' (local) (Windows NT)â'. This
> > second instance was not installed, It just appeared. The MSSQL directory
> > indicates the presence of only one instance. Databases, and security
> > configurations are identical in structure and information between the two
> > instances. Has any one seen this happen? Would you recommend deleting the
> > â'Ghostâ' instance. Any ideas welcome. Thanks to everyone for being there to
> > help. SQL 2000 SP4.
> >
> >
> You can safely delete either one - registrations in EM have no effect on
> the actual instances that are running.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Duplicate instances
s
NT)” , in addition to the default instance, “ (local) (Windows NT)”.
This
second instance was not installed, It just appeared. The MSSQL directory
indicates the presence of only one instance. Databases, and security
configurations are identical in structure and information between the two
instances. Has any one seen this happen? Would you recommend deleting the
“Ghost” instance. Any ideas welcome. Thanks to everyone for being there
to
help. SQL 2000 SP4.coenzyme wrote:
> On one box Enterprise Manager is indicating a second instance “ . (Wind
ows
> NT)” , in addition to the default instance, “ (local) (Windows NT)”
. This
> second instance was not installed, It just appeared. The MSSQL directory
> indicates the presence of only one instance. Databases, and security
> configurations are identical in structure and information between the two
> instances. Has any one seen this happen? Would you recommend deleting the
> “Ghost” instance. Any ideas welcome. Thanks to everyone for being ther
e to
> help. SQL 2000 SP4.
>
You can safely delete either one - registrations in EM have no effect on
the actual instances that are running.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy. It's a real comfort to know there's experienced people like yo
u
who are willing to help.
"Tracy McKibben" wrote:
> coenzyme wrote:
> You can safely delete either one - registrations in EM have no effect on
> the actual instances that are running.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Thursday, March 22, 2012
Duplicate FK Relationships - Help pl
I came across a situation in couple of the enterprise application projects, where I found a strange fact in the MS SQL Server Database they used.
I found multiple foreign-key(FK) relationships existing between tables. When examining the table design, I found that same relationship existed thrice with 3 different FK constraint names.
There are tables with single primary-key(PK) and few others with composite-PKs. As there are tables linked through relationships, will it matter that tables with composite-PKs will involve redundant FK relations with other tables?
I am puzzled as how redundant FK relationships could have got created. Is this purely human error or anythign with respect to MS SQL Server?
Please explain the probable cause for this scenario.
Thanks,
Ganesh
MS SQL does not create anything automatically. If FKs are there, it is because some human or a code generator created it.You didn't say what version you were using, but there were weird problems, which were fixed early in 2000, with the "Diagrams" feature which might have done stuff like that, but I don't know anyone who uses the diagramming in MS SQL so I would be surprised if that was the cause.
|||
Hi Tom,
Thanks for the reply.
I use MS SQL Server 2000 version.
The other project used previous version of MS SQL Server.
In both these projects, the data base design was migrated from another source. I doubt this migration of design is the main cause. But still, the same question remains for the 1st database from where the design was migrated: "Does the initial database source had this duplication because of humar error or something else"?
NOTE: The diagram tool in MS SQL Server is perfect in this aspect, as I checked the design of tables from Query Analyzer - where there were duplicate FK replationships (different constraint-names) for the same column mapping. No database programmer intentionally creates multiple FK constraints on same column mapping. Then, what behind this?
Please check the database of your projects and come back if you too find the same. Also think of the cause for this.
|||There is nothing restricting the use of multiple FKs on the same fields to the same tables, with different constraint names. Yes, it is redundant and will hurt performance and should not be done.Still, either some human did this, accidentally or for some unknown purpose, or a code generator did it. Most likely a code generator created them.
In any case, they should be removed.
|||Thanks Phillips.
Monday, March 19, 2012
Dump files in SQL 7.0 Enterprise
I have been experiencing crash dump files on our SQL 7 Enterprise box several times per day for the last few months. I can't seem to match them up to a particular task or job.
Any ideas appreciated. Thank you.
Here is the head of one of the .dmp files:
================================================== ===================
BugCheck Dump
================================================== ===================
This file is generated by Microsoft SQL Server 1792.842
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current local time is Wed Jul 21 12:13:00 2004
2 Intel x86 level 6, 797 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
TotalPageFile = 3941 MB
Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
Symbol load failed - Proceed to dump without any symbols
Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 07/21/04 12:13:00 spid 29
*
* Input Buffer 42 bytes -
* s p _ r e s e t _ c o n n e c t i o n
*
************************************************** *****************************
Parsing dump files is not something everybody can do and you only provided a
fraction of it. If your crashing that often you should contact MS PSS and
let them work directly with you to fix the issue. They can certainly access
the dump files properly.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"ddrewrey" <ddrewrey@.discussions.microsoft.com> wrote in message
news:A939C3EC-D341-4835-B4EC-E9FB7AF26638@.microsoft.com...
> Hello,
> I have been experiencing crash dump files on our SQL 7 Enterprise box
several times per day for the last few months. I can't seem to match them up
to a particular task or job.
> Any ideas appreciated. Thank you.
> Here is the head of one of the .dmp files:
> ================================================== ===================
> BugCheck Dump
> ================================================== ===================
> This file is generated by Microsoft SQL Server 1792.842
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
> Computer type is AT/AT COMPATIBLE.
> Current local time is Wed Jul 21 12:13:00 2004
> 2 Intel x86 level 6, 797 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> --
> MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
> TotalPageFile = 3941 MB
> Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
> Symbol load failed - Proceed to dump without any symbols
> Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
>
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 07/21/04 12:13:00 spid 29
> *
> * Input Buffer 42 bytes -
> * s p _ r e s e t _ c o n n e c t i o n
> *
>
************************************************** **************************
***
Dump files in SQL 7.0 Enterprise
I have been experiencing crash dump files on our SQL 7 Enterprise box several times per day for the last few months. I can't seem to match them up to a particular task or job.
Any ideas appreciated. Thank you.
Here is the head of one of the .dmp files:
===================================================================== BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server 1792.842
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current local time is Wed Jul 21 12:13:00 2004
2 Intel x86 level 6, 797 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
--
MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
TotalPageFile = 3941 MB
Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
Symbol load failed - Proceed to dump without any symbols
Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
*******************************************************************************
*
* BEGIN STACK DUMP:
* 07/21/04 12:13:00 spid 29
*
* Input Buffer 42 bytes -
* s p _ r e s e t _ c o n n e c t i o n
*
*******************************************************************************Parsing dump files is not something everybody can do and you only provided a
fraction of it. If your crashing that often you should contact MS PSS and
let them work directly with you to fix the issue. They can certainly access
the dump files properly.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly SQL MVP
"ddrewrey" <ddrewrey@.discussions.microsoft.com> wrote in message
news:A939C3EC-D341-4835-B4EC-E9FB7AF26638@.microsoft.com...
> Hello,
> I have been experiencing crash dump files on our SQL 7 Enterprise box
several times per day for the last few months. I can't seem to match them up
to a particular task or job.
> Any ideas appreciated. Thank you.
> Here is the head of one of the .dmp files:
> =====================================================================> BugCheck Dump
> =====================================================================> This file is generated by Microsoft SQL Server 1792.842
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
> Computer type is AT/AT COMPATIBLE.
> Current local time is Wed Jul 21 12:13:00 2004
> 2 Intel x86 level 6, 797 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> --
> MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
> TotalPageFile = 3941 MB
> Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
> Symbol load failed - Proceed to dump without any symbols
> Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
>
****************************************************************************
***
> *
> * BEGIN STACK DUMP:
> * 07/21/04 12:13:00 spid 29
> *
> * Input Buffer 42 bytes -
> * s p _ r e s e t _ c o n n e c t i o n
> *
>
****************************************************************************
***
Dump files in SQL 7.0 Enterprise
I have been experiencing crash dump files on our SQL 7 Enterprise box severa
l times per day for the last few months. I can't seem to match them up to a
particular task or job.
Any ideas appreciated. Thank you.
Here is the head of one of the .dmp files:
========================================
=============================
BugCheck Dump
========================================
=============================
This file is generated by Microsoft SQL Server 1792.842
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current local time is Wed Jul 21 12:13:00 2004
2 Intel x86 level 6, 797 Mhz processor(s).
Windows NT 5.0 Build 2195 CSD Service Pack 4.
Memory
--
MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
TotalPageFile = 3941 MB
Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
Symbol load failed - Proceed to dump without any symbols
Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 07/21/04 12:13:00 spid 29
*
* Input Buffer 42 bytes -
* s p _ r e s e t _ c o n n e c t i o n
*
****************************************
************************************
***Parsing dump files is not something everybody can do and you only provided a
fraction of it. If your crashing that often you should contact MS PSS and
let them work directly with you to fix the issue. They can certainly access
the dump files properly.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"ddrewrey" <ddrewrey@.discussions.microsoft.com> wrote in message
news:A939C3EC-D341-4835-B4EC-E9FB7AF26638@.microsoft.com...
> Hello,
> I have been experiencing crash dump files on our SQL 7 Enterprise box
several times per day for the last few months. I can't seem to match them up
to a particular task or job.
> Any ideas appreciated. Thank you.
> Here is the head of one of the .dmp files:
> ========================================
=============================
> BugCheck Dump
> ========================================
=============================
> This file is generated by Microsoft SQL Server 1792.842
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
> Computer type is AT/AT COMPATIBLE.
> Current local time is Wed Jul 21 12:13:00 2004
> 2 Intel x86 level 6, 797 Mhz processor(s).
> Windows NT 5.0 Build 2195 CSD Service Pack 4.
>
> Memory
> --
> MemoryLoad = 97% TotalPhys = 2047 MB AvailPhys = 47 MB
> TotalPageFile = 3941 MB
> Dump thread - spid = 29, PSS = 0x35799138, EC = 0x357992e8
> Symbol load failed - Proceed to dump without any symbols
> Stack Dump being sent to C:\MSSQL7\log\SQL08175.dmp
>
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 07/21/04 12:13:00 spid 29
> *
> * Input Buffer 42 bytes -
> * s p _ r e s e t _ c o n n e c t i o n
> *
>
****************************************
************************************
***
Sunday, March 11, 2012
dumb question
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:
> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Friday, March 9, 2012
Dual Instances In onSQL Server 2000 Enterprise Manager
I am working on a site's SQL Server 2000 database on a W2k3 machine . I went into Enterprise Manager and saw that their database resides on a named instance. I did not see the default instance listed so I registered that using windows authentication. I noticed that the default instance had a user database that had the same name as the user database on the named instance that I was to work on. I looked at the properties of the databases and saw that on both the default and named instances of SQL Server that the Data Files and Log Files for the user database point to the same location.
Is this a problem? Can anyone see any issues with this? Does this mean that someone can simply connect to the named or the default instance of the SQL Server and connect to the same database?
Kirk
That is the not possible if one instance is already using those database files, then the other instance will not be able to use or even attach the database. Try to refresh the Databases pane of Enterprise Manager on the both the instancesDual Core CPUs and SQL Server 2000
Edition on Windows 2003 Enterprise Edition each equipped with 2 processors.
I'm wondering if anyone has experience with or knowledge of using dual core
Intel Xeon Processors with SQL Server, and if there are definite improvements
over single core higher clocked processors. I'm trying to decide whether to
configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4Ghz
single core processors.
Thanks.
Hi
You may want to read this
http://www.sql-server-performance.com/jc_processor.asp although not
specifically about dual core I would expect dual core to be the akin to using
more than one processor.
John
"Marks70" wrote:
> I need to configure and purchase two new servers running SQL 2000 Enterprise
> Edition on Windows 2003 Enterprise Edition each equipped with 2 processors.
> I'm wondering if anyone has experience with or knowledge of using dual core
> Intel Xeon Processors with SQL Server, and if there are definite improvements
> over single core higher clocked processors. I'm trying to decide whether to
> configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4Ghz
> single core processors.
> Thanks.
Dual Core CPUs and SQL Server 2000
Edition on Windows 2003 Enterprise Edition each equipped with 2 processors.
I'm wondering if anyone has experience with or knowledge of using dual core
Intel Xeon Processors with SQL Server, and if there are definite improvements
over single core higher clocked processors. I'm trying to decide whether to
configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4Ghz
single core processors.
Thanks.Hi
You may want to read this
http://www.sql-server-performance.com/jc_processor.asp although not
specifically about dual core I would expect dual core to be the akin to using
more than one processor.
John
"Marks70" wrote:
> I need to configure and purchase two new servers running SQL 2000 Enterprise
> Edition on Windows 2003 Enterprise Edition each equipped with 2 processors.
> I'm wondering if anyone has experience with or knowledge of using dual core
> Intel Xeon Processors with SQL Server, and if there are definite improvements
> over single core higher clocked processors. I'm trying to decide whether to
> configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4Ghz
> single core processors.
> Thanks.
Dual Core CPUs and SQL Server 2000
Edition on Windows 2003 Enterprise Edition each equipped with 2 processors.
I'm wondering if anyone has experience with or knowledge of using dual core
Intel Xeon Processors with SQL Server, and if there are definite improvement
s
over single core higher clocked processors. I'm trying to decide whether to
configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4Gh
z
single core processors.
Thanks.Hi
You may want to read this
http://www.sql-server-performance.com/jc_processor.asp although not
specifically about dual core I would expect dual core to be the akin to usin
g
more than one processor.
John
"Marks70" wrote:
> I need to configure and purchase two new servers running SQL 2000 Enterpri
se
> Edition on Windows 2003 Enterprise Edition each equipped with 2 processors
.
> I'm wondering if anyone has experience with or knowledge of using dual cor
e
> Intel Xeon Processors with SQL Server, and if there are definite improveme
nts
> over single core higher clocked processors. I'm trying to decide whether t
o
> configure the servers with two 2.8Ghz Dual Core Xeon processors or two 3.4
Ghz
> single core processors.
> Thanks.
Sunday, February 19, 2012
Dts works from SqlServer but not from ASP.net
I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql server
It works right. but when I execute it from my asp.net page I have this error.
Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:\Documents and Settings\luisvalen\Mis documentos\Visual Studio Projects\InventariosLentos\LibreraLentos\exec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:\AplicacionesWeb\InventariosLentos\generacionprocesomateriaprima.aspx.vb:line 60LibreraLentos
I have this on my ASP page
Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click
Try
objexecsp.ejecuta_SP_EXISTENCIASMP()
lblmensajes.Text = "Proceso generado satisfactoriamente"
Catch ex As Exception
lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString
End Try
End Sub
this on my Data Classs
Public Function ejecuta_SP_EXISTENCIASMP()
' call UpdatePrice using a parameter array of SqlParameter objects
Try
Dim ejecutardts As New cDTS
ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")
Catch ex As Exception
Throw ex
End Try
End Function
This is what executes the DTS
Imports System.Runtime.InteropServices
Imports System.Configuration.ConfigurationSettings
Imports DTS
Public Class cDTS
Public Sub EjecutarDTS(ByVal NombreDTS As String)
Dim pkg As New DTS.Package
Dim oStep As DTS.Step
Try
pkg = New DTS.Package
'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")
pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")
pkg.AutoCommitTransaction = True
pkg.Execute()
For Each oStep In pkg.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)
End If
Next
Catch ex As System.Runtime.InteropServices.COMException
Throw ex
Catch ex As Exception
Throw ex
Finally
pkg.UnInitialize()
pkg = Nothing
End Try
End Sub
End Class
This is the CODE of my dts
but as I told before it Works when I right Click on it in enterprise manager
Dim ConnSql
Dim ConnDb2
'* Función para Conexion a Base de Datos ASW en Med13nt
Function ConexionSql()
'On Error Resume Next
Dim strConexion
strConexion = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=True;" & _
"User ID=sa;Password=xx;" & _
"Initial Catalog=asw;" & _
"Data Source=Med20nt"
Set ConnSql = CreateObject("ADODB.Connection")
ConnSql.Open strConexion
If Err.Number <> 0 then ConexionSql = False
End Function
'* Función de Conexion a ASW as400
Function ConexionDb2()
'On Error Resume Next
Dim strConexion
strConexion = "DSN=asw1;User ID=CLAUDIMON;Password=xx"
Set ConnDb2 = CreateObject("ADODB.Connection")
ConnDb2.Open strConexion
If Err.Number <> 0 Then ConexionDb2 = False
' conexion por OLEDB
' Dim strconexion
' strconexion = "Provider=IBMDA400.DataSource.1; Data source=10.2.0.10;User Id=FVENTAS;Password=xx"
' Set ConnDb2 = CreateObject("ADODB.Connection")
' ConnDb2.Open strConexion
' If Err.Number <> 0 Then ConexionDb2 = False
End Function
'* Trasfiere los datos de ASW a Med20nt
Private Function Transferir()
'On Error Resume Next
Dim strSql
Dim producto
Dim strSql2
Dim strSql3
Dim strCero
Dim strcomilla
Dim RS
Dim RS2
Dim RS3
Dim Contador
Dim StrBodega
strBodega=" "
contador=1
strcomilla="'"
strCero="0"
Set RS = CreateObject("ADODB.Recordset")
Set RS2 = CreateObject("ADODB.Recordset")
Set RS3 = CreateObject("ADODB.Recordset")
strSql3 = "SELECT distinct producto from sumvenmpf"
ConnSql.Execute (strSql3)
RS.Open strSql3, ConnSql
' ConnSql.Execute (strSql3)
Do Until RS.EOF
Set RS2 = CreateObject("ADODB.Recordset")
strSql = "SELECT srprdc, srsrom, srplan,ctname,sum(srsthq), sum(srpurq), sum(srcusq), sum(srpicq) FROM HCB453AFIH.srbsro,HCB453AFIH.srbctlsd where ctsign=srplan and srprdc="&strcomilla&RS.Fields("producto").value&strcomilla &" and (srsthq>0 or srpurq>0 or srcusq>0 or srpicq>0) group by srprdc,srsrom,srplan,ctname"
contador=0
RS2.Open strSql, ConnDb2
contador=contador+1
Do until RS2.EOF
strSql2 = "INSERT INTO ASW.dbo.Existenciasmp (srprdc,srsrom,srplan,ctname,srsthq, srpurq, srcusq, srpicq ) values ('" & RS2.Fields("srprdc").value & "','" & RS2.Fields("srsrom").value & "','" & RS2.Fields("srplan").value & "','" & RS2.Fields("ctname").value & "','" & RS2.Fields(4).value & "','" & RS2.Fields(5).value & "','" & RS2.Fields(6).value & "','" & RS2.Fields(7).value & "')"
ConnSql.Execute (strSql2)
RS2.MoveNext
Loop
RS2.Close
Set RS2 = Nothing
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
End Function
'* Inicio de la Interfaz
Function Principal()
'On Error Resume Next
Call ConexionSql
Call ConexionDb2
Call Transferir()
Principal = DTSTaskExecResult_Success
End Function
The strange thing here is that it always returns 864 rows.
when executing from asp.net and 5000 from enterprise manager
I have the same issue, which I set up a DTS package in the SQL Server. When I execute package from the Enterpise manager, it ran with no error.
However, when I run it from an asp.net page, it fails. I use the below code to validate the error of the steps. What could it be wrong?
Thanks!
Terrence
DimWithEvents oPkgAs DTS.Package
oPkg =New DTS.Package()
Dim vStepAs DTS.Step
ForEach vStepIn oPkg.Steps
'test if the activescripttask type of task is in this step, and dont have to the transaction if so
'use instr (return value of the position of string location)
IfNot InStr(1,CType(vStep.Name,String), "DTSActiveScriptTask", CompareMethod.Text) > 0Then
IfNot InStr(1,CType(vStep.Description,String), "Update HTMLText Table", CompareMethod.Text) > 0Then
vStep.JoinTransactionIfPresent =True
Else
vStep.JoinTransactionIfPresent =False'if the step is the 'Update HTMLText Table' step then dont join to transaction
EndIf
Else
vStep.JoinTransactionIfPresent =False'if the step is executing a script task, then dont join the transaction
EndIf
vStep.RollbackFailure =True
Next
'modify transaction properties of the package
oPkg.UseTransaction =True
oPkg.AutoCommitTransaction =True
'set the current time to later evaluate the execution time
sTimestart = Now()
'execute the package
OnErrorResumeNext
oPkg.Execute()
OnErrorGoTo 0
'evaluate total execution time of the package
Dim sTime
sTime = DateDiff("s", sTimestart, Now())
'error logging
'default the flag to true (successful execution) and set to false if error occurs
Dim flagAsString =True
Dim sErrAsString
Dim strLogAsString
Dim oDTSStepAs DTS.Step
ForEach oDTSStepIn oPkg.Steps
If oDTSStep.ExecutionResult = oDTSStep.ExecutionResult.DTSStepExecResult_FailureThen
.....
|||
DTS is SQL Server Agent dependent, your SQL Server Agent needs a service account. Try the links below. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
DTS wont work with Ole DB provider but will with odbc
So everything seems to be working (haven't tested alot) but now I can't run DTS!
I get a weird error.
so then I tried ODBC and it works :[sp3
Friday, February 17, 2012
DTS utility
I am using the DTS utility available with Enterprise Manager in MSSQL server 2000. I can transfer the tables and views without any issues but when i try to transfer the stored procedures it always gives an error. I have tried transferring individual objects too but it does'nt works. The error given is "the user <username> cannot perform the following action". Any help is appreciated.
Thanks in AdvanceEasy is to script them and run at target server.|||I always liked Nigel's sig...
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578
And what that basically means, is what your are fundamentally trying to do is not the best way to skin a cat...
You need to undestand the methodolgy of deployement...
If you describe what you are ultimatley trying to do, I'm sure we can help..|||On the lighter side Brett conveyed the message... :D
On the hardest part I feel DTS must be used for data transfer and for this sort of things use Generate Script utility.
Also one of my peer raised this may be a security issue, so convey the permissions for this user on database objects.|||Got the point guys!!! I used the generate script utility, it is easier and without any issues.
Thanks
Wednesday, February 15, 2012
dts to copy jobs
same. When using DTS to transfer the jobs from old server to new 4 of them
will not transfer over to the new server. It just gives a unspecified error.
Any ideas?
You can generate a script for the jobs instead, through Enterprise Manager
(right click a job name, and it is under Tasks). Just run the generated
script on the target server.
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Tom Reis" wrote:
> We are upgrading our SQL 2000 Enterprise Server to a new server running the
> same. When using DTS to transfer the jobs from old server to new 4 of them
> will not transfer over to the new server. It just gives a unspecified error.
> Any ideas?
>
>
dts to copy jobs
same. When using DTS to transfer the jobs from old server to new 4 of them
will not transfer over to the new server. It just gives a unspecified error.
Any ideas?You can generate a script for the jobs instead, through Enterprise Manager
(right click a job name, and it is under Tasks). Just run the generated
script on the target server.
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Tom Reis" wrote:
> We are upgrading our SQL 2000 Enterprise Server to a new server running th
e
> same. When using DTS to transfer the jobs from old server to new 4 of them
> will not transfer over to the new server. It just gives a unspecified erro
r.
> Any ideas?
>
>
dts to copy jobs
same. When using DTS to transfer the jobs from old server to new 4 of them
will not transfer over to the new server. It just gives a unspecified error.
Any ideas?You can generate a script for the jobs instead, through Enterprise Manager
(right click a job name, and it is under Tasks). Just run the generated
script on the target server.
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Tom Reis" wrote:
> We are upgrading our SQL 2000 Enterprise Server to a new server running the
> same. When using DTS to transfer the jobs from old server to new 4 of them
> will not transfer over to the new server. It just gives a unspecified error.
> Any ideas?
>
>