Help Backup: February 2012

Monday, February 27, 2012

HELP: SqlCeConnection path error in .NET compact framework (Pocket PC Emulator)

Hi all,

I am trying to access my SQL Server database through SqlCeConnection:

cecon = new SqlCeConnection("Data Source=D:\\D_Drive\\csharppract\\nddbpda\\nddbpda\\nddbpdadatabase.sdf");

cecon.Open();

I am getting the following error:

System.Data.SqlServerCe.SqlCeException was unhandled
Message="The path is not valid. Check the directory for the database. [ Path = D:\\D_Drive\\csharppract\\nddbpda\\nddbpda\\nddbpdadatabase.sdf ]"
HResult=-2147467259
NativeError=25009
Source="SQL Server 2005 Mobile Edition ADO.NET Data Provider"
StackTrace:
at System.Data.SqlServerCe.SqlCeConnection.ProcessResults()
at System.Data.SqlServerCe.SqlCeConnection.Open()
at System.Data.SqlServerCe.SqlCeConnection.Open()
at nddbpda.frmCeMain.frmCeMain_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at nddbpda.Program.Main()

When I tried to get the path from which the database file is being accepted, I got a different path:

string path;

path = System.IO.Path.GetDirectoryName(

System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);

MessageBox.Show(path);

This code gives me this path:

\Program Files\nddbpda

Then I changed the path in my connection string to this:

cecon = new SqlCeConnection("Data Source=\\Program Files\\nddbpda\\nddbpdadatabase.sdf");

cecon.Open();

When I ran my application it worked (virtually), but the database is not getting updated (for obvious reasons).

What should I do now to correct this?

Thanks in advance.

Saswata.

Moving to SQL Server Compact Edition forum where it has got better chances of being answered.

-Thanks,

Mohit

|||You can only access files located on the emulator, not your local drives. You have to copy or create the sdf file in the emulator in the desired location. There is no C: drive or D: drive in the emulator, just a "\" root

HELP: SqlCeConnection path error in .NET compact framework (Pocket PC Emulator)

Hi all,

I am trying to access my SQL Server database through SqlCeConnection:

cecon = new SqlCeConnection("Data Source=D:\\D_Drive\\csharppract\\nddbpda\\nddbpda\\nddbpdadatabase.sdf");
cecon.Open();

I am getting the following error:

When I tried to get the path from which the database file is being accepted, I got a different path:

string path;
path = System.IO.Path.GetDirectoryName(
System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
MessageBox.Show(path);

This code gives me this path:

\Program Files\nddbpda

Then I changed the path in my connection string to this:

cecon = new SqlCeConnection("Data Source=\\Program Files\\nddbpda\\nddbpdadatabase.sdf");
cecon.Open();

When I ran my application it worked (virtually), but the database is not getting updated (for obvious reasons).

What should I do now to correct this?

Thanks in advance.

Saswata.

Did you ever find an answer to your problem? I am experiencing the very same thing.

If I reference a local sdf it works fine, but if I reference the sdf located on the CE device, I get the same error as you.
|||

Windows CE devices (including emulator – simulated device) don't support drive letters (see that "D:\" in the path above? That makes it invalid on CE), don't support relative paths (that is, all paths must be full and absolute) and can't see your desktop hard drive (not by drive letter anyway). Common way to get absolute path is to append database name to the path to the application itself as done above.

Naturally, database on desktop won’t be updated because device has no idea it even exists. It changes database on device file system and it’s up to you to copy changed database from device file system to desktop file system as needed.

Now to using database on device from desktop application: that won’t work because there’s no way to reference database on device in desktop application; device is not mapped as desktop file system. Active Sync explorer extension simply fakes it. Visual Studio does that via special remote provider which is not public. If you want to access database on device then copy it to local file system (e.g. using RAPI), access it, and then copy it back to device. See this.

|||

If u got any soln on above please share this wiht me

My ID is abhijeettidke@.gmail.com

HELP: SQL Server Wont Release Memory

Hi,

I am having an issue with SQL server that slows everything to a crawl,
and makes almost any query impossible to complete.

Here are the symptoms:
I stop SQL, I start SQL. The task manager shows SQL starting up and
quickly allocates 50 MB of memory. I then open SQL Manager and I can
open up the instance of SQL Server and I expand to see all of the
databases, memory rises to about 70MB allocated. I then expand a
database and view a list of tables. The memory allocation quickly
rockets to 860+ MB and any attempt to query they database results in
huge hang times.

I have MacAfee anti-virus in place and it is constantly scanning for
viruses, so I don't think it is a virus. The log files do not appear
to be large. I have done everything I know to do to resolve this. Any
help would be much appreciated.

Does anyone have any ideas?

Thank you for taking the time to read this.

Tod"Tod1d" <tod124@.icqmail.com> wrote in message
news:d3ce916b.0309160556.72dbc548@.posting.google.c om...
> Hi,
> I am having an issue with SQL server that slows everything to a crawl,
> and makes almost any query impossible to complete.
> Here are the symptoms:
> I stop SQL, I start SQL. The task manager shows SQL starting up and
> quickly allocates 50 MB of memory. I then open SQL Manager and I can
> open up the instance of SQL Server and I expand to see all of the
> databases, memory rises to about 70MB allocated. I then expand a
> database and view a list of tables. The memory allocation quickly
> rockets to 860+ MB and any attempt to query they database results in
> huge hang times.
> I have MacAfee anti-virus in place and it is constantly scanning for
> viruses, so I don't think it is a virus. The log files do not appear
> to be large. I have done everything I know to do to resolve this. Any
> help would be much appreciated.
> Does anyone have any ideas?
> Thank you for taking the time to read this.
> Tod

Have you tried excluding your MSSQL data folders (all the .mdf/.ndf/.ldf
files) from the virus scan? If the antivirus software is scanning a database
file when MSSQL starts up, then there could easily be a problem of some
kind.

http://support.microsoft.com/defaul...kb;en-us;309422

Simon|||[posted and mailed, please reply in news]

Tod1d (tod124@.icqmail.com) writes:
> I am having an issue with SQL server that slows everything to a crawl,
> and makes almost any query impossible to complete.
> Here are the symptoms:
> I stop SQL, I start SQL. The task manager shows SQL starting up and
> quickly allocates 50 MB of memory. I then open SQL Manager and I can
> open up the instance of SQL Server and I expand to see all of the
> databases, memory rises to about 70MB allocated. I then expand a
> database and view a list of tables. The memory allocation quickly
> rockets to 860+ MB and any attempt to query they database results in
> huge hang times.

McAfee could indeed be a culprit, as Simon suggested. You should
also check if you have your databases set to autoclose, and you should
also check that you don't have ODBC tracing on. (Check this from the
ODBC applet in the Control Panel.)

