ActiveX

I have to put some ActiveX code in the first step of a DTS package, which will search thru all the files in some folder and if it finds a file with filename starting with "Test" (like TestFile.txt), the script will rename it and then use it for transforming data to SQL tables.

In VB Dir$ function could have been used, what should I use in ActiveX?

Thanks a lot for your helpI'm not sure how to rename the file but you might be able to use the xp_fileexist extended proc to see if the file is in the directory.|||This is a start:

Function Main()

Dim objFSO
Dim objFolder
Dim objFile
Dim fileName
Dim folderName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\share$\dir\")
folderName = "\\server\share$\dir\Archive\" & Month(Now) & Day(Now) & Year(Now) & "_" & Hour(Now) & Minute(Now) & Second(Now) & "\"

For Each objFile in objFolder.Files

objFSO.CreateFolder(folderName)

fileName = objFile.Path

objFSO.MoveFile fileName, folderName
Next

Main = DTSTaskExecResult_Success

End Function|||Now I'm getting what I need to do, thanks a lot for your help.sql

active/passive w/o shared disk array

can you have an active/passive without a shared disk array?
can you have an active/active without a shared disk array?
Where are the actual db files stored?
thanksNo you'll need to setup shared disks|||where are the db file stored? On the sared disk?

Can I set up a regular server as a "shared disk" instead of buying expensive disk arrays such as netapp? Any other options?

thanks|||Yes, db files are stored on shared disk, not local|||I think Microsoft advises against using a NAS device, or a shared network drive for SQL Server files. I think it can technically be done, but the chance of corruption of the database is inordinately high.|||Without a shared disk array, when one server goes down, how does the other server get to the data?

SAN is the perferred, although, we have 2 cluster environments here running with DELL PowerVaults and things have been going well so far.

active/active clustering

how does an active/active cluster work with sql server? What about both servers trying to write to the same database files? Each has their own locks. How does active/active work with such constraints?also can you scale out with active/active via database filegroups?|||There is no such thing as an active/active SQL Server cluster. You can install multiple instances or just a single instance in a cluster. Each instance runs on one and only one node at any given time and is inaccessible through any other node. So, it is not possible to use clustering for load balancing, scaling out, or anything other than as a hardware protection mechanism only.|||

thank you for the confirmation of trying to scale via active/active.

|||

There is some sort of "load balancing" possible. It is not real load balancing as you would like it to be, but a very usefull option in my opinion.

Install a cluster with 2 instances and mirror the database from the first instance to the second instance. (it does not have to be a cluster, but it can be usefull for up-times)

The database on the second instance can not be accessed now, but you can make a snapshot of that database and use the snapshot for querying.

The snapshot will not be available for updates, but in some cases you can use the snapshot for query users. That way you keep the query users from your live database, so performance of the live database will not be affected by them and the query users do have reasonable up to date data at their fingertips.

|||

If you were going to do that, there really wouldn't be any point in configuring clustering at all. You would simply drop in Database Mirroring with a Database Snapshot created against the mirror database. Actually gives better fault tolerance than clustering, because you have two copies of the data and it gives better availability, because mirroring reacts more quickly than clustering does. And, you wind up in exactly the same state as if you clustered with 2 instances and then built mirroring, because a cluster failover would trigger a mirror failover and your applications would still need to connect to the mirror which most likely will require manual intervention since it will be a while until all applications are using the new connection libraries with the redirect code in it.

I don't like clustering combined with Database Mirroring for one very specific reason. Every cluster failover casues a cascading failover of Database Mirroring. So, instead of having just a single failover event, you have two each time.

A scenario like this would be served much more stably by using either replication or log shipping instead of Database Mirroring, because you won't get a cascading failover in the event the primary node in the cluster fails and once the instance is brought back online, processing resumes on exactly the same instances as before.

Active Database Connections SQL Express, how many

How can I determine if everyone has logged off of the SQL Express database, ie, no one else is connected.

I need to clean up some files that are generated in my code, but I only want to do that when everyone has logged off the database?

In MS Access, I could try to re-open the MDB file exclusively.

Is there a connection number etc?

Hi there,

There isn't a connection number per se (well there is the @.@.CONNECTIONS function but that returns all server connections since the server was last started/restarted so it's not really useful for your situation).

