Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi
>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and
FrontPage developer.
>With these two apps, I have a "publish to the web"
mentality, so I have
>envisioned having my development SQL Server database on
my local harddisk
>and periodically "publishing" it to my production SQL
Server out on the
>Internet. (By development server, I mean where data
creation and update is
>accomplished.)
>My idea: Update the production SQL Server periodically
(weekly) from the
>development server, but I need to capture data received
from users on the
>production server, then edit and merge on the development
server for later
>republishing to the production server. Alternatively, all
data updates could
>be done on the production server with backups to the
local harddisk server.
>Problem: My Internet SQL Server provider does NOT allow
replication nor the
>creation or implementation of DTS packages, but does
support the DTS
>import/export functions to a point of connection to and
selection of my
>database.
>Which would be best approach to implement given the ISP's
restrictions? Or
>can you suggest another approach?
>Many thanks for your expertise to point me in the right
direction.
>Charax
>
>.
>|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...
> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchisql

Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi

quote:

---

>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and

---

FrontPage developer.

quote:

---

>With these two apps, I have a "publish to the web"

---

mentality, so I have

quote:

---

>envisioned having my development SQL Server database on

---

my local harddisk

quote:

---

>and periodically "publishing" it to my production SQL

---

Server out on the

quote:

---

>Internet. (By development server, I mean where data

---

creation and update is

quote:

---

>accomplished.)
>My idea: Update the production SQL Server periodically

---

(weekly) from the

quote:

---

>development server, but I need to capture data received

---

from users on the

quote:

---

>production server, then edit and merge on the development

---

server for later

quote:

---

>republishing to the production server. Alternatively, all

---

data updates could

quote:

---

>be done on the production server with backups to the

---

local harddisk server.

quote:

---

>Problem: My Internet SQL Server provider does NOT allow

---

replication nor the

quote:

---

>creation or implementation of DTS packages, but does

---

support the DTS

quote:

---

>import/export functions to a point of connection to and

---

selection of my

quote:

---

>database.
>Which would be best approach to implement given the ISP's

---

restrictions? Or

quote:

---

>can you suggest another approach?
>Many thanks for your expertise to point me in the right

---

direction.

quote:

---

>Charax
>
>.
>

---

|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...

quote:

---

> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchi

---

Appropriate Time Out for Alter Database?

Platform: SQL Server 2000 or higher

Scenario: We have an application which is going to perform some changes to a database, and for those changes to be safe, we want to set the database to single_user mode. We'll be executing commands like:

ALTER DATABASE <DbName> SET READ_ONLY WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DbName> SET MULTI_USER

etc.

Is 15 seconds a reasonable timeout for the alter commands? Does database size come into play when considering the time out for such commands? Is there a good rule of thumb for this sort of thing?

What exactly are you changing in the database, or are you talking changing tables?

Generally, alter database and alter table are "safe" to do on a running database. You do not need to set it into single user mode.

Yes, using alter table, size of the table being altered is a factor in how long it will take to complete.

Using alter database is almost instant unless you are creating a new huge file.

|||

Ultimately, it's a large number of operations, which include adding data, removing data, altering table definitions, etc. It's a process to change a schema, and the potential quantity of items is sufficient that we want to make sure that none of our users can make any changes while the process is running, which is why we want to set it into single user mode.

It sounds like giving the alter database command 15 seconds is generous enough, then. In my tests, I've seen it take up to 2 seconds, but the database I was testing on is a little bit smaller than our average database.

|||In that case, you should just use a transaction. That way it will be all or nothing and it will lock what it needs to do.

|||

Well, the scope of the project I'm doing doesn't actually allow me to explore modifying this particular aspect of the solution. One of my tasks is to determine if a 15 second time out is reasonable or not, and having no luck finding the answer to that question anywhere else, I came here to see if there were any guidelines I could follow, for making that determination.