As for the memory, this behaviour is by design. SQL Server grasps as
much memory it can, because it thinks that memory is good for cache.
If there are other applications on the same machine, SQL Server will
yeild if they need memory, but it may not yield fast enough. You can
control how much memory SQL Server may use from the Memory tab in
Enterprise Manager.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

HELP: SQL Server Service can't start up!

I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
uses Windows XP Professional. I used Windows Authentication Mode (instead of
Mixed-Mode). SQL Server 2000 starts up successfully.
Then I installed on a standalone PC which is part of a workgroup "WORKGROUP"
(instead of a domain) and it uses Windows XP Professional. I selected
Windows Authentication Mode. There was a dialog for me to enter my login
details and it already had fields entered, eg:
Username: Owner
Password: <BLANK>
Domain: WORKGROUP
This standalone PC only has one user account "Owner" which is bounded to the
Administrator (the 1st account Windows XP creates for you). The password for
this account is blank. The installation process was successful. But when SQL
Server Service Manager tried to start my SQL Server, it says login failure.
Just wondering, does SQL Server 2000 doesn't like blank passwords and
auto-fails immediately? Or does it have to be on a domain? I cannot use
Mixed-Mode as my development application doesn't work with Mixed-Mode.
Andrew
ERROR.LOG and/or Event Viewer have some info?
Does the MSSQLSERVER Service start automaticaly and under Local System
account?
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:Ox$roroIHHA.816@.TK2MSFTNGP06.phx.gbl...
>I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
>uses Windows XP Professional. I used Windows Authentication Mode (instead
>of Mixed-Mode). SQL Server 2000 starts up successfully.
> Then I installed on a standalone PC which is part of a workgroup
> "WORKGROUP" (instead of a domain) and it uses Windows XP Professional. I
> selected Windows Authentication Mode. There was a dialog for me to enter
> my login details and it already had fields entered, eg:
> Username: Owner
> Password: <BLANK>
> Domain: WORKGROUP
> This standalone PC only has one user account "Owner" which is bounded to
> the Administrator (the 1st account Windows XP creates for you). The
> password for this account is blank. The installation process was
> successful. But when SQL Server Service Manager tried to start my SQL
> Server, it says login failure.
> Just wondering, does SQL Server 2000 doesn't like blank passwords and
> auto-fails immediately? Or does it have to be on a domain? I cannot use
> Mixed-Mode as my development application doesn't work with Mixed-Mode.
>
|||It doesn't have 2 be on a domain.
Test it by giving the local user account a password an restart the sql
service.
Greetz,
SQLWIZ
"Uri Dimant" wrote:

> Andrew
> ERROR.LOG and/or Event Viewer have some info?
>
> Does the MSSQLSERVER Service start automaticaly and under Local System
> account?
> "Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
> news:Ox$roroIHHA.816@.TK2MSFTNGP06.phx.gbl...
>
>
|||Hi
I'm only a novice in this field - but I had what appears to be the same
problem a few weeks back, where my SQL server service was logging in with
Windows authentication that wasn't the primary admin account (although it had
admin access). Anyway - due to a network enforced user login password
rotation - I had to change the login password and forgot that the SQL server
service was still associated with the older password. So the SQL server
service wasn't starting...login failure. As I was doing other work at the
time on the server it took a little while (longer than it should have ;-P) to
consider that it was the password change that stuffed the service startup. I
changed the authentication of the service to use the real admin
authentication (which remains outside of the network password rotation
scheme/policy) and the server services have started fine everytime since.
Cheers
Tim
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> It doesn't have 2 be on a domain.
> Test it by giving the local user account a password an restart the sql
> service.
> Greetz,
> SQLWIZ
> "Uri Dimant" wrote:

HELP: SQL Server Service can't start up!

HELP: SQL SERVER CONNECTION TIMED OUT

can someone please help me solve this problem.

i'm experiencing sql server error: the database controller returned an error status of sql server connection timed out.

i need some help badly.

thanksWhat is the code doing when it times out? Frequently this is a problem with running a long script that exceeds the timeout. What is the complete text of the exception message?

Don|||>>what is the code doing when it times out?
- the code is posting accounting transactions coming from our accounting system.

>>the complete error message?
- the database controller returned an error status of sql server connection timed out

thanks|||Regarding the complete error message, I mean the full page of stuff that you get by default in ASP.NET, including the full error message, stack trace, and everything else that probably looks useless on the page.

That said, how long is the process taking? You may have to extend the SQL connection time out and perhaps the page time out.

Don

Help: SQL Server 2K draginng system down?

Hello, apologies if this is the wrong place to post this.
We've got a dev server which is supporting multiple services for
testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
Although I cannot be certain, I believe since SQL Server 2000 was
installed these symptoms begain happening. The server it resides on
after a period of several days to a week becomes unresponsive. File
sharing throughput becomes slowed down, terminal services ceases
accepting connections, the server itself when logged into at the console
is very sluggish with responding to input. The mouse(USB) pointer is
lagged even. OS is Win2K Server.
The server is not under high load, as it is a development box but we are
concerned it may happen elsewhere if deployed.
Has anyone else had this happen and have they found a solution to it?
Thanks
JasonFirst question is do you have service pack 3 on the box? If not you may be
the recipient of the slammer.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||Yeah make sure you have SP3... I've got a small server at home, Active
Directory, Domain Controller, DNS, DHCP, Internet Portal, SQL Server, you
name it... - all fine for a limited number of users.
--
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
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||In article <#8QuHZriDHA.1940@.TK2MSFTNGP09.phx.gbl>,
rayhigdon@.higdonconsulting.com says...
> First question is do you have service pack 3 on the box? If not you may be
> the recipient of the slammer.
>
Sorry.
Yep, Windows is patched to SP4, SQL Server is patched to 3. Of course I
don't have any solid evidence that SQL Server is actually what caused
the problem either. I just suspect it is.
Thanks,
Jason

