ANYONE PLEASE - Local Synchronization Between SQL Mobile and Local SQL Database

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

Can anyone please help me

|||

If you build your mobile application using VS2005 and SQL Mobile, you can use merge replication between your server-side publication and SQL Mobile. The SQL Mobile Books OnLine are the first place to start - read the section on merge replication.

-Darren Shaffer

|||

Hi Darren

Yes I got my sql mobile to synchronize successfully with the local desktop sql database called "Inventory"

However I have a new problem

I would like to have the desktop replicating with the server for database "Inventory"

I also got this to work seperately

I would now want the SQL mobile database to replicate with the local desktop database "Inventory" which inturn will replicate with the server copy called "Inventory"

I would like to have both replication scenarios joined together. I know that most people will say get the SQL mobile to talk directly to the server but this is the most expensive scenario I believe

Is this possible?

Thankyou for your earlier post

Touraj

|||

Touraj,

Sorry for not reading your post more closely - I see what you are trying to do now. You want to have a 3 tier merge replication going on. Yes, this is possible, but the database on the laptop is going to have to be SQL Server 2005, not SQL Express. The way it would have to work is this:

The laptop would have to have IIS installed and the SQL Mobile server tools. As far as the mobile app is concerned, the laptop is the publisher. On the laptop, you have a subscription to the central server in a replication relationship. You would turn around and create a publication on the laptop with the same articles that are in the subscription to the central server. You would need to be extremely careful with uniqueness in this situation - I would avoid identity columns and use GUIDs for everything in this app domain.

Is there any chance that both the laptops and the PDAs can be subscribers to the central server? That would be a much easier implementation, but it is possible to do what you suggested with the extra tier.

-Darren

|||

Hi Darren

Thankyou again for your kind assistance

Yes you are right. It would be easier and 'cheaper' to get the PDA to synch with the central server via the servers IIS process

I am tossing up between a i-Mate and iPaq 4700. I would be interested to know if you have a preference over the two

Thanks from sunny Australia.

Touraj

|||

I think both i-Mate and HP make great devices - I have not had any trouble with SQL Mobile on my JasJar or on a variety of iPaqs. My WM5 JasJar seems a little sluggish overall compared to a Dell Axim X50v (WM5), which others have noted as well. Not slow, just not instant to respond to input. I have not had specific experience with the 4700. There are some public newsgroups (microsoft.public.pocketpc.developer) where people tend to talk more about specific devices and you could use Google Advanced Groups search to get previous opinions on the 4700.

-Darren

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>sql

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
Paul G
Software engineer.
More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.
|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>
|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.
|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

Any way to generate SQL Script for application role?

Is there any way to generate the SQL to create an application role and all t
he associated grants for table and stored procedure access? Or does anyone h
ave a suggestion for how to migrate an application role from one environment
to another? The developers
originally create the role by clicking on things.The easiest way is to script the existing application role permissions
and edit the script for the new environment. See sp_addapprole in SQL
BOL for the syntax for creating a new application role.
--Mary
On Fri, 16 Apr 2004 10:01:10 -0700, Charlotte
<anonymous@.discussions.microsoft.com> wrote:

>Is there any way to generate the SQL to create an application role and all the asso
ciated grants for table and stored procedure access? Or does anyone have a suggestio
n for how to migrate an application role from one environment to another? The develo
per
s originally create the role by clicking on things.sql

Any way to find user who did update in SQL SERVER?

Hi there,

Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.

Any command or software to check it?

Thanks for the help!On Nov 20, 6:45 pm, LaMoRt <cwe...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.
>
Any command or software to check it?
>
Thanks for the help!

Try running

sp_who
sp_who2
Profiler|||On Nov 20, 6:45 pm, LaMoRt <cwe...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
Is there any way to find the person or user who did update transaction
manually
in query analyser and not through application in certain time?
As i have check thru log file but cannot find anything.
>
Any command or software to check it?
>
Thanks for the help!

Duplicate of http://groups.google.com/group/comp...28b3b6e9b5b1558