The "just use transaction" answer implies that there are no conceivable situations where it'd be appropriate to set the database to single user mode. I think that there probably are situations where it'd be appropriate, though I can't site them offhand. I'd probably need to ask more experienced SQL experts to give me an example where it'd be appropriate. It may be that my assumption is wrong, and that I should just be using transactions, and I guarantee that the next project I have where I can make that choice, I will use transactions. For this project, I'm required to set the database in single user mode, and I suspect that the only part of the TSQL that I need to worry about taking a long time is the "ROLLBACK IMMEDIATE," because that's going to depend on the number of transactions at the time that need to be rolled back.

|||I don't understand your question about "time" to change to single user mode. I don't know how you intend to run your alter database commands. Assuming you are running a script, if you either add the single user mode command to the beginning or make a batch file which ran osql and the alter database to single user, then the run the rest of the update script, then the updates would not run until the single user mode command was done. Or just run the command manually and wait for it to finish.

Yes, the rollback is what will take the time, it might even fail. I have had times, when it is unable to rollback very large transactions, and it just hangs. Depending on your usage and situation at the time, it might be 2 seconds or it make never succeed.

Appointment Finder Query...TSQL Expert Required!

We are designing a booking system and have a list of appointments in a single table with the start and end time of the appointment.

Is there any way to write a query to return the available time gaps, I'm thinking here that we would need to generate a temporary table in the query and return the data from this table that doesn't join with data in the appointment table but I'm guessing and was wondering whether there's an easier way..

The table definition is

CREATE TABLE [dbo].[SALON_Appointment] (
[AppointmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[StylistID] [int] NOT NULL ,
[Recurrency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstStartTime] [datetime] NOT NULL ,
[FirstEndTime] [datetime] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO

Thanks in advance

Hi

You don't say what version of SQL Server you are using and there are a number of pieces of information/constraints missing.

These question include:

What is the length of the working day - does midnight to 07:00 counts as free time? Does the system need to allow for breaks such as lunch time? You could book them as dummy appointments (probably need an appointment type field to allow them to be stripped out under certain circumstances). What is the minimum length for an appointment - is 09:30 to 09:35 a valid gap? What days do you need this for: All Days; Working Days; Days for which appointments exist.|||

I assume that you wana find the gap based on stylistID.

Code Snippet

SELECT A1.StylistID,

DATEDIFF(mi,A1.EndTime, A2.StartTime) AS Gap

FROM Appointment A1 JOIN Appointment A2 ON (A1.StylistID=A2.StylistID)

WHERE (A1.EndTime=

(SELECT MAX(A3.EndTime)

FROM Appointment A3

WHERE (A3.StylistID = A1.StylistID)

AND (A3.EndTime <= A2.StartTime)))

Hope this helps,

Paraclete

|||Hi,

Thanks for the reply.
I think I've worked a solution out which is similar to your method. I create a temporary and fill it with psuedo appointments, I then do a select which uses a sub query with NOT EXISTS to select items from the pseudo table that don't exist in the real appointment table and hence returns a list of available slots.

It looks like a little less code than your method but I've not tested it fully so I'm not sure whether its 100% yet.

Applying the Schema Snapshot and Partitioned Snapshot to a NewSubscriber

If client is synchronizing for first time with server at that time
Snapshot is applied to the client database. If meanwhile application
crashes or network goes down then snapshot is partially applied. When
i am restarting my application and trying to synchronize again my
server database gets corrupted (i.e. some rows get deleted from
central server)
So when a new subsciber is synchronising for first time and meanwhile
the process crashes then what will happen and how to resolve it.
On Nov 15, 12:07 pm, Manish <jainmani...@.gmail.com> wrote:
> If client is synchronizing for first time with server at that time
> Snapshot is applied to the client database. If meanwhile application
> crashes or network goes down then snapshot is partially applied. When
> i am restarting my application and trying to synchronize again my
> server database gets corrupted (i.e. some rows get deleted from
> central server)
> So when a new subsciber is synchronising for first time and meanwhile
> the process crashes then what will happen and how to resolve it.
Currently I done following and problem disappeared. But pls suggest
other options.
When i observed replication related system tables i decided to kill
processed to related to
string sql = "select spid from master.dbo.sysprocesses WHERE
program_name like '" + getMergeAgentProgramName() + "%'"; where
getMergeAgentProgramName() is as follows
private string getMergeAgentProgramName()
{
return mPublisher + "-" +
mPublicationDatabase + "-" +
mPublication + "-" +
mSubscriber;
}
the query will get processes related to replication
after that i killed each process selected under above sql query.
foreach (string spid in spIds)
{
sql = "kill " + spid;
publisherConn.ExecuteNonQuery(sql);
}
but the solution seems un scalable and may be unreliable.

Applying SP3

Hi

I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.

Could anybody tell me what and where is the impact when we install SP3..??

I would appreciate any kinda help.

Rgds

WilsonMake a backup of any production database (including master and msdb) before you apply sp3 (just to be paranoid). Apply the service pack, reboot NT, and go merrily on your way. No rebuilding or other "care and feeding" needed unless something goes dreadfully wrong during the application of the service pack.

-PatP|||just to be paranoid

I thought that was part of the job...

Oh, and someone once told me...in response to

"Do you think everyones out to get you? Are you Paranoid?"

"You're only Paranoid if you're wrong"

So what's the downside?

Application Role & 2nd Database

I'm trying to use application roles for the 1st time and have a problem.
Most of the select statements I use reference tables in a separate database.
After reading the BOL I find that this only works through the GUEST account.
Drat!
Any suggestions to fix this? I'm considering setting up views in the local
DB, but this will involve changing the code and creating the views and I
still don't know if it will work.
--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher college of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309Creating referencing views will work fine. If you don't want to grant
permissions on the underlying tables to guest or public, you can use an
unbroken cross-database ownership chain so that the app role needs only
permissions on views in the application role database.
To maintain an unbroken chain, the owners of the objects involved need to
map to the same login. If your objects are owned by 'dbo', this
necessitates that the owners of both databases be the same. Also, the
cross-database chaining option (introduced in SQL 2000 SP3) needs to be
enabled for those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Thanks, I can run it fine now from Query Analyzer, but not my app... more
work to do there.
I had to set the DB owners as the same account and a few other tasks. The
following MSDN article helped:
http://msdn.microsoft.com/library/d.../>
up_1cj5.asp
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ecqxs2D8DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Creating referencing views will work fine. If you don't want to grant
> permissions on the underlying tables to guest or public, you can use an
> unbroken cross-database ownership chain so that the app role needs only
> permissions on views in the application role database.
> To maintain an unbroken chain, the owners of the objects involved need to
> map to the same login. If your objects are owned by 'dbo', this
> necessitates that the owners of both databases be the same. Also, the
> cross-database chaining option (introduced in SQL 2000 SP3) needs to be
> enabled for those databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Correction......
I did not get the Application role to work across Databases. I continue to
receive the error "Server user 'price_9' is not a valid user in database
'FCoB_Contacts'."
I've tried both
EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
and
EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
Both DBs are owned by the same account.
Drat!
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Since an application role is only known in a single database, you need to
enable the 'guest' user in the other database so you have a security context
in the other database. No guest user permissions need to be granted. For
example:
Use MyOtherDatabase
EXEC sp_adduser 'guest'
Also, you don't need to enable cross-database chaining at the server level.
Your can specify it at the database level for only the databases that need
this option turned on:
EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Correction......
> I did not get the Application role to work across Databases. I continue
to
> receive the error "Server user 'price_9' is not a valid user in database
> 'FCoB_Contacts'."
> I've tried both
> EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
> and
> EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
> EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
> Both DBs are owned by the same account.
> Drat!
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Thanks! That did the trick.
Do we need to be concerned with the existence of the "Guest" account?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
> Since an application role is only known in a single database, you need to
> enable the 'guest' user in the other database so you have a security
context
> in the other database. No guest user permissions need to be granted. For
> example:
> Use MyOtherDatabase
> EXEC sp_adduser 'guest'
> Also, you don't need to enable cross-database chaining at the server
level.
> Your can specify it at the database level for only the databases that need
> this option turned on:
> EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
> EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> to
problem.
I
>|||> Do we need to be concerned with the existence of the "Guest" account?
As long as you haven't granted additional permissions to guest or public,
guest user access is limited to default public role permissions. This
includes the ability to view meta data, (e.g. table aned column names) but
no access to user objects and data. Users will still need an account to
connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:OkTtG8j8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Thanks! That did the trick.
> Do we need to be concerned with the existence of the "Guest" account?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
to
> context
For
> level.
need
continue
database
with
> problem.
the
and
> I
>

Application need Date without Time

Hello,

We are managing industry standard application for solvent business need and it is working fine as per requirement but after replication we got some problem with date type data.

We have stored date data through application in dd/mm/yy format only, we didn't used time data so it is truncated in overall application but after implementation for replication (Merge Model) from multiple location some date field automatically get time with date value, so some time application not retrieving data from database owing to stored time with date.

How I can resolve this issue I didn't get any solution in short way.

Thanks in advance to every particapetience.

R.MallYou do know that datetime data always stores both. right?

If no time is supplied the default time is 0, or midnight.

Application for "Replaying" a Table

I'm on a quest to find a way to "replay" a selected time range of records
from a table. The purpose is to simulate the pace and content of the records
in the table exactly as they were parsed during run-time. The ideal solution
would read records from a source table and write them to a destination table
as per a specified pace, i.e clock speed, 1/2 clock speed, 4x clock speed.

Just looking to see if there's something available currently before I jump
in and develop my own solution.

Thanks,

ChrisYou can get about 85% of what you want from SQL Profiler, although it doesn't quite get you there without some help in the form of a small app... You might even be able to do this with a SQL script, but that would be much more of a challenge.

-PatP|||SQL Profiler isn't going to help him replay the activity. He wants some kind of load simulator.|||Yeah, a tool something like LoadRunner (http://www.mercury.com/us/products/performance-center/loadrunner/) would be ideal, but you can get better than 85% of the way there using SQL Profiler to record the data into a table, then a small app to play the captured activity into another database. Not everybody has deep enough pockets to make load testing tools a snap decision... Most of us need to budget for them, sometimes for a couple of years before we get the cool toys to play with. Having a simple kludge like I've described to show what you can do with this kind of tool makes it a lot easier to sell!

-PatP

appending two datetimes which were converted from strings

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanksthe time is converted from string.

One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:

DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime

SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'

--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

|||thanks for the answer.it worked.

i have another problem.

how can i convert a char(4) into a time where the datatype of the
column is datatime?

i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>
One method is to build a string containing both date and time in format is
'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a datetime
value. For example:
>
DECLARE
@.MyDate datetime,
@.MyTime datetime,
@.MyDateTime datetime
>
SET @.MyDate = '20061121'
SET @.MyTime = '12:13:14'
>
--concatenate date and time strings and assign to datetime data type
SET @.MyDateTime =
CONVERT(char(9), @.MyDate, 112) +
CONVERT(char(8), @.MyTime, 114)
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.

the time is converted from string.

thanks

|||paul_zaoldyeck wrote:

Quote:

Originally Posted by

how can i convert a char(4) into a time where the datatype of the
column is datatime?

Combine it with an appropriate date (today's date, a date from a
related record, whatever) in a similar fashion to the previous
answer. If you don't care about the date, then pick a fixed value
(e.g. January 1, 1970) and use that in all cases.|||The example below parses a string in hh:mm format into a datetime variable.
Note that a datetime always includes both date and time components so your
app will need to ignore the date part, if not needed. The date defaults to
'19000101'.

DECLARE
@.MyTimeString char(4),
@.MyDateTime datetime

SET @.MyTimeString = '1213' --hh:mm

SET @.MyDateTime = LEFT(@.MyTimeString, 2) + ':' + LEFT(@.MyTimeString, 2) +
':00'

SELECT @.MyDateTime

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
news:1164254851.864579.14760@.b28g2000cwb.googlegro ups.com...

Quote:

Originally Posted by

thanks for the answer.it worked.
>
i have another problem.
>
how can i convert a char(4) into a time where the datatype of the
column is datatime?
>
i think this is my only problem now.thanks
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

the time is converted from string.

>>
>One method is to build a string containing both date and time in format
>is
>'yyyymmdd hh:mm:ss'. SQL Server can then parse the string into a
>datetime
>value. For example:
>>
>DECLARE
> @.MyDate datetime,
> @.MyTime datetime,
> @.MyDateTime datetime
>>
>SET @.MyDate = '20061121'
>SET @.MyTime = '12:13:14'
>>
>--concatenate date and time strings and assign to datetime data type
>SET @.MyDateTime =
> CONVERT(char(9), @.MyDate, 112) +
> CONVERT(char(8), @.MyTime, 114)
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"paul_zaoldyeck" <niopaul@.yahoo.comwrote in message
>news:1164084180.584403.64710@.k70g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

can anyone teach me how to append to datetime data into one.
the first is a date and the 2nd one is a time.
>
the time is converted from string.
>
thanks
>

>

appending to the end of current file

Hello all! I have a dts package exporting a text file. I would like for the dts job to append to the end of the file each time it is ran, rather than overwriting it. Is there a simple solution for this?
ThanksDarren Green suggested in earlier posts that :
The text file provider does not support this. The simplest thing to do is to export to a new file, then use the DOS copy command to merge the files.

This can be done with the Execute Process Task, and any dynamic filename stuff can be handled with an ActiveX Script Task to change the connection and the Exec Proc Task at the same time.

copy FileOriginal.txt+FileNew.txt FileResult.txt

HTH|||That is what I am currently doing. I was hoping for something within the dts package. Thanks for you help.

val|||As suggested its not available using TEXT provider with DTS.
May be this is also other way around, is to export to an excel sheet and save that as text file.:)

Appending to a text file via DTS

DTS wizard is not allowing me to append the data to a text file. Every time I run DTS and choose the destination to be this text file (say A.txt), it overwrites the data. I have a table whose data I am dumping to a text file. I truncate the table, then get the data again into it and want to append it to the same text file. But I end up overwriting the text file with the new data.

Kindly let me know where I am going wrong.I am having the same issue as well... I thought I remember seeing an option checkbox somewhere to do that... but I cant seem to find it.
I doubt we are the only ones having to deal with this, so can someone please chime in with an answer or maybe a possible direction for us to follow? Thanks in advance... :)

App.Config files in a Custom Database Extension Class Library

Good Morning..

We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.

In our SDE class library we have included an App.Config file where we want to store configuration settings..

Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..

Here's our app.config file:

-

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<appSettings>

<add key="eventLogName" value="FocusDPEEventLog" />

</appSettings>

<connectionStrings>

<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"

providerName="System.Data.OracleClient" />

</connectionStrings>

</configuration>

-

Here's what our Immediate window Debugger is tellin' us about our configuration settings:

-

ConfigurationManager.AppSettings

{System.Configuration.KeyValueInternalCollection}

[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}

base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}

AllKeys: {Dimensions:[0]}<-incorrect should be 1

ConfigurationManager.ConnectionStrings

Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...

base {System.Configuration.ConfigurationElementCollection}: Count = 1

ConfigurationManager.ConnectionStrings[0]

{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString

base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}

ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Name: "LocalSqlServer"

ProviderName: "System.Data.SqlClient"

Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...

thanks..

Doug

Hi

I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?

Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.

App.Config files in a Custom Database Extension Class Library

Good Morning..

We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.

In our SDE class library we have included an App.Config file where we want to store configuration settings..

Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..

Here's our app.config file:

-

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<configSections>

</configSections>

<appSettings>

<add key="eventLogName" value="FocusDPEEventLog" />