HELP: SQL Server 2000 will not start after full system restore

Hi,
I just did a full system backup, memory and hard disk reconfiguration,
and a full system restore of my SQL Server 2000 machine.
SQL Server refuses to start as a service giving the error message:
"could not start the MSSQLSERVER service on the local computer"
"Error 3: the system cannot find the path specified"
It however starts from the command prompt by simply executing:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
Any assistance welcome!Hi
Check what flags you have specified when you start it as a service.
John
"kim@.kasnet.com" wrote:
> Hi,
> I just did a full system backup, memory and hard disk reconfiguration,
> and a full system restore of my SQL Server 2000 machine.
> SQL Server refuses to start as a service giving the error message:
> "could not start the MSSQLSERVER service on the local computer"
> "Error 3: the system cannot find the path specified"
> It however starts from the command prompt by simply executing:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> Any assistance welcome!
>|||Hi John,
There are no flags specified as startup parameters... is there
anything else I should check?
Kim
On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Check what flags you have specified when you start it as a service.
>John
>"kim@.kasnet.com" wrote:
>> Hi,
>> I just did a full system backup, memory and hard disk reconfiguration,
>> and a full system restore of my SQL Server 2000 machine.
>> SQL Server refuses to start as a service giving the error message:
>> "could not start the MSSQLSERVER service on the local computer"
>> "Error 3: the system cannot find the path specified"
>> It however starts from the command prompt by simply executing:
>> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> Any assistance welcome!|||Hi
Check the registry
key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
You will find entries like:
"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
Server\\MSSQL\\data\\mastlog.ldf"
Check that these files/directories exists. Other possible parameters are
detailed in Books Online topic "sqlservr Application"
John
"velliott@.hdhopwood.com" wrote:
> Hi John,
> There are no flags specified as startup parameters... is there
> anything else I should check?
> Kim
> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Check what flags you have specified when you start it as a service.
> >
> >John
> >
> >"kim@.kasnet.com" wrote:
> >
> >> Hi,
> >>
> >> I just did a full system backup, memory and hard disk reconfiguration,
> >> and a full system restore of my SQL Server 2000 machine.
> >>
> >> SQL Server refuses to start as a service giving the error message:
> >>
> >> "could not start the MSSQLSERVER service on the local computer"
> >> "Error 3: the system cannot find the path specified"
> >>
> >> It however starts from the command prompt by simply executing:
> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >>
> >> Any assistance welcome!
> >>
>|||I checked the registry entries and the only difference is that there
are single backslashes in the directory path. All other path entries
in the registry are represented the same way.
If this is the problem, my logic tells me that the SQL Server should
not start from the command prompt anyway!
Is there a way to get the service startup process to indicate which
specific path it cannot find?
Kim
On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Check the registry
>key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
>You will find entries like:
>"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
>"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
>"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
>Server\\MSSQL\\data\\mastlog.ldf"
>Check that these files/directories exists. Other possible parameters are
>detailed in Books Online topic "sqlservr Application"
>John
>
>"velliott@.hdhopwood.com" wrote:
>> Hi John,
>> There are no flags specified as startup parameters... is there
>> anything else I should check?
>> Kim
>> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Hi
>> >
>> >Check what flags you have specified when you start it as a service.
>> >
>> >John
>> >
>> >"kim@.kasnet.com" wrote:
>> >
>> >> Hi,
>> >>
>> >> I just did a full system backup, memory and hard disk reconfiguration,
>> >> and a full system restore of my SQL Server 2000 machine.
>> >>
>> >> SQL Server refuses to start as a service giving the error message:
>> >>
>> >> "could not start the MSSQLSERVER service on the local computer"
>> >> "Error 3: the system cannot find the path specified"
>> >>
>> >> It however starts from the command prompt by simply executing:
>> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> >>
>> >> Any assistance welcome!
>> >>
>>|||Hi
The double backslashes are from an export of the registry check other
registry values such as
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup]
John
"velliott@.hdhopwood.com" wrote:
> I checked the registry entries and the only difference is that there
> are single backslashes in the directory path. All other path entries
> in the registry are represented the same way.
> If this is the problem, my logic tells me that the SQL Server should
> not start from the command prompt anyway!
> Is there a way to get the service startup process to indicate which
> specific path it cannot find?
> Kim
> On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Check the registry
> >key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
> >
> >You will find entries like:
> >"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
> >"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
> >"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
> >Server\\MSSQL\\data\\mastlog.ldf"
> >
> >Check that these files/directories exists. Other possible parameters are
> >detailed in Books Online topic "sqlservr Application"
> >
> >John
> >
> >
> >"velliott@.hdhopwood.com" wrote:
> >
> >> Hi John,
> >>
> >> There are no flags specified as startup parameters... is there
> >> anything else I should check?
> >>
> >> Kim
> >>
> >> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
> >> <jbellnewsposts@.hotmail.com> wrote:
> >>
> >> >Hi
> >> >
> >> >Check what flags you have specified when you start it as a service.
> >> >
> >> >John
> >> >
> >> >"kim@.kasnet.com" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> I just did a full system backup, memory and hard disk reconfiguration,
> >> >> and a full system restore of my SQL Server 2000 machine.
> >> >>
> >> >> SQL Server refuses to start as a service giving the error message:
> >> >>
> >> >> "could not start the MSSQLSERVER service on the local computer"
> >> >> "Error 3: the system cannot find the path specified"
> >> >>
> >> >> It however starts from the command prompt by simply executing:
> >> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >> >>
> >> >> Any assistance welcome!
> >> >>
> >>
> >>
>|||John,
The registry keys in error were:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
and
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
Both contained the DOS version of the path (eg.
C:\Program~1\Micro~2\...) to the relevant binaries.
I expanded the paths and the service started without error!
Many thanks for your patience and invaluable assistance!!!
Kim
On Thu, 27 Apr 2006 09:50:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>The double backslashes are from an export of the registry check other
>registry values such as
>[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup]
>John
>"velliott@.hdhopwood.com" wrote:
>> I checked the registry entries and the only difference is that there
>> are single backslashes in the directory path. All other path entries
>> in the registry are represented the same way.
>> If this is the problem, my logic tells me that the SQL Server should
>> not start from the command prompt anyway!
>> Is there a way to get the service startup process to indicate which
>> specific path it cannot find?
>> Kim
>> On Thu, 27 Apr 2006 00:04:01 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Hi
>> >
>> >Check the registry
>> >key[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
>> >
>> >You will find entries like:
>> >"SQLArg0"="-dC:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\master.mdf"
>> >"SQLArg1"="-eC:\\Program Files\\Microsoft SQL Server\\MSSQL\\log\\ERRORLOG"
>> >"SQLArg2"="-lC:\\Program Files\\Microsoft SQL
>> >Server\\MSSQL\\data\\mastlog.ldf"
>> >
>> >Check that these files/directories exists. Other possible parameters are
>> >detailed in Books Online topic "sqlservr Application"
>> >
>> >John
>> >
>> >
>> >"velliott@.hdhopwood.com" wrote:
>> >
>> >> Hi John,
>> >>
>> >> There are no flags specified as startup parameters... is there
>> >> anything else I should check?
>> >>
>> >> Kim
>> >>
>> >> On Tue, 25 Apr 2006 09:48:01 -0700, John Bell
>> >> <jbellnewsposts@.hotmail.com> wrote:
>> >>
>> >> >Hi
>> >> >
>> >> >Check what flags you have specified when you start it as a service.
>> >> >
>> >> >John
>> >> >
>> >> >"kim@.kasnet.com" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> I just did a full system backup, memory and hard disk reconfiguration,
>> >> >> and a full system restore of my SQL Server 2000 machine.
>> >> >>
>> >> >> SQL Server refuses to start as a service giving the error message:
>> >> >>
>> >> >> "could not start the MSSQLSERVER service on the local computer"
>> >> >> "Error 3: the system cannot find the path specified"
>> >> >>
>> >> >> It however starts from the command prompt by simply executing:
>> >> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
>> >> >>
>> >> >> Any assistance welcome!
>> >> >>
>> >>
>> >>
>>|||<kim@.kasnet.com> wrote in message
news:l0fs421805aj0doegq6e26ft224ll3mj99@.4ax.com...
> Hi,
> I just did a full system backup, memory and hard disk reconfiguration,
> and a full system restore of my SQL Server 2000 machine.
> SQL Server refuses to start as a service giving the error message:
> "could not start the MSSQLSERVER service on the local computer"
> "Error 3: the system cannot find the path specified"
> It however starts from the command prompt by simply executing:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> Any assistance welcome!
How did you do your system restore?
We had a situation once where files were robocopied from old hardware to
new. The problem with that was that some files took longer to copy than
others, and this messed up the 8.3 names of those blasted c:\Program
Files\Microsoft ... directories. For example, the SQL Server directory was
MICROS~4 on the old box, but MICROS~2 on the new box. We didn't notice this
until we started getting weird errors like you describe. When we checked
the registry, we found that many keys referenced those directories by 8.3
name. Fixing it was a headache.|||Hi Karen
The OP has found that the registry enties
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
and
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
had 8.3 files names in them, change them to the correct values solved her
problem.
John
"Karen Collins" wrote:
> <kim@.kasnet.com> wrote in message
> news:l0fs421805aj0doegq6e26ft224ll3mj99@.4ax.com...
> > Hi,
> >
> > I just did a full system backup, memory and hard disk reconfiguration,
> > and a full system restore of my SQL Server 2000 machine.
> >
> > SQL Server refuses to start as a service giving the error message:
> >
> > "could not start the MSSQLSERVER service on the local computer"
> > "Error 3: the system cannot find the path specified"
> >
> > It however starts from the command prompt by simply executing:
> > "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqlservr.exe"
> >
> > Any assistance welcome!
> How did you do your system restore?
> We had a situation once where files were robocopied from old hardware to
> new. The problem with that was that some files took longer to copy than
> others, and this messed up the 8.3 names of those blasted c:\Program
> Files\Microsoft ... directories. For example, the SQL Server directory was
> MICROS~4 on the old box, but MICROS~2 on the new box. We didn't notice this
> until we started getting weird errors like you describe. When we checked
> the registry, we found that many keys referenced those directories by 8.3
> name. Fixing it was a headache.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86E6F2AF-BD89-46C2-88BB-1329784382E3@.microsoft.com...
> Hi Karen
> The OP has found that the registry enties
> [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath]
> and
> [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance\Library]
> had 8.3 files names in them, change them to the correct values solved her
> problem.
Thanks for the update. Glad to hear she was able to fix the problem.

Help: SQL Server 2000 failed to restart after NT Server reboot

Hi,
We have a NT Server that's set to reboot every weekend. SQL server
2000 and SQL Server Agent were set to auto-restart after reboot. It's
been working fine for a long time.
But several weeks ago,both SQL server and SQL server agent didn't
restart after the reboot so all DTS jobs didn't run until we manually
restart the Server and the server agent. We didn't receive any error
message and event log didn't
have any error either. It did show the SQL server was shut down by
request and the server agent was stoped successfully. But there's no
log about the server starting and ready for client connection. The
services are set with local system account.
We have a utility to check the connection to the server and it came
back with error of "[Shared Memory]SQL Server does not exist or access
denied. [Shared Memory]ConnectionOpen (Connect())"
If the SQL server was never asked to restart by the OS, what could be
the reason and how can I fix it?
Thanks a lot.
Ping LiWhat are you getting with issuing the command OSQL -L at the
commandline. Did you try to restart the SQL Server service the Service
Panel in Windows ? If not, do so and see if the server is coming u and
if not what kind of error message you are getting here. There must be
some event log message if the SQl Server doesnt=B4start up properly
(even if it does there is an entry). Is your event log outta space ?
HTH, Jens Suessmeyer.|||Hi, Jens,
Thanks for your reply. I tried OSQL -L and the result is:
(local)
commserver\commvaultqinetix
digital
zpm
I have no idea what it means.
We were able to restart the SQL server every time it failed to start
automatically after reboot. There's no entry about the server at all.
The last entry is the sql server was shut down by request(we schedule
the task) and the server agent was stopped successfully.
There is no error about the event log out of space. Can you guess what
the possible reason for the failure? They were set to restart
automatically.
Thank you for your help.
Ping
I have no idea what it means.|||Hi
You may want to post the SQL Server log for when the server failed to
re-boot as this should have more information as to why it failed. You may
also want to see if anything was logged in the event log.
As something to test the server is available you may want to use SQL Ping
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 you will need a
processs that parses the output to see if the given server is there.
John
"pli@.umn.edu" <pingli@.gmail.com> wrote in message
news:1128568970.436836.215890@.f14g2000cwb.googlegroups.com...
> Hi,
> We have a NT Server that's set to reboot every weekend. SQL server
> 2000 and SQL Server Agent were set to auto-restart after reboot. It's
> been working fine for a long time.
> But several weeks ago,both SQL server and SQL server agent didn't
> restart after the reboot so all DTS jobs didn't run until we manually
> restart the Server and the server agent. We didn't receive any error
> message and event log didn't
> have any error either. It did show the SQL server was shut down by
> request and the server agent was stoped successfully. But there's no
> log about the server starting and ready for client connection. The
> services are set with local system account.
> We have a utility to check the connection to the server and it came
> back with error of "[Shared Memory]SQL Server does not exist or access
> denied. [Shared Memory]ConnectionOpen (Connect())"
> If the SQL server was never asked to restart by the OS, what could be
> the reason and how can I fix it?
> Thanks a lot.
> Ping Li
>|||Hi, John,
Thanks for your reply.
As my previous posts said, there weren't any event log in the event
viewer after the shut down. The last 2 log entries were: SQL Server
was shut down by request. SQL Server agent was stopped successfully.
I couldn't find any log or information specifying why the server failed
to reboot. I'm trying to find out what the possible causes are so that
I know which direction to go. But so far nothing from the server log
showed up. It just disappeared in nowhere.
Thanks again and hope you can help.
Ping

Help: SQL Server 2000 failed to restart after NT Server reboot

help: SQL report (2000) and window form(VS2003)

hi Gummy
I have one problem. I don't know how to embed a SQL report (2000) onto
window form(VS2003), I do some research, a lot of information is
VS2005. I think you are done before.
Can you give me some information or sample code.
I really need you help.
Thanks so much.
-AlberYour best choice for this is to use URL integration. Embed a IE browser
control, then create the appropriate URL for the reports you want to show. I
have done this and it works well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<lgxdo@.yahoo.com> wrote in message
news:1175188629.964581.98010@.y66g2000hsf.googlegroups.com...
> hi Gummy
> I have one problem. I don't know how to embed a SQL report (2000) onto
> window form(VS2003), I do some research, a lot of information is
> VS2005. I think you are done before.
> Can you give me some information or sample code.
> I really need you help.
> Thanks so much.
> -Alber
>|||Dear Bruce,
Thanks so much. Can you give me a simple code? I create a form and add
a button to the form. And :
-->
private AxSHDocVw.AxWebBrowser axWebBrowser1;
private void button1_Click_1(object sender, System.EventArgs e)
{
object o="";
axWebBrowser1.Navigate("http://gp-ecomm/reportserver/misc/
bbitems",ref o, ref o, ref o,ref o);
}
-->
debug Error:
An unhandled exception of type 'InvalidActiveXStateException' occurred
in axinterop.shdocvw.dll
I am no idea about this.:(
Can you fix it'
Thanks so much.
Alber|||What I have always done is embed an Web browser control in a form. That
isn't what you are doing in the supplied code below.
Also I suggest trying a URL in IE and make sure it works and just copy and
paste into your code and use that for a test.
Indeed, you can start off with showing any website first. If you google you
can find VB code that does this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<lgxdo@.yahoo.com> wrote in message
news:1175376498.129336.290910@.y66g2000hsf.googlegroups.com...
>
> Dear Bruce,
> Thanks so much. Can you give me a simple code? I create a form and add
> a button to the form. And :
> -->
> private AxSHDocVw.AxWebBrowser axWebBrowser1;
> private void button1_Click_1(object sender, System.EventArgs e)
> {
> object o="";
> axWebBrowser1.Navigate("http://gp-ecomm/reportserver/misc/
> bbitems",ref o, ref o, ref o,ref o);
> }
> -->
> debug Error:
> An unhandled exception of type 'InvalidActiveXStateException' occurred
> in axinterop.shdocvw.dll
> I am no idea about this.:(
> Can you fix it'
> Thanks so much.
> Alber
>

Help: SQL 2000 connection error(.NET 2.0, VS 2005)

Moderated by XIII, please use a normal font size for better readability:

for some reason, my web application can not connect to my SQL 2000

my application is based on .net 2.0 (IIS 5.1) using vs 2005workstation does not install any sql serversql 2000 locates at the remote server.the sql server can be connected via vs environment (data connnection), therefore, i think the connection string, server name and instance name is correcti have tried all the solution i can find online, but it is not workingLocalSQLServer connection string has been removed in GobalSettingalso remove all default connection string in web.config (<clear />)aspnet_regsql can register the sql 2000 db successfulvs 2005 can access and display the data in sql 2000 quite wellhas been reboot lots of times.still bug out

any body have any idea? thanks, it already costs me the whole day

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Can you telnet to the remote server (test TCP/IP)? And how about "net use\\servername\IPC$" the remote server (test Named pipe)? You can take a look at this post:

http://forums.asp.net/thread/1266057.aspx

|||

Thank Jay,

I can telnet to the remote server. Actually, I think the connection to the remote server is correct (in VS 2005 environment the data can be display quite well), the only problem is .NET 2.0 always think the remote server is SQL 2005. I have removed the localSQLServer connection string in web.config file (<clear />) but still not working.

|||

Then how about force to use TCP/IP to connect? You can do this by usingtcp:myinstance,portas Data Source, where port is the tcp port used the SQL instance service. Actually there should be no difference when connect to SQL2005 or SQL2000, you can take a look at this article which shows how to place membership on SQL2000:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

HELP: SP4 Version?

@.@.version of 8.00.2039 seems correct.
"Whispering Leaf" wrote:

> I am convinced we do not have SQL 2000 SP4 installed but the console says
I
> do. After a few failed installation of SP4 I cannot imagine it is truly
> installed.
> It says: 8.00.2039 (SP4)
> Do I REALLY have SP4?
>

HELP: SP4 Version?

I am convinced we do not have SQL 2000 SP4 installed but the console says I
do. After a few failed installation of SP4 I cannot imagine it is truly
installed.
It says: 8.00.2039 (SP4)
Do I REALLY have SP4?
:)@.@.version of 8.00.2039 seems correct.
"Whispering Leaf" wrote:
> I am convinced we do not have SQL 2000 SP4 installed but the console says I
> do. After a few failed installation of SP4 I cannot imagine it is truly
> installed.
> It says: 8.00.2039 (SP4)
> Do I REALLY have SP4?
> :)
>

HELP: SP4 Version?

@.@.version of 8.00.2039 seems correct.
"Whispering Leaf" wrote:

> I am convinced we do not have SQL 2000 SP4 installed but the console says I
> do. After a few failed installation of SP4 I cannot imagine it is truly
> installed.
> It says: 8.00.2039 (SP4)
> Do I REALLY have SP4?
>

HELP: sp_help and object browser report view column sizes differently

Hi,

I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
SQL Query Analyzer's object browser to view the columns returned by a view,
I find that sp_help is reporting stale information.

In a recent schema change, for example, someone lengthened a varchar column
from 15 to 50 characters. If we use sp_help to find out about a view that
depends upon this column, it still shows up as VARCHAR(15), whereas the
object browser correctly reports it as VARCHAR(50).

Dropping and recreating the view fixes the problem, but we have quite a few
views, and dropping and re-creating all of them any time a schema change is
made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
it would 'refresh' SQL Server's information, but no luck.

(if you're curious as to why I don't just use the object browser instead,
read boring technical details below)

Has anyone seen this before? Is there some other way (other than
re-creating every view) to tell SQL Server to "refresh" it's information?

Thanks!

-Scott

-------
Boring Technical Information:

The reason this is an issue for us (i.e., I can't just use the object
browser instead) is that our object model classes are built using standard
metadata query methods in Java that seem to be returning the same stale
information that sp_help is returning. These methods are a part of the
standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
object model (at least with respect to views) may not match our current
schema!A view need to expose its columns and each columns datatypes in the system
tables, just like a table. However, in SQL Server, this information is not
refreshed when you modify an underlying object (like ALTER TABLE). This is
why sp_help will show you the old information, it picks it up from
syscolumns. Repro below:
USE tempdb
GO
DROP VIEW v
GO
DROP TABLE t
GO
CREATE TABLE t(c1 varchar(10))
GO
CREATE VIEW v AS SELECT c1 FROM t
GO
EXEC sp_help v
GO
ALTER TABLE t ALTER COLUMN c1 VARCHAR(20)
GO
EXEC sp_help v -- Here, the info is still old
EXEC sp_refreshview v
EXEC sp_help v

Note that you can use sp_refreshview to refresh the view definition.

QA's object browser doesn't pick up the meta-data from syscolumns, that is
why it can show current information. Here's what QA seems to be doing to
pick up the meta-data info:

declare @.P1 int
set @.P1=1
exec sp_prepare @.P1 output, NULL, N'SELECT * FROM [tempdb].[dbo].[v]', 1
select @.P1
exec sp_unprepare 1
--
Tibor Karaszi

sp_refreshview - Refreshes the metadata for the specified view. Persistent
metadata for a view can become outdated because of changes to the underlying
objects upon which the view depends.

exec sp_refreshview 'viewname'

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message
news:OOWdnchUCvygmzWiRTvUrg@.texas.net...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a
view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar
column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a
few
> views, and dropping and re-creating all of them any time a schema change
is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes
that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result,
our
> object model (at least with respect to views) may not match our current
> schema!|||"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

See sp_refreshview in Books Online, which is intended for exactly this situation.

Simon|||Hi

Try looking at sp_refreshview. Previous posts have described ways to
do this for all tables if you need to write a procedure.

John

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

Help: sort by total field

How can I sort by total field? I mean, I am talking about Interactive sort, and I want to sort not by the single field but by total filed of the group (not the general total but the partial total(*)).
Exmpl:
one one.one 10
one.two 20
TOTAL 30 <--(*)
two two.one 40
TOTAL 40 <--(*)
TOTAL 70
Thank You!!!
I'm also interested in this. Did you find a soln?|||

Please read my response with a sample on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=409872&SiteID=1&mode=1

-- Robert

Help: sort by total field

Please read my response with a sample on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=409872&SiteID=1&mode=1

-- Robert

Help: slow sp execution using job

A scheduled job to execute a stored procedure to get data out of one table and insert into another blank table using cursor.
Running this stored procedure in Query Analyzer takes 1.5 mins to finish, however, running a job with the exactly same execute command takes 1 hour to finish.
Please help !!!!!Why dont you create a DTS package to transfer the data between the tables and schedule the package|||Thanks. I never tried DTS. But I need some complicated logic to define the data partition I need to transfer before the data is transfered. I am not sure if DTS can include this logic by a programmable way in its process to set the transfer criteria.
Regarding to the slow problem, I found the answer from someone else's post weeks ago. Basically, I put "set nocount on" in front of each exec and it speeded up the execution.

Originally posted by pcmbalaji
Why dont you create a DTS package to transfer the data between the tables and schedule the package

Help: Shortest Path in sql

Hello to all,

help, help,...

i have with this problem since 3 weeks, until now i cann't resolve this problem. Maybe can somebody help me. I am hopeless. Crying

i have a data table ValidRelationship, i will check if there is a relationship between two members by this table.

datas in the table ValidRelationship:

IDMember IDOtherMember IDType

3700

372610000372637001000037423672100003672374210000342235481000035483422100003548371710000371735481000036753695100003695367510000

I will give two member and check their Relationship with a sql query. but it can be that this two member have no relationship. So i define here that man should search processor <= 6 . To better describe i use a example: max. Result of this query is: 1-2-3-4-5-6. If this is a relationship between 1-7 is 1-2-3-4-5-6-7, but i will give a answer that this is no relationship between 1-7. because processor > 6.

But my problem is: this query executing is too slow. if i habe two member no relationship, the time of this complete sql query to execute is more than 1 minutes. Is my algorithm wrong, or where is the problem, why this executing is so slow? How can i quickly get the relationships between two member, if my algorithms is not right? The following Query is only to processor = 3, but it works too slowly, so i don't write remaining processors.

declare @.IDMint;

declare @.IDOint;

set @.IDM= 3418;

set @.IDO= 4270

selecttop 1 IDMember

from v_ValidRelationships

where IDMember= @.IDM

and @.IDOin(select a.IDOtherMemberfrom v_ValidRelationshipsas awhere a.IDMember= @.IDM)

selecttop 1 a.IDMember, b.IDMember

from v_ValidRelationshipsas a, v_ValidRelationshipsas b

where a.IDMember= @.IDM

and b.IDMemberin(select c.IDOtherMemberfrom v_ValidRelationshipsas cwhere c.IDMember= @.IDM)

and @.IDOin(select d.IDOtherMemberfrom v_ValidRelationshipsas dwhere d.IDMember= b.IDMember)

selecttop 1 a.IDMember, b.IDMember, e.IDMember

from v_ValidRelationshipsas a, v_ValidRelationshipsas b, v_ValidRelationshipsas e

where a.IDMember= @.IDM

and b.IDMemberin(select c.IDOtherMemberfrom v_ValidRelationshipsas cwhere c.IDMember= @.IDM)

and e.IDMemberin(select f.IDOtherMemberfrom v_ValidRelationshipsas fwhere f.IDMember= b.IDMember)

and @.IDOin(select d.IDOtherMemberfrom v_ValidRelationships as d where d.IDMembe = e.IDMember)

If someone has a idea, please help me. Thank a million

Best Regards

Shasha

The reason it starts gumming up is because you are potentially creating millions of records for this process - in step one, you are only scanning the records in the table, but then you're exponentially scanning the table - like this example with just 10 rows:

1) Look for relationship in 10 rows

2) Look for relationship in 10 x 10 rows (100)

3) Look for relationship in 10 x 10 x 10 rows (1000)