Any reason to avoid SQL Mail on a Production Server?

We are supporting a SQL Server 2000 (sp3) / ASP application.
The application is extremely poorly written (deadlock, timeout, stored
procs longer than my kids Christmas lists...) and I would like to have
SQL Mail installed to get various information emailed to us when one
of these events occur (through alert).
I meet stiff resistance from the Production guys but without proper
reason.
Before I put my head on the line... Is there any reason we should be
scared to install SQL Mail on a Production box?
One developer told me that it used to hang their production SQL
Server... (but that may have been SQL Server 6.5)
Thanks
EricEric,
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
>
I would not necessarily view long stored proc as a bad thing. If the task
needs a long stored proc, so be it. It will perform better than breaking it
down to shorter ones.
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
Though not scared, we did see some problem with Outlook:
1. When a process involves Outlook mail, and the mail server or client is
down, the process hangs (at least we saw this on SQL7).
2. Not whether a general problem or server specific: we saw that a SQL7
server's service be restarted by a too long mail recipient value.
We have the notifications you are considering on most of our servers. Just
mention this for your consideration to be prepared to talk to your
counterparts in other groups.
hth
Quentin|||Well, you'll need to run SQL under a domain user account -- not necessarily
a domain admin if you set all the appropriate permissions, but without a
domain user, there's no way to hook to an Exchange mailbox.
You'll also need to install a MAPI-compliant email program on the server
(preferably Outlook). Yet anothe application on a production server means
another point of hacking attacks.
In my experience, SQL Mail and SQL Agent Mail (they are two different
things) don't crash a server, but there are circumstances where they'll
stop working. If they try to send an email while the Exchange server is
offline or unreachable, that particular MAPI session can get messed up, but
remains in memory. You need to stop and start SQL Mail/SQL Agent Mail to
get it working. There are some ways around this like including a .pst file
in the profile you use for mail, but that too has issues.
Another option you may want to look into is Gert Drapers SMTP-based email
(www.sqldev.net). The only thing installed is an extended stored procedure,
it doesn't run into the problems MAPI can create, and you don't need to set
SQL to run under a specific account. The only limitation is that you can
use it for the integrated alert system.
Hope that helps.
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message
news:11269dcb.0307250628.35979849@.posting.google.com...
> We are supporting a SQL Server 2000 (sp3) / ASP application.
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
> I meet stiff resistance from the Production guys but without proper
> reason.
> Before I put my head on the line... Is there any reason we should be
> scared to install SQL Mail on a Production box?
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
> Thanks
>
> Eric|||> The only limitation is that you can
> use it for the integrated alert system.
I assume you meant "can't"...
To get around this limitation, I simply add a step to jobs I need alerts
for:
step 1
call some sql
if succeeds, quit with success
if fails, goto step 2
step 2
call xp_smtp_sendmail to send alert
-- so that the failure of step 1 is correctly reflected in sys:
if succeeds, quit with failure
if fails, quit with failure|||I think I'll try to get them to install Outlook.
Our SQL Server and SQL Agent services already run under domain
accounts.
Otherwise I know there are ActiveX objects available to send mail on
our production machine but it would be far less convenient than
xp_sendmail.
Thanks for your comments
Eric

Any one used Application Role before?

