How to diagnose a deadly embrace

I have an application which is running on 40 active workstations. I am
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>

How to develope a notification service application

hi all,

i am a novice in notification service.i am trying to develope an application on notification service but i am not able to do a sample application.i am not able to understand what is instance and rest of the things in notification service.

can anybody provide any guideline or links so that i am able to understand what notification service is and how to create a sample application.

please help

thanks a lot in advance.

Hi -

I typically recommend that people start with the walkthrough included in BOL; it'll give you a pretty good overview of the technology, terminology, etc. Many people, after going through the example, are left with more questions that when they began. That's where some of the other sample instances can help out to some extent.

At the risk of sounding like I'm doing little more than promoting my own book, I will mention that I do have a book out there that's designed to get people up to speed with SSNS in a very short amount of time. It's less than 200 pages and I've heard good things about it in the community.

Shyam Pather, also has a book out there on the subject. At 600+ pages, it's definitely a great resource to have and it covers much more than my book - custom content formatters, etc.

I also blog about the topic here - http://www.sqlns.com.

And of course you can content to visit this forum and we'll be glad to provide whatever help we can.

HTH...

Joe|||

Indeed, Joe Webb is right:

his and S.Pather's books are the best 2 books in the market now.

There are quite a few others which either have a chapter on NS or are completely dedicated to NS, but those 2 are way better.

Just get either one of those 2 books (or, better yet, both) and follow the samples.

It won't take you too long to get your own prototype up and running, and then you will change and extend it the way you need.

That's how I did my NS app.

Undoubtedly, MSDN is the best source of technical info when you need to quickly find an answer to a particular question...

...but to get you up and running, you need a good book.

How to develope a notification service application

hi all,

i am a novice in notification service.i am trying to develope an application on notification service but i am not able to do a sample application.i am not able to understand what is instance and rest of the things in notification service.

can anybody provide any guideline or links so that i am able to understand what notification service is and how to create a sample application.

please help

thanks a lot in advance.

Hi -

I typically recommend that people start with the walkthrough included in BOL; it'll give you a pretty good overview of the technology, terminology, etc. Many people, after going through the example, are left with more questions that when they began. That's where some of the other sample instances can help out to some extent.

At the risk of sounding like I'm doing little more than promoting my own book, I will mention that I do have a book out there that's designed to get people up to speed with SSNS in a very short amount of time. It's less than 200 pages and I've heard good things about it in the community.

Shyam Pather, also has a book out there on the subject. At 600+ pages, it's definitely a great resource to have and it covers much more than my book - custom content formatters, etc.

I also blog about the topic here - http://www.sqlns.com.

And of course you can content to visit this forum and we'll be glad to provide whatever help we can.

HTH...

Joe|||

Indeed, Joe Webb is right:

his and S.Pather's books are the best 2 books in the market now.

There are quite a few others which either have a chapter on NS or are completely dedicated to NS, but those 2 are way better.

Just get either one of those 2 books (or, better yet, both) and follow the samples.

It won't take you too long to get your own prototype up and running, and then you will change and extend it the way you need.

That's how I did my NS app.

Undoubtedly, MSDN is the best source of technical info when you need to quickly find an answer to a particular question...

...but to get you up and running, you need a good book.

how to determine sp level at app install time?

Hi,
I have an application that uses an instance of MSDE we create. What should
our installer check to find out if we need to install msde sp3, when our
users upgrade our software?
thanks,
David
Hi,
Installation of ur application require a valid login to sql server right,
then you could verify the service pack by executing the below query.
SELECT SERVERPROPERTY('PRODUCTLEVEL')
This returns SP3 if the service pack installed is SP3. Based on this u could
continue the installation.
Thanks
Hari
SQL Server Mvp
"David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
> Hi,
> I have an application that uses an instance of MSDE we create. What
> should
> our installer check to find out if we need to install msde sp3, when our
> users upgrade our software?
> thanks,
> David
|||Thanks Hari,
We have a problem with that solution though. I guess we need to use osql to
run the serverproperty query, but we don't know how to run it so that the
user doesn't see it run, and also we don't know how to get the response from
osql back into our installer code.
Do you have any suggestions on how to handle those issues?
Thanks very much!
David
"Hari Prasad" wrote:

> Hi,
> Installation of ur application require a valid login to sql server right,
> then you could verify the service pack by executing the below query.
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> This returns SP3 if the service pack installed is SP3. Based on this u could
> continue the installation.
> Thanks
> Hari
> SQL Server Mvp
> "David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
> news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
>
>

How to determine if application fetches the result set?

Hi
I have a FoxPro application here to deal with. How do I determine whether
the application fetches the result set to completion or not?
Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
look for?
Many thanks,
Oskar
Hi
You should get SQL_SUCCESS returned from your SQLExecute/SQLFetch statement
if it has completed or SQL_STILL_EXECUTING if it hasn't.
John
"Oskar" wrote:

> Hi
> I have a FoxPro application here to deal with. How do I determine whether
> the application fetches the result set to completion or not?
> Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
> look for?
> --
> Many thanks,
> Oskar
>
sql

How to determine if application fetches the result set?

Hi
I have a FoxPro application here to deal with. How do I determine whether
the application fetches the result set to completion or not?
Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
look for?
Many thanks,
OskarHi
You should get SQL_SUCCESS returned from your SQLExecute/SQLFetch statement
if it has completed or SQL_STILL_EXECUTING if it hasn't.
John
"Oskar" wrote:

> Hi
> I have a FoxPro application here to deal with. How do I determine whether
> the application fetches the result set to completion or not?
> Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
> look for?
> --
> Many thanks,
> Oskar
>

How to determine if application fetches the result set?

Hi
I have a FoxPro application here to deal with. How do I determine whether
the application fetches the result set to completion or not?
Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
look for?
--
Many thanks,
OskarHi
You should get SQL_SUCCESS returned from your SQLExecute/SQLFetch statement
if it has completed or SQL_STILL_EXECUTING if it hasn't.
John
"Oskar" wrote:
> Hi
> I have a FoxPro application here to deal with. How do I determine whether
> the application fetches the result set to completion or not?
> Do I have to look for SQLCloseCursor in ODBC trace? What are the signs to
> look for?
> --
> Many thanks,
> Oskar
>

How to determine if a database is in use

I have a VB application that uses SQL Server 2000. I am adapting it to support both 2000 and 2005. I connect to the database using ODBC. My application has the typical backup/restore functionality. Before I do a backup or restore, I check to see if the database is currently being used by another application. In SQL Server 2000, I did this by connecting to the 'master database' and running the following query:

SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'

If I get a count greater than 0, someone else is using the database. This of course does not work on SQL Server 2005. I have come up with an alternative. For SQL Server 2005, I connect to the database I want to backup or restore and run the following query:

SELECT COUNT(*) FROM sys.dm_tran_locks

If I get a count greater than 1 (1 because I had to connect to the database myself to run the query), someone else is using the database. The problem is, it isn't terribly reliable. I sometimes run the query and get a count greater than one, then try again a few seconds later and get a count of 1. Having the Studio Enterprise manager open to the point that you can see all database in the database tree also has an impact. The problem must be more complicated than my simply solution can handle. Trouble is I am having a hard time finding any docs that discusses the issue. I am probably just no looking in the right place. Does anyone have a better way to determine if another process is using the database you want to backup or restore?

Why worry for backups, the operation is online, i.e. people can be accessing the system whilst the backup is running.

Not the case for restores but they should be few and far between. You can kick everyone off, by using the ALTER DATABASE command in SQL 2005.

If you do need to find connections go to sysprocesses in SQL 2000 and in sql 2005 sys.dm_exec_requests

|||