4) Look for relationship in 10 x 10 x 10 x 10 rows (10,000)

5) 100,000

6) 1,000,000

You would have to totally revisit the structure to avoid this.

|||

Hello Sohnee,

I'm sorry that i can not good understand what you mean. Could you please explain more some details? Thank you very much.

Regards

Shasha

|||

Sorry, I don't have time to write the exact query, but perhaps someone else here wants to take a shot.

Assuming the that IDMember is "parent", and IDOtherMember is "child". Build a recursive CTE, that returns all the children of the parent up to x levels deep (columns parent,child,path_from_parent_to_child,depth).

Then using the CTE, filter only the rows where parent is equal to an input parameter, child is equal to an input parameter, and depth is equal to or less than an input parameter.

|||

@.MaxDepth determines the "number of processors". If you want to search up to 7 deep, set @.MaxDepth to 7. The results are not exactly the same, but similiar enough. The Path column will include a comma delimited list of ID's it went through to reach the other member.

DECLARE @.MaxDepthint

DECLARE @.IDMemberint

DECLARE @.IDOtherMemberint

SET @.MaxDepth=10

SET @.IDMember=3700

SET @.IDOtherMember=3726

WITH DirectReports(IDMember, IDOtherMember,Path, Depth)

(

-- Anchor member definition

SELECT IDMember, IDOtherMember,','+CAST(IDMemberasvarchar(MAX))+','+CAST(IDOtherMemberASvarchar(MAX))+','ASPath, 1AS Depth

FROM ValidRelationship

WHERE IDMember=@.IDMember

UNIONALL

-- Recursive member definition

SELECT t2.IDMember, t1.IDOtherMember, t2.Path+CAST(t1.IDOtherMemberASvarchar(MAX))+','ASPath, Depth+ 1

FROM ValidRelationship t1

INNERJOIN DirectReportsAS t2ON t2.IDOtherMember= t1.IDMember

WHERECHARINDEX(','+CAST(t1.IDOtherMemberASVARCHAR(MAX))+',',t2.Path,0)=0

AND t2.Depth<@.MaxDepth

)