I'm using vb6 and i'm trying to create a connection string using an
application role.
If this is possible.
I don't know what the string should look like or whether i have put
everything in place.
I'm using Windows Authentication Mode. However I created a Application Role
and password for my app. Should i use this to connect to the db so i don't
have to use a userid? I shouldn't need a windows login to use an application
.
According to my book: "After the application has enabled and application
role, all permissions of the user are suspended, and only the permissions of
the role are enforced." Then it says later: "The best part is that all
activity is still audited with the users' login information."
Microsoft said at
http://msdn.microsoft.com/library/d...
ahx.asp :
"Use the Integrated Security keyword, set to a value of SSPI, to specify
Windows Authentication (recommended),
or
use the User ID and Password connection properties to specify SQL Server
Authentication.
Security Note When possible, use Windows Authentication. If Windows
Authentication is not available, prompt users to enter their credentials at
run time. Avoid storing credentials in a file. If you must persist
credentials, you should encrypt them with the Win32? crypto API. For more
information, see "The Crypto API Function" in the MSDN? Library at this
Microsoft Web site."
I don't can't make sense of both of these statements. How do they work
together or do they contradict? What should i do?
Here is my connection String
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DatabaseName;Data Source=ServerName
I'm able to get it to work with my login using Windows Authenticity, but
everyone can't use my login name. I can't set permissions for all users
either.
Thanks!You still need both a login (SQL Server or Windows) and a user name in the d
atabase. Then you app
uses sp_setapprole to enter the application role. If you aren't familiar wit
h SQL Server security
architecture, read in Books Online on the topic (so you understand both the
login and the user name
concepts).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jacob" <jacob@.discussions.microsoft.com> wrote in message
news:68E272F5-7FD8-4D8C-B4B6-F8F2AEC7A70F@.microsoft.com...
> I'm using vb6 and i'm trying to create a connection string using an
> application role.
> If this is possible.
> I don't know what the string should look like or whether i have put
> everything in place.
> I'm using Windows Authentication Mode. However I created a Application Rol
e
> and password for my app. Should i use this to connect to the db so i don't
> have to use a userid? I shouldn't need a windows login to use an applicati
on.
> According to my book: "After the application has enabled and application
> role, all permissions of the user are suspended, and only the permissions
of
> the role are enforced." Then it says later: "The best part is that all
> activity is still audited with the users' login information."
>
> Microsoft said at
> http://msdn.microsoft.com/library/d...r />
_0ahx.asp :
> "Use the Integrated Security keyword, set to a value of SSPI, to specify
> Windows Authentication (recommended),
> or
> use the User ID and Password connection properties to specify SQL Server
> Authentication.
>
> Security Note When possible, use Windows Authentication. If Windows
> Authentication is not available, prompt users to enter their credentials a
t
> run time. Avoid storing credentials in a file. If you must persist
> credentials, you should encrypt them with the Win32? crypto API. For more
> information, see "The Crypto API Function" in the MSDN? Library at this
> Microsoft Web site."
>
> I don't can't make sense of both of these statements. How do they work
> together or do they contradict? What should i do?
> Here is my connection String
> Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DatabaseName;Data Source=ServerName
> I'm able to get it to work with my login using Windows Authenticity, but
> everyone can't use my login name. I can't set permissions for all users
> either.
> Thanks!
>

Any Movement on the 4 gig limit?

We're very interested in having our application use SQL/e but we can't have the 4 gig limit. It makes sense to me that SQL/e simply should not be able to access a file over the network, and then you wouldn't have any reason to put a 4 gig limit on it.

At that point it becomes a very flexible alternative for remote users that need to have a large amount of data (i.e. documents, images etc.) with them.

We'd love to start building an abstraction layer so that we can support both SQL Server and SQL/e so that we can support network and remote users and not have the nightmare that is SQL Server Express installation. (care of the windows installer group's bugs...)

Thanks! Hoping for a favourable answer!

The limit is one of the restrictions on having a free db engine, you might want to look at the workgroup edition of SQL Server 2005. The other option is to have muiltiple database files as the limit is on the size of the database files not the combined engine. You could store archived information in files and keep the production/live data in the current file.

|||Ya, but the point to the whole deal is that the 4 gig limit is not applicable in the case of a single user database scenario like SQL/e because it's specifically there to prevent people from using SSE to share the database and not buy SQL Server.

With SQL Server 2000 we had the personal edition which was $99. That was fine and dandy and I could live with that on client machines. However, that's gone for SQL Server 2005 and as a result it gets incredibly expensive to outfit laptops with a database engine that doesn't have a 4 gig limit.

SQL/e is the perfect way to distribute your application in a smart client (online/offline) environment because it's drag and drop installable instead of the disaster that is SSE installation. And because it's designed for a single user scenario there should be no reason for the 4 gig limit. They should just prevent the APIs from connecting to a datasource on a network device. Problem solved and you get a great little database for distributed applications.

And since this is exactly the scenario that the product was designed for it just makes sense to not have a limit and lock it down.

Any luck?

Hi All -
Yet another company with the EXACT same problem that all of you have so
eloquently described. Was there any luck with either the application of the
patches (or SP1) mentioned or in the use of SQLXMLOLEDB as the provider?
Thanks
Keith
Hi Keith,
Could you kindly explain the problem that you are referring to?
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"klockwood" <klockwood@.discussions.microsoft.com> wrote in message
news:ED3DC4B7-C52A-4D93-B329-A5EE6C1D6630@.microsoft.com...
> Hi All -
> Yet another company with the EXACT same problem that all of you have so
> eloquently described. Was there any luck with either the application of
> the
> patches (or SP1) mentioned or in the use of SQLXMLOLEDB as the provider?
> Thanks
> Keith

Any luck?

Hi All -
Yet another company with the EXACT same problem that all of you have so
eloquently described. Was there any luck with either the application of the
patches (or SP1) mentioned or in the use of SQLXMLOLEDB as the provider?
Thanks
KeithHi Keith,
Could you kindly explain the problem that you are referring to?
thanks
Chandra
--
---
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"klockwood" <klockwood@.discussions.microsoft.com> wrote in message
news:ED3DC4B7-C52A-4D93-B329-A5EE6C1D6630@.microsoft.com...
> Hi All -
> Yet another company with the EXACT same problem that all of you have so
> eloquently described. Was there any luck with either the application of
> the
> patches (or SP1) mentioned or in the use of SQLXMLOLEDB as the provider?
> Thanks
> Keith

Any Linux-based apps similar to MS SQL Enterprise w/ DTS ?

Hi all,

I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
Manager-type application for Linux that would give me access to a DTS
stored on the server? I'm a web programmer by profession, and most of
my tools are Linux-based, but because I do so much indepth work in MS
SQL and DTS's, I've been dual-booting between Windows and Linux mainly
because there's no way I know of to access my MS SQL DTS's from Linux.

Any Linux solutions out there that will read and maybe even edit SQL
DTS's?

Thanks,

Alex.Don't know of any linux tools for sql server but here are two alternatives
that may be useful to you:
http://www.vmware.com/ - no need for dual booting if you can run both OSs at
the same time

http://www.microsoft.com/downloads/...&displaylang=en
SQL Server web data admin tool

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0404150752.6019c780@.posting.google.c om...
> Hi all,
> I bet this doesn't exist, but does anyone know of an MS SQL Enterprise
> Manager-type application for Linux that would give me access to a DTS
> stored on the server? I'm a web programmer by profession, and most of
> my tools are Linux-based, but because I do so much indepth work in MS
> SQL and DTS's, I've been dual-booting between Windows and Linux mainly
> because there's no way I know of to access my MS SQL DTS's from Linux.
> Any Linux solutions out there that will read and maybe even edit SQL
> DTS's?
> Thanks,
> Alex.

Any inventive ways to DBREINDEX and minimize blocking

I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I would like to come up with a routine in which I could execute a
> DBREINDEX
> > without taking downtime in my application. The tables that I want to
> > REINDEX are pretty small (the reindexing should take no more than 10-15
> > seconds).
> >
> > The problem is that if the table i want to REINDEX has an IX lock on the
> > table, then the SPID trying to runs the REINDEX waits with a Sch-M lock
on
> > that table. The Sch-M lock in turn then blocks everyone else from
> obtaining
> > an IX lock. So if I had some busy process that was running for a long
> time
> > holding a IX lock (let's say 30 seconds), the reindex would be blocked
for
> > up to 30 seconds. No big deal to me if the reindex spid gets blocked
but
> > what I am concerned about it the blocking that it will in turn do to
other
> > spids waiting for an IX lock.
> >
> > I was hoping that I could define some process like:
> > WHILE 1=1
> > BEGIN
> > (If an Sch-M lock is available without actually taking it)
> > BEGIN
> > DBCC DBREINDEX table
> > RETURN
> > END
> > END
> >
> > Any comments would be greatly appreciated.
> >
> >
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>

Any inventive ways to DBREINDEX and minimize blocking

I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.
Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>
|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other
>
|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?
|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>

Any inventive ways to DBREINDEX and minimize blocking

I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other[vbcol=seagreen]
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>

Any idea what the purpose of this SPROC might be? (no prizes, sorry!)

I've been asked to document an application and I'm going through all
the Stored Procedures and trying to work out what they're supposed to
do.

Can anyone give me an idea of what the Stored Procedure
wsBookingListsGetAll below is trying to achieve? Is it incomplete? I
can't see any reason to pass in the Parameter, and what is the UNION
SELECT 0 all about?

Many thanks

Edward

CREATE Procedure wsBookingListsGetAll
@.DebtorIDvarchar(15)
As
set nocount on

SELECT
fldBookingListID
FROM
tblWsBookingList
UNION
SELECT 0

return

GO

/* Table def */
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblWSBookingList]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblWSBookingList]
GO

CREATE TABLE [dbo].[tblWSBookingList] (
[fldDebtorID] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
,
[fldAddressCode] [char] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldEmail] [varchar] (250) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fldBookingListID] [int] IDENTITY (1, 1) NOT NULL ,
[fldInvoiceNumber] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[fldPayeeID] [char] (15) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GOHi Edward,

It is obvious the SP is returning list of booking list ids. Maybe there are
two scenarios:

1). Incomplete: The SP parameter was intended to filter the list by debtor
id but the code was never completed.
2). Obsolete: In the past the parameter was used properly in the SP but
something required to return the full list all the time and the condition
was dropped from the SQL code. Since the parameter may be passed from the
client application, the developer did not bother to change properly the
client code to drop the parameter, but rather did the change only in the SQL
code and did not comment the change.

As for the union with SELECT 0 it seems like the return list required a
placeholder for some special value (maybe in the client application the list
needs to show "Not Selected" which will be mapped to the 0 value).

Probably the best would be to look at the places where this SP is called and
that will help you to figure out the reasons.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

Any help on application that works like a profiler

Hi
I like to write an application that works kind of like Profiler.
I want to get some sort of event notification that something has happened on
the database server to let me know I should check for data and then log into
some table.
I tried through system stored procedure, but they don't let me directly
trace into the table, for that I have trace into file then from there to a
table.
I want to write something like profiler type application, that should be
continously listening to server events.
Are there any COM objects available for that or some other where through
which I can do this.
Thanks in Advance
PushkarIn SQL Server profiler, you can filter events based on application name or
user, and output the event trace to a table or text file. Once this is in
place, your application can simply query for specific event conditions. Just
in case, if your intention is to implement a complex data constraint, then
use a trigger. Also, if you are wanting to block specific types of user
access to tables or stored procedures, then implement this using appropriate
logins and object level permissions.
"Pushkar" <tiwaripushkar@.yahoo.co.in> wrote in message
news:eFI1SsgZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> I like to write an application that works kind of like Profiler.
> I want to get some sort of event notification that something has happened
on
> the database server to let me know I should check for data and then log
into
> some table.
> I tried through system stored procedure, but they don't let me directly
> trace into the table, for that I have trace into file then from there to a
> table.
> I want to write something like profiler type application, that should be
> continously listening to server events.
> Are there any COM objects available for that or some other where through
> which I can do this.
> Thanks in Advance
> Pushkar
>

Any facility to hold string data of length more than 8000 characters?

Hi,
I have a typical scenario in one of our application.
In a Stored Procedure, I need to get values from different fields and form a
select statement.
The problem here is each field may be upto a length of 8000. So after
forming the final query it will exceed length of 8000. How to concatenate
these field values to form a query which will have length of more than 8000
characters? Is there any data type allows this or is there array concept?
Please provide a solution. Help of any sort is highly appreciated.
Thanks,
Su ManSu,
You concatenate them right at the execution statement:
EXEC(@.str1 + @.str2 + ...)
Ilya
"Su Man" <subu501@.yahoo.com> wrote in message
news:d89f1u$neq$1@.news.mch.sbs.de...
> Hi,
> I have a typical scenario in one of our application.
> In a Stored Procedure, I need to get values from different fields and form
a
> select statement.
> The problem here is each field may be upto a length of 8000. So after
> forming the final query it will exceed length of 8000. How to concatenate
> these field values to form a query which will have length of more than
8000
> characters? Is there any data type allows this or is there array concept?
> Please provide a solution. Help of any sort is highly appreciated.
> Thanks,
> Su Man
>|||But we do not know how many fields to be concatenated. They are dynamic
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:u8uM#YPbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Su,
> You concatenate them right at the execution statement:
> EXEC(@.str1 + @.str2 + ...)
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89f1u$neq$1@.news.mch.sbs.de...
form
> a
concatenate
> 8000
concept?
>|||Su,
What is you statement? Every statement has an end.
Ilya
"Su Man" <subu501@.yahoo.com> wrote in message
news:d89g2r$rfc$1@.news.mch.sbs.de...
> But we do not know how many fields to be concatenated. They are dynamic
> "Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
> news:u8uM#YPbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> form
> concatenate
> concept?
>|||It seems to me, although I am not an expert, that when one has to do
something like this, there is a major flaw in the data model.
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%231HD4fPbFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Su,
> What is you statement? Every statement has an end.
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89g2r$rfc$1@.news.mch.sbs.de...
>|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d89hcb$evf$1$8300dec7@.news.demon.co.uk...

> It seems to me, although I am not an expert, that when one has to do
> something like this, there is a major flaw in the data model.
I couldn't agree more!|||Ilya,
Eevery statement will not have end.
Statement1 may be - select a,c,h,j,e from gggjjjh
Statement2 may be - where t1.a = t2.b
Statement3 may be - group by g,k,l
Statement4 may be - having s = 'value'
Statement5 may be - Order by w,k
The above are just indcative and each statement may be upto a lengh of 8000.
Considering the above,
Statement1+Statement2+Statement3+Stateme
nt4+Statement5 may sum up to 40000
and which data type can hold the string.
Or is there any concept of array where I can hole all individula statements
and finall concatenate to execute.
Please reply.
Thanks,
Su man
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:#1HD4fPbFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Su,
> What is you statement? Every statement has an end.
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89g2r$rfc$1@.news.mch.sbs.de...
and
after
than
>

Any difference between MSDE and SQL Server 2000 :-?

Hi,

I am developing an web-database application using ASP.net,c# and MSDE. and after the completion of the project i want to deploy the database over the server whichs got sql server professional edition.

Are there any changes to be made while deploying it over the server and also will the connection string for MSDE and SQL client the same ?

Please do clear me the confusions i have got ..

thanks inadvance

No difference. Just move it over, change the credentials you use to connect, and all will be well.|||

Hey Hi,

Thanks for ur reply man.. Now i dont have any kinda confusions with these 2, i will continue my work.

Thanks again

Any compatibility issues between SQL 2005 Express and SQL 2005 Server

Hi All,

The IP company that host my site is installing SQL 2005 soon and I my building a web application with SQL 2005 Express. So the question is has anyone heard of any problems with uploading an 2005 express onto a sql 2005 server? I would like to find out now before I finish all of the work. Don't want to find out later that you can't run a sql 2005 express database on a sql 2005 server.

Thanks for taking the time to read this.

Well it seem no one know the answer so I emailed the author of a book called Beginning SQL Server 2005 Express. He emailed me back

SQL Server 2005 Express uses .mdf and .ldf file formats that are compatible with SQL Server 2005. Therefore, if you can upload the .mdf and .ldf files for your SQL Server Express database, you or your isp should be able to attach the files to their SQL Server 2005 database server.

I hope this reply helps.

Rick Dobson

So yes you should beable to upload 2005 express onto a SQL Server 2005.

Thanks away guys

Have a good day

Freon22