I figured that might be be someone's response, but I wanted to keep my post short so I did not explain any further. While backups can be done on-line, in my case it would not be appropriate. I am doing a backup and restore as part of my database upgrade process. A backup is done just before the data upgrade (adding and deleting tables, fields, moving data around, etc). The restore is used to recover from any failure during the upgrade process. So, allowing the users to be in the system for a few more moment's just postpones the problem. I cannot have someone in the database while I am making structure changes.

That said, kicking them off unexpectedly isn't very elegant either. I would much prefer to put a check before going through this process to tell the user that someone is connected to the database and I cannot proceed until they are out. I am running this business application in locations across the country. It is used by "normal" users who may have few skills other than a knowledge of how to run the business application.

I do appreciate the suggestion to use the sys.dm_exec_requests stored procedure. I'll look into this now.

Thank you very much!

|||You could issue a

alter database <database> set single_user

And just wait. If it timesout then someone is still in the db.

|||That also stops new users from connecting.|||

This looked like a really good idea. I tried using it by connecting to the database via my application, then going to Management Studio and entering the alter database as you suggested. The result was that it waited indefinitely for the lock to be removed (it never timed out). I searched around for how to set the timeout period. The only thing I could find is 'set lock_timeout=n' but it is documented that alter database ignores this setting (as does create database and drop database). I could not find an alternative.

I'll go back and try to work with your first suggestion.

Thanks!

Andy

|||

I went back and found the problem with my original solution. The SQL Server 2000 approach and the corresponding SQL Server 2005 approach are more similar than I thought. In SQL Server 2000, I ran the following query to see if anyone was using the database called 'MyDatabase':

SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'

In SQL Server 2005, the following seems to work the same:

SELECT COUNT(*) FROM sys.dm_tran_locks WHERE resource_database_id IN (SELECT dbid from sys.sysdatabases where name='MyDatabase')

Thank you for you other suggestions. Setting the access to single user is probably better, because it prevents people using the database part way through the upgrade process. In order to use it, I just need to figure out how to make it timeout in a reasonable period of time.

Andy

|||

You can set the timeout in your application. If using ADO the command object has a command timeout.

As for your second set of code. you can simplify it by using db_id('your database name') to get the database_id of your database

|||Thanks for the info. I am not using ADO, but I'll keep that in mind if I ever switch. The biggest problem that prevents me from using ADO is the lack of support for cursors. Not everything works well with a disconnected recordset. I keep hearing that support will be added back in... but I do not think it has happened so far.|||

Why do you need cursors?

What do you use instead of ADO?

What are you programming in?

|||

I only have one need for cursors (but it is big). In many placed in an application, I have to manage a collection of items where that is very large (several thousand to as many as 10 million). It isn't practical to load that many things in a collection (it takes too long) or display that many things in a listbox or spreadsheet. The way I solve this problem know is by paging the data into and out of the collection.

While getting a page worth of data doesn't require a cursor, navigating from page to page without it has its problems. For example, just bringing up a vertical scrollbar is a problem. There isn't a way to tell with a disconnected recordset what page you are on relatively to the entire collection. Going to the next and previous page is terribly innefficient (especially if you are not near the top of the collection) because ther isn't a good way to get the 'next' or previous page. Disconneted recordsets involve rerunning queries to go from one page to the next (which does not perform well). These are problems I can solve with a cursor.

Of course the applications that I am writing are desktop applications with a relatively small number of users. A lot of folks say cursors are expensive and do not scale well. In my case, that is a good trade off. Most of those folks are writing web pages which is a very different animal. I would rather have a highly function and high performing interface than be concerned about scalability to a number of users because my applications are typically used by less than 100 users at at time.

Anyway, all of my applications are written in VB 6.0. I use the ODBC APIs for database access. Most of them work against more than one database (e.g. SQL Server, Access, Sybase). I will begin converting them to .NET next year (I am working on a prototype of the .NET archicture I will use and a prototype of how to convert them). I will probably leave the data access alone, since the existing data access works will in .NET. This also allows me to focus my efforts on just getting the applications working again.