-- Statement that executes the CTE

SELECTTOP 1 IDMember,IDOtherMember,SUBSTRING(Path,2,LEN(path)-2),Depth

FROM DirectReports

WHERE IDOtherMember=@.IDOtherMember

ORDERBY DepthDESC

|||

I changed the query from what I described above so that it won't allow circular paths (3700,3726,3700, etc) because they will never be "shortest", and also to reduce processing time.

I also implemented the MaxDepth differently. Instead of allowing the CTE to recurse completely and then filtering based on the depth, I terminate the recursion at the MaxDepth.

|||

hello to motly,

Thank you very much. With your help i have resolved this problem. Now i can quickly get a result. Thanks

Best Regard

Shasha

Help: Selecting from two tables: odd situation

Hi, I have two tables setup as shown below:

Table name,"config"
+------+------+
| config_name | config_value |
+------+------+
| default_style | 1 |
| *other names | *other values |
+------+------+

Table name,"styles"
+------+------+
| style_id | style_name |
+------+------+
| 1 | Purple Hue |
| *other names | *other values |
+------+------+

Now, for my question:
I need to select everything (*) from the config table
and the 'style_name' from the styles table where style_id of the styles table is equal to the value of default_style of the config table.

I got as far as this query
"SELECT config.*, styles.style_name FROM `config`, `styles` WHERE config.default_style = styles.style_id LIMIT 1"

But obviously that will not work.

I know I can do this with two queries, but I am optimizing and I would like to reduce this down to 1 query, is this even possible to do with one query?

Thanks.left outer join|||Is this the same as a left join? I have heard of left join, but never of a left outer join.

Just an alias or what?|||yeah, it's the same

some databases allow the OUTER keyword to be optional

Help: SELECT from a string

I'm really new to T-SQL, so this is probably a really easy question, but here goes:

I'm trying to create a function that selects builds a string specifying a table to select from:

SELECT * FROM ('AdventureWorks.sys.schemas')

That's what I want to do, but it gives me:

Incorrect syntax near 'AdventureWorks.sys.schemas'.

Basically, I need to know how to convert from a string to a table type. Hopefully this is easy.

Thanks a bunch!

In order to do that you will need dynamic sql, but because you mentioned that you are new, I will recomend to stay away from it by now and move forward in learning T-SQL.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

You can't 'convert' from a string to a table type. As Alejandro indicates, using dynamic SQL may be the only viable option.

As you will discover upon reviewing Erland's article (see link above), using dynamic SQL has quite a few security issues, and if used improperly, may jepardize the safety and security of your server and data.

HELP: REPORTING SERVICES Pie Chart Point Label Formatting

I'm trying to complete a report with a few pie charts. these charts need to show the category, the associated revenue, and the % of total. I can get the first two but am having a problem with trying to add the % to the label. This is the only show-stopper for my report.

thanks

You can calculate the percentage by using an expression for the data point label. E.g. =Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "ChartName")

See attached sample report.

-- Robert

==================================

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ProdCat">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="ProdCat">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>ProdCat</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProdCat.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.125in</Width>
<Top>4.5in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderYear">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderYear">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderYear</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderYear.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderQtr">
<GroupExpressions>
<GroupExpression>=Fields!OrderQtr.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="OrderQtr">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderQtr</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderQtr.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Chart Name="SalesChart">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style />
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Sales.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>P2</Format>
</Style>
<Value>=iif( Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, " ", Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart"))</Value>
<Position>Top</Position>
<Visible>true</Visible>
</DataLabel>
<Style>
<BorderWidth>
<Default>=iif( Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"SalesChart") < 0.03, "0.5 pt", "1 pt")</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Title>
<Style />
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProdCat.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProdCat.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!OrderYear.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>4.375in</Height>
<Subtype>Plain</Subtype>
<PlotArea>
<Style />
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style />
</MajorGridLines>
<MinorGridLines>
<Style />
</MinorGridLines>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="AdventureWorks2000">
<rd:DataSourceID>78faf15d-b746-4b6a-8f10-86ed6ddf1787</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial catalog=AdventureWorks2000</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="ProdCat">
<DataField>ProdCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SubCat">
<DataField>SubCat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderYear">
<DataField>OrderYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderQtr">
<DataField>OrderQtr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Sales">
<DataField>Sales</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AdventureWorks2000</DataSourceName>
<CommandText>SELECT ProductCategory.Name AS ProdCat, ProductSubCategory.Name AS SubCat, DATEPART(yy, SalesOrderHeader.OrderDate) AS OrderYear,
'Q' + DATENAME(qq, SalesOrderHeader.OrderDate) AS OrderQtr, SUM(SalesOrderDetail.UnitPrice * SalesOrderDetail.OrderQty) AS Sales
FROM ProductSubCategory INNER JOIN
SalesOrderHeader INNER JOIN
SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID INNER JOIN
Product ON SalesOrderDetail.ProductID = Product.ProductID ON
ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID INNER JOIN
ProductCategory ON ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE (SalesOrderHeader.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy, SalesOrderHeader.OrderDate), ProductCategory.Name, ProductSubCategory.Name, 'Q' + DATENAME(qq,
SalesOrderHeader.OrderDate), ProductSubCategory.ProductSubCategoryID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>9059fe01-2a5d-4d68-bfd9-2a49ccc184e6</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

|||Robert, thanks for the input. will use immediately.|||

Thanks Robert

Its work for me too

HELP: REPORTING SERVICES Pie Chart Point Label Formatting

thanks

You can calculate the percentage by using an expression for the data point label. E.g. =Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "ChartName")

See attached sample report.

-- Robert

==================================

|||Robert, thanks for the input. will use immediately.|||

Thanks Robert

Its work for me too

Monday, February 27, 2012

HELP: SqlCeConnection path error in .NET compact framework (Pocket PC Emulator)

HELP: SqlCeConnection path error in .NET compact framework (Pocket PC Emulator)

HELP: SQL Server Wont Release Memory

HELP: SQL Server Service can't start up!

HELP: SQL Server Service can't start up!

HELP: SQL SERVER CONNECTION TIMED OUT

Help: SQL Server 2K draginng system down?

HELP: SQL Server 2000 will not start after full system restore

HELP: SQL Server 2000 will not start after full system restore

Help: SQL Server 2000 failed to restart after NT Server reboot

Help: SQL Server 2000 failed to restart after NT Server reboot

Help: SQL Server 2000 failed to restart after NT Server reboot

help: SQL report (2000) and window form(VS2003)

Help: SQL 2000 connection error(.NET 2.0, VS 2005)

HELP: SP4 Version?

HELP: SP4 Version?

HELP: SP4 Version?

HELP: sp_help and object browser report view column sizes differently

Help: sort by total field

Help: sort by total field

Help: slow sp execution using job

Help: Shortest Path in sql

Help: Selecting from two tables: odd situation

Help: SELECT from a string

HELP: REPORTING SERVICES Pie Chart Point Label Formatting

HELP: REPORTING SERVICES Pie Chart Point Label Formatting

Help Backup

Blog Archive

About Me