You can use the stored procedure sp_who or even sp_who2 by opening up a new query window and typing in "exec sp_who" or "exec sp_who2".

These stored procedures will give you a result set showing all active processes in the database, what database they are currently working in and also what machine (host) they are connected from. Note that a connection may take up multiple processes so you are looking for distinct process IDs in the output from these stored procedures.

I guess you could use the output from the stored procs to see that you're the only person connected to the database (use the combination of the host name, process ID and database name to determine this).

You could be more complicated and actually query system tables for the information you want, but using the stored procedure should be sufficient.

Also, you can set your database to single user to prevent anyone else connecting when you do your work....Actually, from my understanding, setting a database to single user mode actually boot anybody who is currently connected but not you if anyone actually is connected when you set the database to single user mode.

To set single user mode, you can:

1) Right click on your database in Management Studio Express and select "Properties" from the context menu that appears

2) In the "Database Properties" dialog, there will be an option (on the left hand side) for "Options". Click on this

3) There will be a set of properties displayed in the bottom half of the dialog. Scroll through these and there should be a property called "Restrict Access". Change this property (should currently be MULTI_USER) to SINGLE_USER and apply.

4) After you've finished, set the "Restrict Access" property back to MULTI_USER so that other people can connect. Note that sometimes Management Studio Express can have a hissy fit when you try to do this....In that case, close Management Studio Express, re-open it and re-connect to the server and then try again.

NB (1). It might take a little bit of time for all people using your database to be booted once you set the access to SINGLE_USER so wait a bit before doing any work.

NB (2). If you are connecting as an account with SysAdmin privileges (e.g. "sa") and everyone else who connects does not use an account with either SysAdmin, dbowner or dbcreator privileges then you can also set the access to RESTRICTED_USER instead of SINGLE_USER

NB (3). Restricting database access can also be done through the ALTER DATABASE statement if you want to write some SQL for it. I find the GUI is easier, though, but if you want to use SQL then look in SQL Server Books Online for info on the ALTER DATABASE statement.

Hope that helps a bit, but sorry if it doesn't
|||

If you're using a tool such as Management Studio (Full or Express) you can open the Activity Monitor from the Management node. This will tell you all the processes that are open and what they are doing. You can also find information about existing Locks.

If these suggestions don't meet your needs, you could also ask this question on the SQL Database Engine forum or the T-SQL forum. Those forums are great places to ask general questions about how to do general things in SQL Server.

Regards,

Mike

Mark the best posts as Answers!

Active Data - binding recordset not working

I have converted some reports to read TTX files (from OLEBD), and then changed the 'runReport' page to discard data, and 'setprivate' data (see below). This works beautifully in the test enviroment, but not on our production servers (windows 2000). Its Crystal 8 on all servers.

If any one has any idea... or needs me th explain in more detail... your help would be appreciated!!!How did you run on production?
Did you create set up and installed there?|||Yes - I copied over the TTX files, the associated report files, and the modified ASP pages to the live web servers. By the way, here is the code used on the ASP pages that binf the active data to the report:

rs.Open "SELECT * from table1",cn,adOpenForwardOnly

set session("oRs")=rs

'Attach to report file, and then reassign data from recordset to it
Set Database = session("oRpt").Database
set Tables = Database.Tables
set Table1 = Tables.Item(1)

session("oRpt").DiscardSavedData
Table1.SetDataSource session("oRs")

Acrobat Writer and Reporting Services

If I have Adobe Acrobat Professional installed in my Report Server, would I be able to toggle the document propeties of PDF files (Export to PDF) to make it not-printable, not-modifiable?

Is there any .dll that I can overwrite to change the default settings?

thanks

You could try writing your own rendering extension and output the report to PDF manually as well as setting the additional options you wish. Maybe you could hack it by making your extension call the report server via the webserice, retrieving the PDF output and setting the additional settings before returning the modified PDF from your extension.

For more information about writing custom rendering extensions go here http://msdn2.microsoft.com/es-es/library/ms153624.aspx

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
Gary
When you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegro ups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>
|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?
|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegr oups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
GaryWhen you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
--
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegroups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
--
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegroups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
GaryWhen you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegroups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegroups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>