DTS Using a Query or VB ADO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS Using a Query or VB ADO

Author

Topic

bkmurti
Starting Member

13 Posts

Posted - 2002-11-26 : 17:59:40

hello,

i am trying to develop a small database utility that handles general database operations fro my college onternal project. I am struck on Data Transformation Service.

i am not able to find any information on it and the info i found with SQL server help i wan not able to understand

1. Can any one suggest a way to do it via a Query , i can convert it into VB code and execute
for example SqlServer to Access

2. I wanted to know VIA code if SQL Service manager has started if not i want start it from my VB code how will i do it.

Advance thanks,
gopal

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-26 : 18:48:54

With a better explanation of what you're trying to do you're going to get better advice.

What are you trying to do in the dts.?

Sounds like you're trying to pull data from SqlServer to Access.
If this is the case let me suggest using access 2000/2002 and creating an access data project you can then create views, stored procedures, triggers, forms, reports, charts...etc...

As to your second question
Enumeration of windows services is the key.
http://support.microsoft.com/default.aspx?scid=KB;en-us;q183478

bkmurti
Starting Member

13 Posts

Posted - 2002-11-26 : 21:24:35

hai,
what i am trying to do is convert database in one format to database into another format

for ex: sql Server to access
sql server to excel
...

this what i am trying to do

bye
murti

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 21:38:42

OK, but what does that have to do with the Service Manager?

You can also use SQL-DMO to check the status of a SQL Server, there is documentation in Books Online on how to use the SQL-DMO object model.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-26 : 22:50:06

Excel is not a database it's a data analysis tool.

Most of the time people are going from access to sql server just wondering why you would want to go backwards.

bkmurti
Starting Member

13 Posts

Posted - 2002-11-27 : 08:45:19

hai,
what i am trying to do is include general database operations like
create database,backup,restore etc for these operations to run Service manager has to be started else it is giving error.

i gave an example sqlserver to access but it also other way round, i am trying to mimic DTS wizard
bye
murti

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-27 : 09:13:09

quote:
what i am trying to do is include general database operations like
create database,backup,restore etc for these operations to run Service manager has to be started else it is giving error.

No, the Service Manager does NOT have to be running, the SQL Server needs to be running. The error message may indicate you need to use Service Manager to start SQL Server, but once the SQL Server is started Service Manager is no longer necessary. By the way, what is the exact error message you are getting? It's odd that a SQL Server wouldn't already be started and running when they try to connect. I think you need to explain your situation in more detail (DON'T generalize or simplify, give us as much information as you can)

Unless security is an issue, I don't know why you don't just provide Enterprise Manager to the people accessing the server. You're making a lot more work for yourself trying to re-create its functionality. And trying to mimic the Import/Export Wizard is unnecessary, you can write code that actually calls the wizard directly.

bkmurti
Starting Member

13 Posts

Posted - 2002-11-27 : 20:10:46

hi robvolk,

the simple thing is i am doing an application for my self. i am using desktopserver of sql server 7. i am doing this as an attemp in my home for a better knowledje of VB as well as sql server,not in office.

until now i got errors when o tried to open dtabases without service manager,saying connection failed.

i did not that you need not start service manager.

now, how do u start
1. sql server from vbcode,as well as service manager
2. how to check if sql server and its service manager are running
3. how to tranform data from one format to anothet.

***Error Message without service manager started is "

connection could not be established to Servername-sql server doesnot exist or access denied
connectionOpen(connect())
Please verify sql server is running and check your sql server registration properties

***** This is the message and this is not coming when serice manager is started**

waiting for reply

sorry for troubling you,
thanks
murti

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-27 : 21:06:25

Hi

I think what you are after is the Start method of the SQL Server object in DMO. It will connect to an SQL Server instance and attempt to start it.

There is some doco in Books Onlike about it.

Hope that helps

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-27 : 21:31:28

Here's some code:

Public Sub sqlser()
Dim sql As Object
set sql=CreateObject("SQLDMO.SQLServer")
sql.Name = "mySQLServer"                 'set the name of the server
MsgBox sql.Status                        'check the status code; 1=running, 3=stopped, 4=starting
sql.Start False, "mySQLServer"           'try starting the SQL Server
MsgBox sql.Status                        'check status code again
Set sql = Nothing                        'destroy object when done
End Sub

You can rewrite this to check the status code before starting, and then optionally start the service if it is stopped (using the Start method when it's already running will throw a trappable error) However, you should check it again after a few seconds (10-15, maybe more) to ensure that the server started correctly. This code does NOT require the Service Manager to be running, unless you are running everything on something like Windows 98 or ME perhaps, but it works without Service Manager on Win2K. Frankly, running SQL Server on Win98 or ME is a waste of time, it will never behave the same way as it does on Win2K or NT.

You should also look into the SQL-NS object model in Books Online, it details how to run the various SQL Server wizards, including the Import/Export wizard. I know you're trying to learn, but wizard building is not a good first or second project for educational purposes; they're a little bit too involved. If you need to provide as much functionality as possible, use SQL-NS to run the existing wizard. You'll learn a lot more from writing code to do that, and you can apply that to your own wizard afterwards. When you do, take a look at the DTS object model, also documented in Books Online. It covers the various Package, Transformation, and other objects related to DTS.

Edited by - robvolk on 11/27/2002 21:32:32

bkmurti
Starting Member

13 Posts

Posted - 2002-12-04 : 20:30:47

hi robvolk
thanks for your advise, i have tried what u have given me and it was usefull.

but i the example code we have to give the server name. i heard that the server name and other info will be stored in registry ,how to retrieve it.
as i want server to start automatically and shutdown automatically.
how will i know the list of servers without actually starteing the servers

i tried this after studying books online

Private Sub Command1_Click()
Dim sql As Object
Dim str As String
Set sql = CreateObject("sqldmo.registeredserver")
sql.GetName str 'Error is coming here
'str = sql.Name ' when i try this i am getting empty message box
MsgBox str
End Sub

it is giving error saying "object doesn't support this property"

thank u
bye
murti

Private Sub Command1_Click()
Dim sql As Object
Dim str As String
Set sql = CreateObject("sqldmo.registeredserver")
sql.GetName str
'str = sql.Name
MsgBox str
End Sub

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-04 : 20:53:38

quote:
as i want server to start automatically and shutdown automatically.
how will i know the list of servers without actually starteing the servers

???

You're going to have to explain in more detail what you're trying to do. This kind of feature makes no sense to me whatsoever. I don't see any need to start or stop servers for the sake of a running application; that makes no sense and is not an ideal use of SQL Server, and shouldn't be a necessary feature for any of the other tasks you've described so far (in fact it will interfere with the successful operation of those tasks)

The same applies to the names of the servers, although you can find code to read registry keys on any decent Visual Basic site. It shouldn't be necessary to require such a feature however, since you'd need to know the names of the servers beforehand in order to register them in the first place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources