CRecordset takes 10 seconds to open

Hi All

I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.

I have multiple CRecordset classes within my app. When I developed the app over MS Access I read somewhere that it was better to have a single database object that is shared by multiple recordsets. So I ended up with something like this -

Code Snippet

// In the class header - 2 CRecordset derived classes

CMyRecordSet m_MyRecordSet; // Connects to the database using

// its "GetDefaultConnect" string

CAnotherSet * m_pAnotherSet;

// Within a "CreateRecordSets" method

if (!m_MyRecordSet.IsOpen()) m_MyRecordSet.Open();

// This works fine

m_pAnotherSet = new CAnotherSet (m_MyRecordSet.m_pDatabase);

m_pAnotherSet->Open();

// This open takes 10s to execute,

// there are 10 rows of data in the table

I have about 6 recordsets that I create in this way, there is a tiny amount of data in the database. The open for the next record sets return immediately.

The same app over MS Access works fine - no delays. SQLServer Express and Access are both running on my development PC.

Can anyone tell me why this call takes so long and whether there is a way of avoiding it?

Subsequent calls to the database return immediately.

Thanks

Alec

SQL Server 2005 Express 9.00.3042.00

Microsoft SQL Server Management Studio Express 9.00.2047.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I thought that I had found the cause of this but I've only found out how to reproduce it.

If I change the character set to unicode in the Visual Studio 2005 project settings the problem goes away. (This is an MFC SDI form view application.) I had originally creating my sql database tables by exporting them from Access and this had created nvarchars. I changed these to varchars but still I get the 10s delay when the project is built with "No Character set" selected. I'm not sure what the side affects of leaving unicode selected may be so I don't really want to change this without understanding the underlying cause.

In the CRecorset code generated by visual studio it says

// The string types below (if present) reflect the actual data type of the

// database field - CStringA for ANSI datatypes and CStringW for Unicode

// datatypes. This is to prevent the ODBC driver from performing potentially

// unnecessary conversions. If you wish, you may change these members to

// CString types and the ODBC driver will perform all necessary conversions.

// (Note: You must use an ODBC driver version that is version 3.5 or greater

// to support both Unicode and these conversions).

My ODBC driver version is 2000.85.1117.00 - am I looking at bthe wrong version number? (this is from the ODBC create new datasource window).

I have varchars being loaded in CString so is there any conversion necessary? (even if there was this convserion should not take so long).

Can anyone explain the delay related to selected charatcter set?

Thanks

Alec

creation of table that type of format........

hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
-------------
empid ph
-- ----------
office Mobile home
--- --- ---
100 9380768532 98455555 98822213

--------------

i want above type of format and then how to insert into values that
phone table . please help me.surya (suryaitha@.gmail.com) writes:
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:
> Phone table
> -------------
> empid ph
> -- ----------
> office Mobile home
> --- --- ---
> 100 9380768532 98455555 98822213
> --------------
> i want above type of format and then how to insert into values that
> phone table . please help me.

The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).

--
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|||"surya" <suryaitha@.gmail.com> wrote in news:1144477944.400250.34550
@.z34g2000cwc.googlegroups.com:

> hello all
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

Creating VirtualDeviceSet for Remote SQL Server 2005 failed.

Hi, all!

I am writing the VDI application to allow backup/restore MS SQL Server 2005. I would want to backup/restore remote servers as well as local. I have local instance of MS SQL 2005 and remote. Local instance has MSSQL2005_ZORG instance name, remote uses default (so it supposed to be MSSQLSERVER).

While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2".

When i try to create VIrtualDeviceSet via CreateEx i pass "MSSQL2005_ZORG" as lpInstanceName parameter and all works fine. But I could't create same device set for remote 'MSSQLSERVER' instance. I passed any combination for that, such as "VM2000SRVZ2\MSSQLSERVER", "MSSQLSERVER", "VM2000SRVZ2", "\\VM2000SRVZ2\MSSQLSERVER" and so on. No luck always get VD_E_INSTANCE_NAME (0x80770007).

Any idea?

--Thanks

Hi,
As per the Microsoft Virtual Backup specifications, VDI can be used only on local machines.You can download the specifications from

http://www.microsoft.com/downloads/details.aspx?familyid=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&displaylang=en

If u have used VDI for some time, i need ur help.Relpy if you can

Creating GUI with SSIS or Passing parameter(s) in SSIS

Hello All!

I have two questions to ask in this one thread. I would appreciate any feedback.

1. Is it possible to create GUI from SSIS using macro so that it can display forms or dialogs? If so how can I create a form that can be used to pass the parameters for the execution of the SSIS?

2. Is it possible to pass parameter(s) to SSIS? If yes, how can we do it...Please provide me with any example.

I wait to hear from you!

Thanks,
Niben

1. Yes, you can use the Script task to show forms and gather user input. But you shouldn't. See #2 for information on the better approach

2. You can set values in an SSIS package from an external source in several ways. Configurations are an option that allow you to store values in an XML file or database table. These values are read in at run-time. These are used primarily for things like connection strings, and other relatively static information that may change once in a while, or when you move from test to production.

You can also set the value of a variable or property of the package when you execute it by using the /SET option of DTEXEC. This method is preferred for values that change each time the package is executed. In the case that you need user input for your package, I would suggest gathering it up front by creating a GUI in your choice of languages, then pass the values collected to the SSIS package using the /SET command.