</appSettings>

<connectionStrings>

<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"

providerName="System.Data.OracleClient" />

</connectionStrings>

</configuration>

-

Here's what our Immediate window Debugger is tellin' us about our configuration settings:

-

ConfigurationManager.AppSettings

{System.Configuration.KeyValueInternalCollection}

[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}

base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}

AllKeys: {Dimensions:[0]}<-incorrect should be 1

ConfigurationManager.ConnectionStrings

Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...

base {System.Configuration.ConfigurationElementCollection}: Count = 1

ConfigurationManager.ConnectionStrings[0]

{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString

base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}

ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Name: "LocalSqlServer"

ProviderName: "System.Data.SqlClient"

Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...

thanks..

Doug

Hi

I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?

Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.

Apostrophes and Dynamic SQL

I am trying to Ad-hoc query from a user input(internal app), where the user
enters in a name just as "Al's Game Crazy". Each time I do this, the
Apostrophe gets in the way. I have tried using the "char(39)" setup for wit
h
the like statement. But I keep getting and error.
Error returned:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Code:
set nocount on
Declare @.SQL_Where varchar(1000)
Declare @.Brand varchar(50)
Declare @.pos int
Declare @.SQL varchar(2000)
--Set @.Brand = 'Al' + char(39) +'s Game Crazy'
Set @.Brand = 'Al''s Game Crazy'
SET @.SQL_Where = ''
-- Check Brand
if Len(rtrim(ltrim(@.Brand))) > 0
BEGIN
SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
if @.pos > 0
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
Substring(@.Brand, @.pos + 1, len(@.Brand))
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand +
'%' + char(39) + ' and'
END
-- Remove the last part of the where clause filter " and"
SET @.SQL_Where = rtrim(@.SQL_Where)
SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
select @.Brand
select @.SQL_Where
SET @.SQL = 'select top 3000 * from view_ContactLocation where' + @.SQL_Where
select @.SQL
exec (@.SQL)
set nocount off
Any help or direction would be appreciated.
Thanks,
ScottTry replacing occurrances of the single apostrophe ' with double apostrophe
''
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||JT, I tried the modifiying the following:
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) + Substring(@.Brand,
@.pos + 1, len(@.Brand))
to
SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' + Substring(@.Brand, @.pos
+ 1, len(@.Brand))
There appears to be no difference. I get the same errors. I believe that
is where you were thinking about doing the double "'".
Thanks,
Scott|||Scott Heffron wrote:
> JT, I tried the modifiying the following:
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> to
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + '''' +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> There appears to be no difference. I get the same errors. I believe
> that is where you were thinking about doing the double "'".
> Thanks,
> Scott
No. He's talking about using the REPLACE function to replace the apostrophe
with two apostrophes:
SET @.Brand=REPLACE(@.Brand,'''',''')
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You need to double-up the apostrophe to pass it into the stored procedure.
So from the app, the call need to look like this:
EXEC dbo.MyProc @.Brand = 'Al''s Game Crazy'
This doubling-up allows the stored procedure to be called successfully, as
the double apostrophe acts as an escape mechanism instead of ending the
string early. Now when you get the string into the stored procedure, it's
back to a single apostrophe only. So, if you are trying to put this string
into a string inside the stored procedure, you need to double them up again.
Of course, inside of SQL, you need to escape all instances of a quote. So,
try:
SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' +
REPLACE(@.Brand, '''',''') +
'%' + char(39) + ' and' -- shudder... why add an AND unless you know there
is more WHERE?
Please see the following.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
I think with some work you could streamline this process quite a bit and
make it far less hairy-looking. I'm not going to address the @.sql and
@.sql_where, because I have no idea if and why end users are able to write
your sql statements for you. But for the wildcard search alone, you could
do this:
CREATE PROCEDURE dbo.SearchContacts
@.Brand VARCHAR(50) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 3000 <column list! don't use *>
FROM View_ContactLocation
WHERE Brand LIKE '%'+@.Brand+'%';
END
GO
"Scott Heffron" <ScottHeffron@.discussions.microsoft.com> wrote in message
news:C79CAF92-5466-4B06-B136-7C7970B93BBA@.microsoft.com...
>I am trying to Ad-hoc query from a user input(internal app), where the user
> enters in a name just as "Al's Game Crazy". Each time I do this, the
> Apostrophe gets in the way. I have tried using the "char(39)" setup for
> with
> the like statement. But I keep getting and error.
> Error returned:
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ''.
> Code:
> set nocount on
> Declare @.SQL_Where varchar(1000)
> Declare @.Brand varchar(50)
> Declare @.pos int
> Declare @.SQL varchar(2000)
> --Set @.Brand = 'Al' + char(39) +'s Game Crazy'
> Set @.Brand = 'Al''s Game Crazy'
> SET @.SQL_Where = ''
> -- Check Brand
> if Len(rtrim(ltrim(@.Brand))) > 0
> BEGIN
> SELECT @.pos = PATINDEX('%' + char(39) + '%', @.Brand)
> if @.pos > 0
> SET @.Brand = Substring(@.Brand, 1, @.pos - 1) + char(39) +
> Substring(@.Brand, @.pos + 1, len(@.Brand))
> SET @.SQL_Where = @.SQL_Where + ' Brand like ' + char(39) + '%' + @.Brand
> +
> '%' + char(39) + ' and'
> END
> -- Remove the last part of the where clause filter " and"
> SET @.SQL_Where = rtrim(@.SQL_Where)
> SET @.SQL_Where = substring(@.SQL_Where, 1, Len(@.SQL_Where) - 4)
> select @.Brand
> select @.SQL_Where
> SET @.SQL = 'select top 3000 * from view_ContactLocation where' +
> @.SQL_Where
> select @.SQL
> exec (@.SQL)
> set nocount off
>
> Any help or direction would be appreciated.
> Thanks,
> Scott
>
>|||Thanks, it worked.|||There are several columns that can be used in the where clause that is the
reason for the "and" at the end. Not all the columns are used. I did not
want to do a search on column1 = '%%' if nothing was in the @.column1
variable. I am expecting that is wasting query time and not needed. Sorry,
I used the "*" to save space. You are absolutely right on using the column
names.
What the variable will look like from the users application is "Al's Game
Crazy"
There are 9 different variables. Is it better to allow for a search on '%%'
or leave it out and create the where clause dynamically?
Thanks,
Scott|||> There are 9 different variables. Is it better to allow for a search on
> '%%'
> or leave it out and create the where clause dynamically?
Have you read the following article? I think it applies quite nicely to
your situation. It looks like a long read, but I think you'll be glad you
did it.
http://www.sommarskog.se/dyn-search.html|||Scott Heffron (ScottHeffron@.discussions.microsoft.com) writes:
> I am trying to Ad-hoc query from a user input(internal app), where the
> user enters in a name just as "Al's Game Crazy". Each time I do this,
> the Apostrophe gets in the way. I have tried using the "char(39)" setup
> for with the like statement. But I keep getting and error.
Rather than building the entire SQL string, use sp_executesql instead.
Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
an example. (Further below there is also an example that uses EXEC()
and deals with strings in a structured way.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973B3B229ACAYazorman@.127.0.0.1...

> Rather than building the entire SQL string, use sp_executesql instead.
> Look at http://www.sommarskog.se/dyn-search.html#sp_executesql for
> an example. (Further below there is also an example that uses EXEC()
> and deals with strings in a structured way.)
Other way is to use some tool that will do all job for you (provide your use
r
with friendly interface to create queries and generate SQL statement in resu
lt).
We prefer EasyQuery (http://devtools.korzh.com/eq/) but I think there are so
me
similar products even free ones.
With the best regards, Nik.

Anyway to recover delete stored proc?

Folks.

I screwed up big time, I deleted a very long and smart stored proc (pls
don't ask how).

Is there anyway I can recover it?

Any advice appreciated.

Tada.KoliPoki (rayone@.gmail.com) writes:
> I screwed up big time, I deleted a very long and smart stored proc (pls
> don't ask how).
> Is there anyway I can recover it?

Do you have a backup of the database? Or do you run the database with
full or bulk-logged recovery? In that case you might be able to.

If you don't have any backup and run with simple recovery, the procedure
has left for outer space.

Generally, all source code should be under version control. See the
database as the place where you have the binary representation of
the source.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA