Advice on developing a mobile app that uses SQL 2005 CE

Hi,

I am new to SQL 2005 CE but fairly experienced with SQL 2000 and know my way around SQL 2005. I am creating a PDA app that must support the following scenario:

My company has a SQL 2000 server with a "computer assets" database. This asset database must be able to be transferred to an .sdf file (SQL 2005 mobile) on the PDA when the user docks their PDA to the desktop PC (not the same machine as the SQL 2000 server). This transfer can be via an option in the mobile app that is initiated by the user, or automatic.

The mobile application basically supports stock-taking of the assets, so users go out on site and record information about the assets. This information is stored in a separate .sdf database on the PDA.

When the user gets back to the office they dock with their desktop and then must transfer the data from the second .sdf database onto the SQL 200 server. Lets call this the "asset metadata".

thats basically it! but im having issues finding the right process to attack the data transfer tasks.

My issues:

- Firstly, how do i populate the .sdf on the PDA for development purposes? Microsoft tutorials seem to be geared at SQL server 2005 management

studio only. I only have SQL server 2005 Management Studio Express on

my development machine as well as all the necessary SQL 2005 CE SDKs and VS2005. We

do not use SQL Server 2005 at my organisation as yet and it will be some time before we migrate across. In effect im looking for something that behaves like a DTS package to transfer data across.

- How to populate the "asset database" .sdf file from the SQL 2000 server when the user has docked to their desktop PC? (This needs to be refreshed )

- How to transfer the "asset metadata" from the .sdf on the PDA to the SQL server?

ive looked at merge replication a little but am not sure whether this is what i need and again most online information seem to be tailored to SQL Server 2005.

Can anyone kindly point me in the right direction?

I believe that merge replication will be the solution to your problems, as it will allow you to: Populate the device SDF, but for development and production purposes, and transfer inserted data from the device to the central SQL Server.

I would strongly recommend upgrading to SQL Server 2005, but SQL Server 2000 is a supported platform for merge synchronization with SQL 2005 CE.

The topics below this covers both SQL Server 2000 and 2005 configurations: http://msdn2.microsoft.com/en-us/library/ms171850.aspx

|||Hi Erik,

Thanks for your post. This article does indeed look like what i was searching for. ive been a bit busy on other work though and unable to dive into it thoroughly.

i will update once i have time to get back to the SQL mobile project again.|||

Hello again,


ive managed to setup everything as follows:

- created the publication for merge replication on the sql 2000 server.
- allowed the publication to have sql server CE subscrptions
- snapshot was created fine (at least sql server said everything was successful/)
- set up IIS for replication and this also went well. IIS is running on the same physical server as SQL 2000

So i then added some code to the applicaiton on my pocket PC, and it seems to be working except that the database on the local device doesnt seem to get any data added to it.

The following VB.Net code is what i am using (i have replaced server names etc with arbitrary values enclosed in < > for privacy reasons)

Try
' Set the Replication object.
repl = New SqlCeReplication()
repl.InternetUrl = "http://<myserver>/AssetSync/sqlcesa30.dll"
repl.Publisher = "<publisher name>"
repl.PublisherDatabase = "AssetRegister"
repl.PublisherLogin = "<login>"
repl.PublisherPassword = "<password>"
repl.Publication = "asset_list"
repl.SubscriberConnectionString = "Data Source=\Assets.sdf"
repl.Subscriber = "MySubscriber"

' Create the Local SSCE Database subscription.
repl.AddSubscription(AddOption.ExistingDatabase)

' Synchronize to the instance of SQL Server 2000 to populate the Subscription.
repl.Synchronize()

Catch ex As SqlCeException
' Use your own error handling routine to show error information.
outputLabel.Text = ex.ToString

Finally
outputLabel.Text = "Subscription Complete!"
' Dispose of the Replication object.
repl.Dispose()
End Try

There are no erros thrown and my application actually outputs "Subscription Complete!" .. but the database is not being populated. My VS2005 project has a blank Assets.sdf file in it.

i have also tried starting the app with no .sdf file and changing the subscription code to "repl.AddSubscription(AddOption.CreateDatabase)"

i am thinking there might be something wrong with the publication? but this behvaes the same - no database created.
|||

I would start by testing the publication from a desktop, namely via SQL Server 2005 management studio, or using this utility from Nick Randolph: http://www.softteq.com/tools/publish.htm

advice on creating a backup plan

Could someone give me some feedback on the backup plan that I have created
for my companies SQL Server. First a little background.
We have 1 win2k advance server running both IIS and our SQL Server. The
database held on the server is the backend to our e-commerce site. The db
needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
Atleast half of the tables in the db are modified through the day. There are
no large bulk insertions or deletions done through the day. The MDF and LDF
files are on different hd's.
I would like to create a plan so that in case of massive hardware failur we
could have a complete up-to-date copy of the db back up in less than 4
hours. I would like to backup up my sql databases and the Win2k system
state. The database recovery method is set to FULL.
The backup cycle starts at 6:00 am EST with a complete backup of the
database. Every 2 hours after the complete backup the transaction log is
backed up. Every 6 hours after the complete backup a Differential DB backup
is made.
At 5:00 am the next day those files are copied to another drive and the
cycle starts again with the complete db backup file overwriting itself.
This way I will always have a complete backup of the following day. I also
copy the backed up backups offline to a different server for archiving.
The system db's are backedup every week. Integrity checks are done every
week as well.
Is there anything else I should do like re create indexes as part of my
plan? How often should I back up the system state? Rigth now I back it up
once a week.
Any comments would be appreciated as preventing the loss of data is of the
utmost importance. Thanks in advance.The big thing I see regards WHERE the backups are kept...If you are leaving
them on the SQL box all day, change the plan... backup things to a different
box, and get the backups off of the SQL Server...
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
"news.microsoft.com" <cramsundar@.mail.com> wrote in message
news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> Could someone give me some feedback on the backup plan that I have created
> for my companies SQL Server. First a little background.
> We have 1 win2k advance server running both IIS and our SQL Server. The
> database held on the server is the backend to our e-commerce site. The db
> needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
> Atleast half of the tables in the db are modified through the day. There
are
> no large bulk insertions or deletions done through the day. The MDF and
LDF
> files are on different hd's.
> I would like to create a plan so that in case of massive hardware failur
we
> could have a complete up-to-date copy of the db back up in less than 4
> hours. I would like to backup up my sql databases and the Win2k system
> state. The database recovery method is set to FULL.
> The backup cycle starts at 6:00 am EST with a complete backup of the
> database. Every 2 hours after the complete backup the transaction log is
> backed up. Every 6 hours after the complete backup a Differential DB
backup
> is made.
> At 5:00 am the next day those files are copied to another drive and the
> cycle starts again with the complete db backup file overwriting itself.
> This way I will always have a complete backup of the following day. I
also
> copy the backed up backups offline to a different server for archiving.
> The system db's are backedup every week. Integrity checks are done every
> week as well.
> Is there anything else I should do like re create indexes as part of my
> plan? How often should I back up the system state? Rigth now I back it
up
> once a week.
> Any comments would be appreciated as preventing the loss of data is of the
> utmost importance. Thanks in advance.
>|||We do not have another box to back up the data too. We have a very small
operation. The backups reside on two different hds and will be copied off
the server.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:Oh4WL2pjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> The big thing I see regards WHERE the backups are kept...If you are
leaving
> them on the SQL box all day, change the plan... backup things to a
different
> box, and get the backups off of the SQL Server...
>
> --
> 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
>
> "news.microsoft.com" <cramsundar@.mail.com> wrote in message
> news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > Could someone give me some feedback on the backup plan that I have
created
> > for my companies SQL Server. First a little background.
> >
> > We have 1 win2k advance server running both IIS and our SQL Server. The
> > database held on the server is the backend to our e-commerce site. The
db
> > needs to be up 24-7 and typically sees the most usage from 9am - 11pm
EST.
> > Atleast half of the tables in the db are modified through the day. There
> are
> > no large bulk insertions or deletions done through the day. The MDF and
> LDF
> > files are on different hd's.
> >
> > I would like to create a plan so that in case of massive hardware failur
> we
> > could have a complete up-to-date copy of the db back up in less than 4
> > hours. I would like to backup up my sql databases and the Win2k system
> > state. The database recovery method is set to FULL.
> >
> > The backup cycle starts at 6:00 am EST with a complete backup of the
> > database. Every 2 hours after the complete backup the transaction log is
> > backed up. Every 6 hours after the complete backup a Differential DB
> backup
> > is made.
> >
> > At 5:00 am the next day those files are copied to another drive and the
> > cycle starts again with the complete db backup file overwriting itself.
> > This way I will always have a complete backup of the following day. I
> also
> > copy the backed up backups offline to a different server for archiving.
> >
> > The system db's are backedup every week. Integrity checks are done
every
> > week as well.
> >
> > Is there anything else I should do like re create indexes as part of my
> > plan? How often should I back up the system state? Rigth now I back it
> up
> > once a week.
> >
> > Any comments would be appreciated as preventing the loss of data is of
the
> > utmost importance. Thanks in advance.
> >
> >
>sql

Advice needed on creating a test environment

I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
Is it possible to take a backup of prod at the appropriate time and restore
it on your test box?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:78asg1pda4tvee5ug2kh5o5et09bu8m458@.4ax.com...
I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
|||On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:

>Is it possible to take a backup of prod at the appropriate time and restore
>it on your test box?
I don't think I can do that because a lot of my development is on
stored procedures. If I back it up and restore I'd lose my work so I
would have to save off my work, restore the backup and then recreate
my work. Right now I am thinking that my only solution is going to be
using DTS to copy all the data over after it does its nightly
retrieval of data from corporate (it is a moderate amount of data)
|||Hi,
I think in this case you may go for Transactional replication only for
Selected tables.
U can specify the time at which it needs to Copy the data from Production to
Developement.
Any help reply..
Regards,
Herbert
"Matthew Speed" wrote:

> I have built a sizable (for me anyway) SQL application for a client on
> which they now base their entire business. I need to set up a
> parallel installation of the database for development purposes, the
> idea being I can make changes to the development version without
> impacting day to day operations until it is confirmed that all changes
> are working correctly. My problem is this: the application gets
> information from their headquarters every night along with information
> from employees throughout the day. Every night the system performs an
> hour of batch processing on the combined data. In order for this
> sandbox to work, I need to be able to import into the test system an
> exact copy of the data only on which the various stored procedures are
> going to do their work.
> It makes sense that the time to copy the data over is after the
> poduction system finishes getting its data and before the batch
> processing starts.
> Is the best way to do this to create a bunch of DTS packages to
> replace the development data tables with the production using an
> append?
>
|||Why don't you store all your work do disk? I wouldn't dare to work in an environment where my latest
revision is in the database? There are also tools that can compare schemas and create a diff DDL
script based on that. This way, you have your source code in .sql files and after restoring the
backup, you just run that .sql file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:bm4tg15kphl0966uf4ie311ov86q72jgui@.4ax.com...
> On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote:
>
> I don't think I can do that because a lot of my development is on
> stored procedures. If I back it up and restore I'd lose my work so I
> would have to save off my work, restore the backup and then recreate
> my work. Right now I am thinking that my only solution is going to be
> using DTS to copy all the data over after it does its nightly
> retrieval of data from corporate (it is a moderate amount of data)
|||On Thu, 25 Aug 2005 16:34:37 -0400, Matthew Speed <mspeed@.mspeed.net>
wrote:
>I have built a sizable (for me anyway) SQL application for a client on
>which they now base their entire business. I need to set up a
>parallel installation of the database for development purposes, the
>idea being I can make changes to the development version without
>impacting day to day operations until it is confirmed that all changes
>are working correctly. My problem is this: the application gets
>information from their headquarters every night along with information
>from employees throughout the day. Every night the system performs an
>hour of batch processing on the combined data. In order for this
>sandbox to work, I need to be able to import into the test system an
>exact copy of the data only on which the various stored procedures are
>going to do their work.
>It makes sense that the time to copy the data over is after the
>poduction system finishes getting its data and before the batch
>processing starts.
>Is the best way to do this to create a bunch of DTS packages to
>replace the development data tables with the production using an
>append?
Well, is that the only change made to the production data, appends?
Probably not!
Herbert's suggestion to consider replication is a good one.
But, do you really need up-to-date data every day? Usually there is
development that goes on for some days or weeks (or months or years),
often to a different schema than production is using (!!!), and only
then is even somewhat up-to-date data needed for testing.
It's pretty easy to script out all your SPs, restore a backup of
production onto your test machine, then script the SPs back in. Or
keep the SPs in a separate database.
Or, arrange some DTS logic sufficient to do your updates on demand,
but probably more than just appends.
Lots of ways to go, but frankly, none of them is painless!
J.

Advice needed on creating a test environment

I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?Is it possible to take a backup of prod at the appropriate time and restore
it on your test box?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:78asg1pda4tvee5ug2kh5o5et09bu8m458@.4ax.com...
I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?|||On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>Is it possible to take a backup of prod at the appropriate time and restore
>it on your test box?
I don't think I can do that because a lot of my development is on
stored procedures. If I back it up and restore I'd lose my work so I
would have to save off my work, restore the backup and then recreate
my work. Right now I am thinking that my only solution is going to be
using DTS to copy all the data over after it does its nightly
retrieval of data from corporate (it is a moderate amount of data)|||Hi,
I think in this case you may go for Transactional replication only for
Selected tables.
U can specify the time at which it needs to Copy the data from Production to
Developement.
Any help reply..
Regards,
--
Herbert
"Matthew Speed" wrote:
> I have built a sizable (for me anyway) SQL application for a client on
> which they now base their entire business. I need to set up a
> parallel installation of the database for development purposes, the
> idea being I can make changes to the development version without
> impacting day to day operations until it is confirmed that all changes
> are working correctly. My problem is this: the application gets
> information from their headquarters every night along with information
> from employees throughout the day. Every night the system performs an
> hour of batch processing on the combined data. In order for this
> sandbox to work, I need to be able to import into the test system an
> exact copy of the data only on which the various stored procedures are
> going to do their work.
> It makes sense that the time to copy the data over is after the
> poduction system finishes getting its data and before the batch
> processing starts.
> Is the best way to do this to create a bunch of DTS packages to
> replace the development data tables with the production using an
> append?
>|||Why don't you store all your work do disk? I wouldn't dare to work in an environment where my latest
revision is in the database? There are also tools that can compare schemas and create a diff DDL
script based on that. This way, you have your source code in .sql files and after restoring the
backup, you just run that .sql file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:bm4tg15kphl0966uf4ie311ov86q72jgui@.4ax.com...
> On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote:
>>Is it possible to take a backup of prod at the appropriate time and restore
>>it on your test box?
> I don't think I can do that because a lot of my development is on
> stored procedures. If I back it up and restore I'd lose my work so I
> would have to save off my work, restore the backup and then recreate
> my work. Right now I am thinking that my only solution is going to be
> using DTS to copy all the data over after it does its nightly
> retrieval of data from corporate (it is a moderate amount of data)|||On Thu, 25 Aug 2005 16:34:37 -0400, Matthew Speed <mspeed@.mspeed.net>
wrote:
>I have built a sizable (for me anyway) SQL application for a client on
>which they now base their entire business. I need to set up a
>parallel installation of the database for development purposes, the
>idea being I can make changes to the development version without
>impacting day to day operations until it is confirmed that all changes
>are working correctly. My problem is this: the application gets
>information from their headquarters every night along with information
>from employees throughout the day. Every night the system performs an
>hour of batch processing on the combined data. In order for this
>sandbox to work, I need to be able to import into the test system an
>exact copy of the data only on which the various stored procedures are
>going to do their work.
>It makes sense that the time to copy the data over is after the
>poduction system finishes getting its data and before the batch
>processing starts.
>Is the best way to do this to create a bunch of DTS packages to
>replace the development data tables with the production using an
>append?
Well, is that the only change made to the production data, appends?
Probably not!
Herbert's suggestion to consider replication is a good one.
But, do you really need up-to-date data every day? Usually there is
development that goes on for some days or weeks (or months or years),
often to a different schema than production is using (!!!), and only
then is even somewhat up-to-date data needed for testing.
It's pretty easy to script out all your SPs, restore a backup of
production onto your test machine, then script the SPs back in. Or
keep the SPs in a separate database.
Or, arrange some DTS logic sufficient to do your updates on demand,
but probably more than just appends.
Lots of ways to go, but frankly, none of them is painless!
J.

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.
|||Did anyone figure out how to resolve this? I'm having the same problem....
|||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.
|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.|||Did anyone figure out how to resolve this? I'm having the same problem.... |||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.
|||Did anyone figure out how to resolve this? I'm having the same problem....
|||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.
|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.|||Did anyone figure out how to resolve this? I'm having the same problem.... |||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.|||Did anyone figure out how to resolve this? I'm having the same problem.... |||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

Advanced SQL Generation Options in VS2005

Hi There

im not sure if im in the right place but i was wondering why the above option is greyed out when creating a new sql datasource. This occurs even when i select all fields within the table (including primary key fields)

Thanks
Todd

I'm not exactly sure which forum would be best for you - you could try this one ...

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Donald

|||Were you able to find an answer to this? I am having the same issue.|||not yet
|||Ive had another crack at this & worked it out.

For me i didnt have the primary key set in the sql database.

Added that & its Happy Days

Hope this helps.

Todd|||I have the same problem. I set the primary key in the table I'm trying to access to ArticlesID with the value of int, I select all the proper settings, but the Advanced SQL Options is still greyed out. I cannot seem to get this to work.|||Did anyone figure out how to resolve this? I'm having the same problem.... |||I found that you have to make absolutely certain that you set the primary key correctly by right clicking on the column in the database, then you have to make sure you don't click the "show unique records" checkbox.|||One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.|||

limonada wrote:

One thing I noticed is that the option is grayed out if the table used in the select statement has no primary key, which makes sense, since UPDATE and DELETE operations on a table without a primary key are very specific to the logic behing the table and cannot be generated automatically.

This is what I found as well and it's worth mentioning again as I spent ages before realising what I was doing wrong. It is not enough to just have a primary key in the table, the primary key column MUST be part of the data you are retrieving/selecting otherwise the "Generate INSERT, UPDATE and DELETE statements" option will be greyed out in the advanced menu.

|||

This happened to me too, and I realized that setting the Column Property "Identity Specification" to Yes for the intended Primary Key field will not alone make it as a Primary Key. You have to right click on the Column and select "Set Primary Key" to enable the Advanced SQL Generations Options.

Hope this information help with any newbie like me who made the same mistake. :-)

|||Pls set a primary key to a unique field,it will work.

Advanced Expression?

I'm creating a report that looks through detail records and want it to sum
amounts based on a record type. If FieldType = 1 to sum accounts in a range
otherwise to sum all records with that account. The result is an error
[BC30201] Expression expected. The formula I have so far is:
=iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
db.owner.table with (nolock) where Fields!account between
Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
I've also tried VB's Select/Case statement rather than IIF but it still
returns an error.
If this can't be done with expressions, can it be done someother way?
Thanks,
RickThis seems you might want to try creating a stored procedure and using the sp
as the basis of your report, not trying to calc that in a report cell. You
can't use a select statement from within the iif statement.
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick|||Mike,
Thanks for your reply. Are you referring to creating a stored procedure in
SQL or does RS have the ability to create stored procedures as well, like in
Report properties.custom code?
Since my post I came to the same conclusion you mention that what I'm trying
to do won't work in the reporting cell so I've been looking into creating a
function using custom code but as you mention, the select statement can't be
used within IIF. Can a stored procedure be called from IIF?
As a general custom code question, can C as well as VB code be used?
Thanks again!
Rick
"mike" wrote:
> This seems you might want to try creating a stored procedure and using the sp
> as the basis of your report, not trying to calc that in a report cell. You
> can't use a select statement from within the iif statement.
> "rickp3131" wrote:
> > I'm creating a report that looks through detail records and want it to sum
> > amounts based on a record type. If FieldType = 1 to sum accounts in a range
> > otherwise to sum all records with that account. The result is an error
> > [BC30201] Expression expected. The formula I have so far is:
> >
> > =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> > db.owner.table with (nolock) where Fields!account between
> > Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> >
> > I've also tried VB's Select/Case statement rather than IIF but it still
> > returns an error.
> >
> > If this can't be done with expressions, can it be done someother way?
> >
> > Thanks,
> > Rick|||You can try an SQL user defined function returning a table with summary
details, then use the IIF statement to get more specific, or you can use a
"Where" statement in your dataset
"rickp3131" wrote:
> I'm creating a report that looks through detail records and want it to sum
> amounts based on a record type. If FieldType = 1 to sum accounts in a range
> otherwise to sum all records with that account. The result is an error
> [BC30201] Expression expected. The formula I have so far is:
> =iif( Fields!FieldType.Value = 1, select sum( Fields!amount.Value) from
> db.owner.table with (nolock) where Fields!account between
> Fields!fromaccount.Value and Fields!toaccount.Value ,Sum(Fields!amount.Value))
> I've also tried VB's Select/Case statement rather than IIF but it still
> returns an error.
> If this can't be done with expressions, can it be done someother way?
> Thanks,
> Rick

ADO/VB6: Creating a primary key

Hi all,
I've narrowed down my problem to a key issue. If I don't create a key, then
ADO is happy. If I do, then I know get a complaint. I've whipped up the
code fragment below to illustrate my problem. I'm using ADO v2.8, although
v2.7 has the same problem.
Dim dbtblSQL as New ADOX.Table
Dim dbkeySQL as New ADOX.Key
With dbtblSQL
.Name = "New Table"
.Columns.Append "Col1", adInteger, 0
.Columns.Append "Col2", adInteger, 0
.Columns.Append "Col3", adInteger, 0
With dbkeySQL
.Name = "MyKey"
.Type = adKeyPrimary
.Columns.Append dbtblSQL.Columns("Col1") 'Problem line 1
.Columns("Col1").RelatedColumn = "Col1"
End With
.Keys.Append dbkeySQL
End With
Call dbcatSQL.Tables.Append(dbtblSQL)
VB6 now complains on problem line 1 stating "Object already in collection.
Cannot append", but If I don't "append", then the item really isn't in the
collection.
The question boils down to how to create a primary key related to a column
in a table, where the column already exists. Thanks in advance.
Martin A. Weinberger
ButterflyVista
http://www.butterflyvista.com/
(when responding to me by email, remove the "X_" from the beginning)
Hi Again,
I also tried the single line method. The code below still produces a "The
parameter is incorrect."
objTable.Columns("PrimaryKey_Field").Properties("A utoIncrement").Value =
True
objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field"
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.
|||I found the problem thanks to some outside help. :-) The problem turned out
to be that when you use the AutoIncrement attribute, you must append the
table before you can create keys on it. This little MS ommission cost me a
few days, but at least I learnt something, so I thought that I pass it on.
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.

ADO.NET Timeout Exception - I have tried everything

Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.
Im a creating a data set and populating it with a call to a store proc.
Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.
I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")
The error is:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" />
I have admin rights on that db.
I have set my command.timeout to 500.
If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.
If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.
I am using SQL2000 with vb.net in VS2003.
I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.
regards,
Stas K.(a.k.a Sorcerdon)I had a similar problem as you. WHat I did was to was to insert the result
s
of the SP to a static table and then I read the contents of the static table
with the dataadapter.
Instead of retrieving data from the SP -- see if you can invoke the SP with
a command object from your client app and have the SP insert rows into a
static table. Then look at the table in Query Analyzer to see if you got an
y
rows. If you did not get any rows then you may have a connection problem.
If you did get rows, then I would retrieve data from the static table.
Rich
"sorcerdon@.gmail.com" wrote:

> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)
>

ADO.NET Timeout Exception - I have tried everything

Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.

Im a creating a data set and populating it with a call to a store proc.

Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.

I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")

The error is:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" /
I have admin rights on that db.

I have set my command.timeout to 500.

If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.

If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.

I am using SQL2000 with vb.net in VS2003.

I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.

regards,
Stas K.(a.k.a Sorcerdon)(sorcerdon@.gmail.com) writes:
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.

How long time does take before you get the error? Since you say
that you've set the command timeout to 500, I expect it to take
three minutes, but I want to verify.

> If I use a DataReader with the same storeProc, it works fine.

Since DataAdapter.Fill more or less is just a wrapper on ExecuteReader,
this is funky. (Then again, that sounds like you have a workaround.)

I get the feeling that there is a blocking issue lurking here. When
you run DataAdapter.Fill and are waiting for it to timeout, run sp_who
from Query Analyzer and look for non-zero values in the Blk column.
In such case the spid in the Blk column blocks the spid on this row.

--
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,

Already done that. There are no locks.
I forgot to mention that in the original post.

regards,
Stas K (a.k.a Sorcerdon)|||Sorcerdon (sorcerdon@.gmail.com) writes:
> Already done that. There are no locks.
> I forgot to mention that in the original post.

I afraid then I don't have more suggestions with the information you have
given.

You could use Profiler to see if you get different query plans for
the different situations, althogh I don't see why that would happen.

Then again, there may be some pertinent information you have shared with
us.

--
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|||The problem is solved but a new mystery begins.
The problem was one of the parameters being passed is wrong.
but the mystery is that sql didnt return an error - it just froze...
intresting.

ADO.NET Timeout Exception - I have tried everything

Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.
Im a creating a data set and populating it with a call to a store proc.
Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.
I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")
The error is:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" />
I have admin rights on that db.
I have set my command.timeout to 500.
If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.
If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.
I am using SQL2000 with vb.net in VS2003.
I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.
regards,
Stas K.(a.k.a Sorcerdon)Another thought: Go to see the "SQL Server Logs" or the "Event Viewer" on th
e
Server machine to see if there is error occurred on hardware. Sometimes a
certain bad sector on disk can cause SQL Server timeout.
Just my 2 cents.
James
"sorcerdon@.gmail.com" wrote:

> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)
>|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> Hello!
> I am looking for someone who has solved this multi-million people's
> problem. EVERYONE seems to ahve this problem.
> Im a creating a data set and populating it with a call to a store proc.
> Its a complex stored proc with the end result as an insert to a temp
> table. Then I do a select from the temp table - in the store proc.
>
> I get the following sqlException error on the following line:
> DataAdapterName.Fill(DataSetName, "TableName")
>
> The error is:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
>
> My connectiong string looks like this:
> <add key="cnITDevWinUser" value="Data Source=server; Integrated
> Security=SSPI; Initial Catalog=dbname; pooling=false;connection
> reset=false;connection lifetime=5;min pool size=1;max pool
> size=10;connection timeout=120" />
>
> I have admin rights on that db.
>
> I have set my command.timeout to 500.
>
> If i run this same code in a windows application, it works fine.
> If I use a DataReader with the same storeProc, it works fine.
> If I run this same code on a simple selec (hello world), it also works
> fine.
>
> If I run this store proc in QueryAnalyzer it works fine and is done
> within 6 seconds.
> If I run this on a different machine it produces the same result.
>
> I am using SQL2000 with vb.net in VS2003.
>
> I have looked everywhere for the answer. I can't find it anywhere.
> PLEASE SOMEONE HELP.
>
> regards,
> Stas K.(a.k.a Sorcerdon)

ADO error creating database

im creating a ecommerce website, and i need to create a database, im using visual studio .net
and when i try to create a new database using VS.net i choose "use sql server authentication" and no matter what i type in for the login id, i get the following ado error

"ADO Error : ' Login failed for user "blank". Reason. Not associated with a trusted SQL server connection.'

thanks in advance.is the sql server on a different machine?|||no its on my machine. im using xp pro. is that ok? or am I trying to do something thats not possible?|||and you're sure you know a valid sql user name and password with decent rights, such as owner?|||just check ur connection string... It must contain clauses: uid=<the_login_to_ur_db>, pwd = <ur_password> and database=<ur_db_name>. If its not (this is really possible... I have encountered smth. like that...) then add it into connection string manualy divided with semicolon of course... It should help.

ado "sql authentication" connections affected by loss of domain controller

I have observed that a temporary loss of a domain controller can cause
problems creating new ado connections between a client machine running
ado and a separate sql server machine that are members of the domain.
I understand why this happens when creating connections with windows
authentication. What is a mystery is that it also sometimes effects
new connections that use "sql authentication". Below is a description
of my test scenario.

a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.

b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.

Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.

Thanks,
Frank"Frank" <frank@.policecentral.com> wrote in message
news:c148e27c.0401120604.7d0b3c0f@.posting.google.c om...
> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank

This is a complete guess, and may be an overly obvious question, but could
the 50% failure rate be due to unsuccessful name resolution, and not an
MSSQL login issue? So in the cases where the SQL authenticated connection
fails, can you still resolve the name of the SQL server? Certainly in
theory, losing the domain controller shouldn't affect SQL logins, provided
that the client can actually find the server to begin with.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4002fd9c$1_1@.news.bluewin.ch>...
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.c om...
> > I have observed that a temporary loss of a domain controller can cause
> > problems creating new ado connections between a client machine running
> > ado and a separate sql server machine that are members of the domain.
> > I understand why this happens when creating connections with windows
> > authentication. What is a mystery is that it also sometimes effects
> > new connections that use "sql authentication". Below is a description
> > of my test scenario.
> > a. Setup 3 machines.
> > i. one domain controller machine. windows 2000 based. I have tried
> > both a regular domain setup and a domain setup in compatibility mode.
> > ii. one sql server machine that is a member of the domain. windows
> > 2000.
> > iii. one client machine running an ado test program that communicates
> > with the sql server machine. I have tried both 2000 and xp.
> > b. Start your test ado program and create a connection.
> > c. Fire a query.
> > d. It should work.
> > e. Unplug the network cord on the domain controller.
> > f. Create a new connection and fire a new connection about a minute or
> > so after.
> > g. It should work. Apparently the client caches account information
> > from the domain controller for a certain amount of time. The time
> > seems to be shorter by default in xp than 2000.
> > h. Wait 20 minutes or more.
> > i. Create another new connection. You will notice a timeout error.
> > If your using windows authentication the timeout will happen 100% of
> > the time. That is to be expected. If your using sql authentication
> > the timeout seems to happen about 50% of the time. I can't explain it
> > other than some strange Microsoft bug. I speculate that it may be
> > some bug with the way ado caches connections. Perhaps a previously
> > setup windows authentication connection gets reused by a request for a
> > sql authentication connection. However I haven't been able to prove
> > it.
> > Any insight you can offer would be appreciated. I also have a test
> > program you can use to reproduce this behavior if you are interested.
> > Thanks,
> > Frank
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon

I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.

ado "sql authentication" connections affected by loss of domain controller

I have observed that a temporary loss of a domain controller can cause
problems creating new ado connections between a client machine running
ado and a separate sql server machine that are members of the domain.
I understand why this happens when creating connections with windows
authentication. What is a mystery is that it also sometimes effects
new connections that use "sql authentication". Below is a description
of my test scenario.
a. Setup 3 machines.
i. one domain controller machine. windows 2000 based. I have tried
both a regular domain setup and a domain setup in compatibility mode.
ii. one sql server machine that is a member of the domain. windows
2000.
iii. one client machine running an ado test program that communicates
with the sql server machine. I have tried both 2000 and xp.
b. Start your test ado program and create a connection.
c. Fire a query.
d. It should work.
e. Unplug the network cord on the domain controller.
f. Create a new connection and fire a new connection about a minute or
so after.
g. It should work. Apparently the client caches account information
from the domain controller for a certain amount of time. The time
seems to be shorter by default in xp than 2000.
h. Wait 20 minutes or more.
i. Create another new connection. You will notice a timeout error.
If your using windows authentication the timeout will happen 100% of
the time. That is to be expected. If your using sql authentication
the timeout seems to happen about 50% of the time. I can't explain it
other than some strange Microsoft bug. I speculate that it may be
some bug with the way ado caches connections. Perhaps a previously
setup windows authentication connection gets reused by a request for a
sql authentication connection. However I haven't been able to prove
it.
Any insight you can offer would be appreciated. I also have a test
program you can use to reproduce this behavior if you are interested.
Thanks,
Frank"Frank" <frank@.policecentral.com> wrote in message
news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> I have observed that a temporary loss of a domain controller can cause
> problems creating new ado connections between a client machine running
> ado and a separate sql server machine that are members of the domain.
> I understand why this happens when creating connections with windows
> authentication. What is a mystery is that it also sometimes effects
> new connections that use "sql authentication". Below is a description
> of my test scenario.
> a. Setup 3 machines.
> i. one domain controller machine. windows 2000 based. I have tried
> both a regular domain setup and a domain setup in compatibility mode.
> ii. one sql server machine that is a member of the domain. windows
> 2000.
> iii. one client machine running an ado test program that communicates
> with the sql server machine. I have tried both 2000 and xp.
> b. Start your test ado program and create a connection.
> c. Fire a query.
> d. It should work.
> e. Unplug the network cord on the domain controller.
> f. Create a new connection and fire a new connection about a minute or
> so after.
> g. It should work. Apparently the client caches account information
> from the domain controller for a certain amount of time. The time
> seems to be shorter by default in xp than 2000.
> h. Wait 20 minutes or more.
> i. Create another new connection. You will notice a timeout error.
> If your using windows authentication the timeout will happen 100% of
> the time. That is to be expected. If your using sql authentication
> the timeout seems to happen about 50% of the time. I can't explain it
> other than some strange Microsoft bug. I speculate that it may be
> some bug with the way ado caches connections. Perhaps a previously
> setup windows authentication connection gets reused by a request for a
> sql authentication connection. However I haven't been able to prove
> it.
> Any insight you can offer would be appreciated. I also have a test
> program you can use to reproduce this behavior if you are interested.
> Thanks,
> Frank
This is a complete guess, and may be an overly obvious question, but could
the 50% failure rate be due to unsuccessful name resolution, and not an
MSSQL login issue? So in the cases where the SQL authenticated connection
fails, can you still resolve the name of the SQL server? Certainly in
theory, losing the domain controller shouldn't affect SQL logins, provided
that the client can actually find the server to begin with.
Simon|||Which network library are you using to establish the
connection? Named pipes will require an underlying
NetBIOS connection to the SQL Server, which may result in
the "cache-ing" behavior you are seeing. NetBIOS
connections will require NT user account authentication
and the use of a domain controller.
If you refer to the SQL Server by IP Address in your
connectino string, you can force the client to connect
using TCP/IP which does not require an underlying Named
Pipe connection.
I don't know if this will help, but it is one possible
explanation.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>I have observed that a temporary loss of a domain
controller can cause
>problems creating new ado connections between a client
machine running
>ado and a separate sql server machine that are members
of the domain.
>I understand why this happens when creating connections
with windows
>authentication. What is a mystery is that it also
sometimes effects
>new connections that use "sql authentication". Below is
a description
>of my test scenario.
>a. Setup 3 machines.
> i. one domain controller machine. windows 2000
based. I have tried
>both a regular domain setup and a domain setup in
compatibility mode.
> ii. one sql server machine that is a member of
the domain. windows
>2000.
> iii. one client machine running an ado test
program that communicates
>with the sql server machine. I have tried both 2000 and
xp.
>b. Start your test ado program and create a connection.
>c. Fire a query.
>d. It should work.
>e. Unplug the network cord on the domain controller.
>f. Create a new connection and fire a new connection
about a minute or
>so after.
>g. It should work. Apparently the client caches account
information
>from the domain controller for a certain amount of
time. The time
>seems to be shorter by default in xp than 2000.
>h. Wait 20 minutes or more.
>i. Create another new connection. You will notice a
timeout error.
>If your using windows authentication the timeout will
happen 100% of
>the time. That is to be expected. If your using sql
authentication
>the timeout seems to happen about 50% of the time. I
can't explain it
>other than some strange Microsoft bug. I speculate that
it may be
>some bug with the way ado caches connections. Perhaps a
previously
>setup windows authentication connection gets reused by a
request for a
>sql authentication connection. However I haven't been
able to prove
>it.
>Any insight you can offer would be appreciated. I also
have a test
>program you can use to reproduce this behavior if you
are interested.
>Thanks,
>Frank
>.
>|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4002fd9c$1_1@.news.bluewin.ch>...
> "Frank" <frank@.policecentral.com> wrote in message
> news:c148e27c.0401120604.7d0b3c0f@.posting.google.com...
> > I have observed that a temporary loss of a domain controller can cause
> > problems creating new ado connections between a client machine running
> > ado and a separate sql server machine that are members of the domain.
> > I understand why this happens when creating connections with windows
> > authentication. What is a mystery is that it also sometimes effects
> > new connections that use "sql authentication". Below is a description
> > of my test scenario.
> >
> > a. Setup 3 machines.
> > i. one domain controller machine. windows 2000 based. I have tried
> > both a regular domain setup and a domain setup in compatibility mode.
> > ii. one sql server machine that is a member of the domain. windows
> > 2000.
> > iii. one client machine running an ado test program that communicates
> > with the sql server machine. I have tried both 2000 and xp.
> >
> > b. Start your test ado program and create a connection.
> > c. Fire a query.
> > d. It should work.
> > e. Unplug the network cord on the domain controller.
> > f. Create a new connection and fire a new connection about a minute or
> > so after.
> > g. It should work. Apparently the client caches account information
> > from the domain controller for a certain amount of time. The time
> > seems to be shorter by default in xp than 2000.
> > h. Wait 20 minutes or more.
> > i. Create another new connection. You will notice a timeout error.
> > If your using windows authentication the timeout will happen 100% of
> > the time. That is to be expected. If your using sql authentication
> > the timeout seems to happen about 50% of the time. I can't explain it
> > other than some strange Microsoft bug. I speculate that it may be
> > some bug with the way ado caches connections. Perhaps a previously
> > setup windows authentication connection gets reused by a request for a
> > sql authentication connection. However I haven't been able to prove
> > it.
> >
> > Any insight you can offer would be appreciated. I also have a test
> > program you can use to reproduce this behavior if you are interested.
> >
> > Thanks,
> > Frank
> This is a complete guess, and may be an overly obvious question, but could
> the 50% failure rate be due to unsuccessful name resolution, and not an
> MSSQL login issue? So in the cases where the SQL authenticated connection
> fails, can you still resolve the name of the SQL server? Certainly in
> theory, losing the domain controller shouldn't affect SQL logins, provided
> that the client can actually find the server to begin with.
> Simon
I thought of that and tried doing a ping by name after it failed. It
worked ok. However given the 50% nature of the problem I still don't
totally rule it out.|||I considered that possibility. I did bring up the "Client Network
Utility" on the client and made tcp/ip the only choice. The behavior
did not change. However I am referencing the machine by name and not
ip. I will try the ip technique and report back.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> Which network library are you using to establish the
> connection? Named pipes will require an underlying
> NetBIOS connection to the SQL Server, which may result in
> the "cache-ing" behavior you are seeing. NetBIOS
> connections will require NT user account authentication
> and the use of a domain controller.
> If you refer to the SQL Server by IP Address in your
> connectino string, you can force the client to connect
> using TCP/IP which does not require an underlying Named
> Pipe connection.
> I don't know if this will help, but it is one possible
> explanation.
> Matthew Bando
> BandoM@.CSCTGI(remove this).com
>
> >--Original Message--
> >I have observed that a temporary loss of a domain
> controller can cause
> >problems creating new ado connections between a client
> machine running
> >ado and a separate sql server machine that are members
> of the domain.
> >I understand why this happens when creating connections
> with windows
> >authentication. What is a mystery is that it also
> sometimes effects
> >new connections that use "sql authentication". Below is
> a description
> >of my test scenario.
> >
> >a. Setup 3 machines.
> > i. one domain controller machine. windows 2000
> based. I have tried
> >both a regular domain setup and a domain setup in
> compatibility mode.
> > ii. one sql server machine that is a member of
> the domain. windows
> >2000.
> > iii. one client machine running an ado test
> program that communicates
> >with the sql server machine. I have tried both 2000 and
> xp.
> >
> >b. Start your test ado program and create a connection.
> >c. Fire a query.
> >d. It should work.
> >e. Unplug the network cord on the domain controller.
> >f. Create a new connection and fire a new connection
> about a minute or
> >so after.
> >g. It should work. Apparently the client caches account
> information
> >from the domain controller for a certain amount of
> time. The time
> >seems to be shorter by default in xp than 2000.
> >h. Wait 20 minutes or more.
> >i. Create another new connection. You will notice a
> timeout error.
> >If your using windows authentication the timeout will
> happen 100% of
> >the time. That is to be expected. If your using sql
> authentication
> >the timeout seems to happen about 50% of the time. I
> can't explain it
> >other than some strange Microsoft bug. I speculate that
> it may be
> >some bug with the way ado caches connections. Perhaps a
> previously
> >setup windows authentication connection gets reused by a
> request for a
> >sql authentication connection. However I haven't been
> able to prove
> >it.
> >
> >Any insight you can offer would be appreciated. I also
> have a test
> >program you can use to reproduce this behavior if you
> are interested.
> >
> >Thanks,
> >Frank
> >.
> >|||Thanks for the help. The mystery has been solved. I ended up opening
a ms support incident and they helped me figure it out. For those
that guessed dns server that was the right idea. However what made it
difficult to pin down is the following.
A. I tried as one person suggested connecting by ip address. I
thought this would eliminate dns issues from the equation. However as
the ms support guy informed me ado does a gethostbyaddr early in the
connection if you pass in an ip address. Therefore if the computer
can't do that your out of business.
B. I also thought that dns was not a factor because I could ping by
name after a connection failure and it would work. However the reason
it worked is because all the machines were on the same subnet and the
broadcast was what made it work. Once the dns server drops out the
first ping response was very slow. if you would do a ping immediately
after the response the 2nd ping would be fast. That behavior was what
was causing the randomness of the connection working. The first time
the connection would time out. However the attempt woke things up and
the second attempt would succeed.
Now that I know this I can route the important machines to a more
isolated and protected dns server or manually edit the hosts files to
eliminate the domain controller / dns controller that I can't control
from the equation.
frank@.policecentral.com (Frank) wrote in message news:<c148e27c.0401131411.32f26332@.posting.google.com>...
> I considered that possibility. I did bring up the "Client Network
> Utility" on the client and made tcp/ip the only choice. The behavior
> did not change. However I am referencing the machine by name and not
> ip. I will try the ip technique and report back.
> "Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message news:<062d01c3d9ec$f7e783c0$a001280a@.phx.gbl>...
> > Which network library are you using to establish the
> > connection? Named pipes will require an underlying
> > NetBIOS connection to the SQL Server, which may result in
> > the "cache-ing" behavior you are seeing. NetBIOS
> > connections will require NT user account authentication
> > and the use of a domain controller.
> >
> > If you refer to the SQL Server by IP Address in your
> > connectino string, you can force the client to connect
> > using TCP/IP which does not require an underlying Named
> > Pipe connection.
> >
> > I don't know if this will help, but it is one possible
> > explanation.
> >
> > Matthew Bando
> > BandoM@.CSCTGI(remove this).com
> >
> >
> >
> > >--Original Message--
> > >I have observed that a temporary loss of a domain
> controller can cause
> > >problems creating new ado connections between a client
> machine running
> > >ado and a separate sql server machine that are members
> of the domain.
> > >I understand why this happens when creating connections
> with windows
> > >authentication. What is a mystery is that it also
> sometimes effects
> > >new connections that use "sql authentication". Below is
> a description
> > >of my test scenario.
> > >
> > >a. Setup 3 machines.
> > > i. one domain controller machine. windows 2000
> based. I have tried
> > >both a regular domain setup and a domain setup in
> compatibility mode.
> > > ii. one sql server machine that is a member of
> the domain. windows
> > >2000.
> > > iii. one client machine running an ado test
> program that communicates
> > >with the sql server machine. I have tried both 2000 and
> xp.
> > >
> > >b. Start your test ado program and create a connection.
> > >c. Fire a query.
> > >d. It should work.
> > >e. Unplug the network cord on the domain controller.
> > >f. Create a new connection and fire a new connection
> about a minute or
> > >so after.
> > >g. It should work. Apparently the client caches account
> information
> > >from the domain controller for a certain amount of
> time. The time
> > >seems to be shorter by default in xp than 2000.
> > >h. Wait 20 minutes or more.
> > >i. Create another new connection. You will notice a
> timeout error.
> > >If your using windows authentication the timeout will
> happen 100% of
> > >the time. That is to be expected. If your using sql
> authentication
> > >the timeout seems to happen about 50% of the time. I
> can't explain it
> > >other than some strange Microsoft bug. I speculate that
> it may be
> > >some bug with the way ado caches connections. Perhaps a
> previously
> > >setup windows authentication connection gets reused by a
> request for a
> > >sql authentication connection. However I haven't been
> able to prove
> > >it.
> > >
> > >Any insight you can offer would be appreciated. I also
> have a test
> > >program you can use to reproduce this behavior if you
> are interested.
> > >
> > >Thanks,
> > >Frank
> > >.
> > >