How to detect that the sql server already lost the connection?

Hi,
Sometimes when i use my application but suddenly the
sql server disconnected and i find out that my query
become very slow.
Can i check with you, how to detect the connection
already stop in my application? If i dun want to try and
catch for every sql transaction, how i can catch the sql
server disconnected error once the server already
disconnected? I don't want my application just seems
like 'hang' there.
Thanks for advice.
regards,
florence
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:078501c4ac35$087620a0$a301280a@.phx.gbl...
> Hi,
> Sometimes when i use my application but suddenly the
> sql server disconnected and i find out that my query
> become very slow.
> Can i check with you, how to detect the connection
> already stop in my application? If i dun want to try and
> catch for every sql transaction, how i can catch the sql
> server disconnected error once the server already
> disconnected? I don't want my application just seems
> like 'hang' there.
When I've had an application that performs periodic queries against the
database where the connection could be lost between queries, I've used a
simple query to start the next batch of queries just to see that the
connection was alive. For example, you might do a simple "SELECT 1" just to
see that the connection is alive, then do your complicated query.
This was in Java using the JDBC driver.
Rick
sql

How to detect if SQL Reporting Services is installed ?

From an ASP.Net application I need to detect if on a given node, Repoting
services is installed and if so, which version. Does anyone know a stored
procedure or a Web Services API to do this ?
thanks,RaviHello Ravi,
There are a couple ways to determine RS version:
1) Check the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
2) Use the webservice API like this:
For RS 2000:
ReportingService rs1 = new ReportingService();
rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs1.ServerInfoHeaderValue = new ServerInfoHeader();
rs1.ListChildren("/", false);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
For RS 2005:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
Note that there is a new property in 2005, "ReportServerVersion". Also be
aware that ReportServerVersionNumber returns differntly formatted values
for 2000 and 2005.
-Chris
> From an ASP.Net application I need to detect if on a given node,
> Repoting services is installed and if so, which version. Does anyone
> know a stored procedure or a Web Services API to do this ?
> thanks,Ravi
>|||Thanks Chris, that was very helpful.
I have started using the #1 to read the registry. But my requirement is that
I need to be able to detect RS and its version on local and remote machines,
including ones behind firewall. Reading Registry in these secanrios leads to
lot of security issues. Further, I also need to detect if RS is currently
running, I need this to enable a button to launch Report Builder.
Using the RS2005 Web Services seems to be a better approach. Do you know of
an API to also detect if Report Services is currently running ? Do you know
what happens if Report Services is not installed on a remote machine but we
try to call a Web Service ?
thanks for your time and help,
Ravi
"Chris Baldwin" wrote:
> Hello Ravi,
> There are a couple ways to determine RS version:
> 1) Check the registry at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
> 2) Use the webservice API like this:
> For RS 2000:
> ReportingService rs1 = new ReportingService();
> rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs1.ServerInfoHeaderValue = new ServerInfoHeader();
> rs1.ListChildren("/", false);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
> For RS 2005:
> ReportingService2005 rs = new ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs.ServerInfoHeaderValue = new ServerInfoHeader();
> rs.ListChildren("/", false);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
> Note that there is a new property in 2005, "ReportServerVersion". Also be
> aware that ReportServerVersionNumber returns differntly formatted values
> for 2000 and 2005.
> -Chris
>
> > From an ASP.Net application I need to detect if on a given node,
> > Repoting services is installed and if so, which version. Does anyone
> > know a stored procedure or a Web Services API to do this ?
> >
> > thanks,Ravi
> >
>
>

How to design flexible relationships between documents?

Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
EdgardEdgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied up
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribió en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
>> Hi,
>> I have an inventory application which also handles accounts payable, and
>> accounts receivables.
>> One of the problems I currently have is that I need to tie, in a flexible
>> way, different transaction types.
>> For example, I need to tie invoices to their "shipping-notifications", to
>> the original orders, etc...
>> A friend of mine was telling me about SAP and how there you can in a
>> flexible way define documents (or transactions) so that they can be tied
>> up to one or more documents of a given type.
>> I've been thinking about this, but I can't figure out how a design that
>> would allow this in a flexible way....
>> How does this work?
>> Thanks,
>> Edgard
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?

How to design flexible relationships between documents?

Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi Tracy,
This was my original design, but the problem is that there are situations
where there are multiple orders on an invoice, or other types of transaction
relationship's, and this approach just doesn't cut it. For example,
there are other types of transactions where I want to 1) make a demand
forecast for all stores, 2) generate a consolidated purchase order, 3) Get 1
or more invoices for that purchase order, 4) send the merchandise to the
stores in one or more shipments, etc... The approach doesn't work in
something like this :-(
Apparently with SAP (and I imagine other ERP's would be the same) you can
define relationships between the transactions in a very flexible way:
Transaction A goes to multiple transactions B, which go to multiple
transactions C, etc...
I found this design quite interesting, and clever. I have searched on the
internet to see if there is information on how this is designed, but
couldn't find yet anything...
Thanks,
Edgard
"Tracy McKibben" <tracy@.realsqlguy.com> escribi en el mensaje
news:ez2ta7SmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Edgard L. Riba wrote:
> Can you not carry the original order number through to the shipping
> notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
> (invoice header contains an invoice number and the original order number)
> ?|||Hi,
I have an inventory application which also handles accounts payable, and
accounts receivables.
One of the problems I currently have is that I need to tie, in a flexible
way, different transaction types.
For example, I need to tie invoices to their "shipping-notifications", to
the original orders, etc...
A friend of mine was telling me about SAP and how there you can in a
flexible way define documents (or transactions) so that they can be tied up
to one or more documents of a given type.
I've been thinking about this, but I can't figure out how a design that
would allow this in a flexible way....
How does this work?
Thanks,
Edgard|||Edgard L. Riba wrote:
> Hi,
> I have an inventory application which also handles accounts payable, and
> accounts receivables.
> One of the problems I currently have is that I need to tie, in a flexible
> way, different transaction types.
> For example, I need to tie invoices to their "shipping-notifications", to
> the original orders, etc...
> A friend of mine was telling me about SAP and how there you can in a
> flexible way define documents (or transactions) so that they can be tied u
p
> to one or more documents of a given type.
> I've been thinking about this, but I can't figure out how a design that
> would allow this in a flexible way....
> How does this work?
> Thanks,
> Edgard
>
Can you not carry the original order number through to the shipping
notifications (i.e. "Order #xxxx has shipped"), then on to the invoice
(invoice header contains an invoice number and the original order number) ?

How to design Database to search faster from 1 million customer''s

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).

Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.

I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

The bank example is not a good one, as the banks are always requesting a unique identifying attribute from the customers (like an id from their id card or their SSN). If you want to search through all the fields, you would have to implement something like fulltext searching / soundex functionality (as I assume that you did not wrote Jhon instead of John accidentially)

Requirement 2 is not a database issue, as the other bank database is normally not on the same server and is normally reached via a Web service through a service bus.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jeans for replying to my post.

Perhaps you got me otherwise.Let me now tell you exact sitiuation.

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

We consider exact sitiuation here,

We have a huge (about 1 million records) database of people involved in loan, credit card or any kind of fraud against banks.It is combined database for all banks in a country and each bank is contributing a list of defaulters from it's side to this combined database.

Now if some new customer comes to avail the services of a bank, then bank wants to ensure that new customer never appeared in this defaulter list before.

But one important searching criteria for matching against this defaulter's dabase is that we should able to carryout sounlike search i.e (let me explain with one example mentioned below)

If a person names "Mohammed Ali", then our application should be able to find out variation of names which sounds similar to orignal name of person, i.e

"Mohammad Ali"

"Mohamad Ali"

This requirement is expected from banks if new customer comes to bank with fake ID Proof or with forged documents to avail the services. In this case there will be no pre defined unique id for customer or any identifier and application will have to solely on name matching logic.

My concern is mostly associated with the performance of application, especially to the database design (for optimal performance), rather then logic of search.

|||

There are a lot of factors to take into account - hardware architecture, I/O, memory, database structure, etc. database architecture also has to be considered - creating filegroups which will contain the database files, storing the database files in multiple drive spindles, creating the tables to be stored in filegroups so that searches can be performed by multiple drive splindles, etc.

|||

Thanks bass,for your reply

But our main focus is on database design rather then Hardware configuration...Hardware is not a issue as we have plenty for our use.

I shall appriciate your efforts if you can suggest something for DB design or tuning of database.

|||

Although the soundex functionality is implemented in SQL Server there might be more sophisticated algorithms out there that might fit your need better than the SOUNDEX in SQL Server does. but to your point about the performance of the system: there should be no problem in searching even "large" (though 1M is not that large) database for the names passed by the application. My design suggestion would be to store each name normalalized in tables, generate the soundex words either in your frontend application or using a CLR function and query your table for the soundex terms. By the score of matching and found items you can choose to display the TOP N customers who match the names passed or who have a certain score (e.g. passing John Smith might bring back a long list of entries :-) ) If the tables gets even bigger you could decide to use table partitiioning to scale out your design.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to Deploye VB.net Application Along With Database....Please Help.

Hi Guys,
I had a little Problem With My Application Deployment.

I want To Make Setup For My Application(VB.NET) Such that When it begins To Install,The Database Of the Application Will Automaticallly Get Installed.....
Even I have Generated Script For That But I dont Know How to Execute It With Installation File.I have made Complete Setup Of My Application Exept Database.

Now,I want Few Code Lines To Execute My Database Script.
So how can i Execute My Script Through Installation..

And Also If there is another Way To Accomplish This Please Suggest Me.

All Suggestions Will be Accepted...

Thanx In Advance..WOuld C# code also be ok for you ? :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||yes it will be ok for me..

How to deploye SQL 2005 Express with my application

Hello,

I have an application that uses SQL Server express. I'd like to be able to
package the SQL Server Express set up file and deploy it with my application
but all the searching I've done only tells me how to deploy SQL Server
Express manually (i.e. how to set it up yourself). I can script my set up
to see if an instance of SQL Server Express already exists but if it
doesn't, I'd like to know the best way to deploy this on the target machine
automatically.

Any help or direction would be greatly appreciated.

Thanks!
Rick
"Rico" <me@.you.comwrote in message
news:MSE8h.349364$R63.85587@.pd7urf1no...

Quote:

Originally Posted by

Hello,
>
I have an application that uses SQL Server express. I'd like to be able
to package the SQL Server Express set up file and deploy it with my
application but all the searching I've done only tells me how to deploy
SQL Server Express manually (i.e. how to set it up yourself). I can
script my set up to see if an instance of SQL Server Express already
exists but if it doesn't, I'd like to know the best way to deploy this on
the target machine automatically.
>
Any help or direction would be greatly appreciated.
>

See:

Embedding SQL Server Express in Applications
http://msdn2.microsoft.com/en-us/library/ms165660.aspx
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/d...qlexcustapp.asp
David|||Excellent! Thanks David!

Rick

"David Browne" <davidbaxterbrowne no potted meat@.hotmail.comwrote in
message news:OTgVwLYDHHA.1196@.TK2MSFTNGP02.phx.gbl...

Quote:

Originally Posted by

>
>
"Rico" <me@.you.comwrote in message
news:MSE8h.349364$R63.85587@.pd7urf1no...

Quote:

Originally Posted by

>Hello,
>>
>I have an application that uses SQL Server express. I'd like to be able
>to package the SQL Server Express set up file and deploy it with my
>application but all the searching I've done only tells me how to deploy
>SQL Server Express manually (i.e. how to set it up yourself). I can
>script my set up to see if an instance of SQL Server Express already
>exists but if it doesn't, I'd like to know the best way to deploy this on
>the target machine automatically.
>>
>Any help or direction would be greatly appreciated.
>>

>
See:
>
Embedding SQL Server Express in Applications
http://msdn2.microsoft.com/en-us/library/ms165660.aspx
>
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/d...qlexcustapp.asp
>
David

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How To Deploy SSRS To Production Server ?

Hi All

I am having a problem with SSRS reports which I hope you can help with.

We have a web application which contains a couple dozen reports built using SSRS. In order to do a new release I first deploy the web application and the SSRS reports to our pre-production server for testing.

Whilst testing is going on, developers are making further changes to some of the reports and also the the database structure.

My problem is that when I want to move the new release from the pre-production server to the production server, I can copy the application but not the SSRS reports. So my only choice appears to be to deploy the SSRS reports from a devlopment machien directly to the production server.

This is clearly going to cause me problems as those reports may have been further developed and the database structure may have changed sine deploying the application to the pre-production server so I am effectly releasing untested reports directly to the production srever.

So my quesiton is:

How can I copy my SSRS reports from the pre-production server to the production server? Is there another way of dong this?

Regards

Smeat

yes.....

can we just get a link to this tool pinned?

www.sqldbatips.com

has a tool called Reporting Services Scripter 2.0.0.8

1.you point it at RS, set the options, it creates the script + files
2.global find replace in the files i.e. change servernames
3.run script

I suggest you try this in some kind of development environment first to understand all the different options this tool has|||

Thanks for the info.

One of our developers is checking this out.

Smeat

|||

One way of accompishing your goal will be using a version control software like Visual Source Safe. Here's the scenario.

1. Developer develops version 1 of report and check in Visual Source Safe.

2. Designated person (configuration administrator) pushes version 1 of report to pre-production environment.

3. Report is tested by QA group & users in the pre-production environment.

4. QA group and/or users discover errors and/or updates to be done by developers on version 1 of the report.

5. Developer implements updates and fix errors found on version 1 of the report & check in new version into Visual Source Safe (version 2).

6. Repeat step 2 but this time will be version 2 of the report.

7. Repeat step 3. Move to next step (step 8) if QA group & users are OK with updates; otherwise, repeat step 5 - 7

8. Designated person (configuration administrator) pushes final version of report to production.

I hope this helps.

How to deploy SQL Server Everywhere with my windows form application?

Hello!

I've been creating a vb.net 2005 windows form application using Everywhere and it has been going pretty well. However, I haven't had much luck finding any help on how to deploy my Everywhere database file(s) and Everywhere itself. To install redistribute Everywhere, can I just add the the 8 .dlls found in the Everywhere directory into my VS setup project? Also, what database file(s) do I need to deploy in my windows setup?

Thanks!

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

Darren

|||

Thanks for the link Darren. This looks like it's using ClickOnce but I was hoping to deploy Everything using the Windows installer. Any thoughts on how that might happen?

Thanks!

|||

it's the same process of putting the right DLLs that belong to SQL Ev into your program's app directory.

-ds

How to deploy report in C# with ADO XML datasources

HI,

i m creating CR in C# and ADO.net XML file as data source. my application is calling CR in exe file for viewing report. although i hav work out the setup and deployment project, yet its still need to install Crystalreport in MS.Visual studio 2003.

any others way to solve this problem?

plz don hesitate to reply me k.

emiliecan you please clarify the question? You don't know what you need to package to get crystal working on the deployment machine?