How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) ?
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.
SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) ?
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>
|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a crit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.[vbcol=seagreen]
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
a
>

How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) '
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) '
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a crit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
>

How to manage RTE text directly in the DB ?

Hi all,
I store my Rich-Text files in my SQL2000 database.
I want to add a line at the end... it's very easy in my VB application with
a Rich Text control... but how to do that directly in the database (via a
Trigger) '
Does SQL2000 have Rich Text function or show I use an external (Windows)
component ?
Lilian.SQL Server 2000 has no knowledge of the contents of your character strings.
Content could be RTF, HTML XML, etc.
It might be possible to build the desired RTF string in Transact-SQL but I
don't know enough about RTF formatting codes to say for sure. Maintaining
RTF content is a job best done in application code rather than Transact-SQL.
Another option is to write an ActiveX control or extended stored procedure
to manipulate RTF strings that could be invoked from Transact-SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> Hi all,
> I store my Rich-Text files in my SQL2000 database.
> I want to add a line at the end... it's very easy in my VB application
> with
> a Rich Text control... but how to do that directly in the database (via a
> Trigger) '
> Does SQL2000 have Rich Text function or show I use an external (Windows)
> component ?
> Lilian.
>|||Maybe using an OLE object like this example:
http://support.microsoft.com/kb/312839/en-us
... but which object ?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> a écrit dans le message
de news:OYknWxePGHA.3100@.TK2MSFTNGP11.phx.gbl...
> SQL Server 2000 has no knowledge of the contents of your character
strings.
> Content could be RTF, HTML XML, etc.
> It might be possible to build the desired RTF string in Transact-SQL but I
> don't know enough about RTF formatting codes to say for sure. Maintaining
> RTF content is a job best done in application code rather than
Transact-SQL.
> Another option is to write an ActiveX control or extended stored procedure
> to manipulate RTF strings that could be invoked from Transact-SQL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:eMeCdsdPGHA.428@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > I store my Rich-Text files in my SQL2000 database.
> >
> > I want to add a line at the end... it's very easy in my VB application
> > with
> > a Rich Text control... but how to do that directly in the database (via
a
> > Trigger) '
> >
> > Does SQL2000 have Rich Text function or show I use an external (Windows)
> > component ?
> >
> > Lilian.
> >
> >
>sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

how to make Pocket PC synchronization secure?

Hello,
we are going to develop Pocket PC application in C#, targeting Windows Mobile 5.0. The application will use SQL Server Mobile database in the device to synchronize with publication created in SQL Server 2005 database.

What security measures we have to take?

Is the synchronization message send through internet is in encrypted format?

Can we implement digital certificate along with Pocket PC application?

If anybody has an idea about the security implementations with Pocket PC applications, please reply to the problem.

Regards,

Shanavas.

Shanavason,

When you talk about synchronizing data between SQL Mobile and SQL Server 2005, you have 3 main options: Remote Data Access (RDA), Merge Replication, or web services. Assuming you plan to use RDA or merge, the data exchange is compressed but not encrypted. You should use SSL (HTTPS) for your RDA or merge replication dialog between device and server. You can also password protect and/or encrypt your SQL Mobile database on the device to secure any data that might be jeopardized if the device is lost or falls into untrusted hands.

The SQL Mobile books on-line cover the options for securing RDA and merge replication topologies and the connection authentication options available.

-Darren Shaffer

how to make Enforce Password Policy unchecked by default

I am trying to create a login using my application.

The application creates the user and password itself .

when working with sql server 2000 it works fine.

when i try to install my application with sql server 2005 it is displaying the error .

'Password does not meet windows policy requirements

because it is not complex enough'

I want to keep the same password.

Is there any way to disable/uncheck this option by default?

Prashant

You could modify the login creation script to specify CHECK_POLICY=OFF

USE [master]

GO

CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

|||

This is good but for this i need to make changes in my code .My application integrates with sql server 2000,MSAccess ,Oracle.

the code for creating login is same .So can it be possible at sql server level to make Check_policy =OFF as default option.

|||I would be very interested to know this as well. I have a similar issue. If your SQL 2005 server is not part of a domain that enforces group security policies, one option you do have is to relax the default Local Security Policy on the 2003 server upon which your SQL 2005 is running. Using the "Local Security Policy" editor found in Administive Tools you can select "Password Policy" under Account Policies. From there you can change the settings for history, age, length, complexity requirements, and encryption. You will want to turn off the "Password must meet complexity requirements". You might have to change some of the other settings to meet your specific application needs.|||

I would be interested in this as well.

We have a third party Application that the client says works with SQL Server 2005, but the user logon creation they have built in dos not pass a correct password, and turning the poicy off may fix the problem.

|||No, many features which are implemeted in the engine cannot be changed by default:

CREATE LOGIN(...)

CHECK_POLICY = { ON | OFF }

Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

Maybe you post a bug / feature request on the connect forums to make it happen in further versions.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

how to make all Updates\ Inserts in the database Upper case?

I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
> I have a Sql server 2000 database which is already in production. The
client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application.
Does the client really care how it is STORED? You could easily create views
for all the relevant tables which perform UPPER() on varchar columns, and
this would be much easier than using triggers on all the tables to do the
convert on entry, IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)
|||You can do this in several places. Edit your client side code, so that it
converts all the input data to upper case, and then passes to the database.
Another option is to edit your stored procedures, so that they convert the
input data to upper case, before inserting/updating tables.
Yet another option is to have a trigger that converts the data to upper
case, but not really recommended, because of performance implications.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
I have a Sql server 2000 database which is already in production. The client
now want all the data entered from the application to be stored in Upper
case, irrespective of how the case typed it in the application. Is there any
system Database option in the database which when set to On would save all
the data being stored to UPPER CASE.
thanks
Sameer
|||thanks for your help Narayana .
I already did consider these 3 options but i am at a point where with the no
of the database table, stored procs and application, it would take me a long
time to do any kind of programming to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. Again please let me know if there
is any option or any other way other than the once you have metioned which
when i set would force all the data comming in to Upper case.
thanks, waiting for you reply
sameer
"Narayana Vyas Kondreddi" wrote:

> You can do this in several places. Edit your client side code, so that it
> converts all the input data to upper case, and then passes to the database.
> Another option is to edit your stored procedures, so that they convert the
> input data to upper case, before inserting/updating tables.
> Yet another option is to have a trigger that converts the data to upper
> case, but not really recommended, because of performance implications.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "sameer" <sameer@.discussions.microsoft.com> wrote in message
> news:B7CDA2D8-FEAF-4C1B-8E5A-EE8357F8D239@.microsoft.com...
> I have a Sql server 2000 database which is already in production. The client
> now want all the data entered from the application to be stored in Upper
> case, irrespective of how the case typed it in the application. Is there any
> system Database option in the database which when set to On would save all
> the data being stored to UPPER CASE.
> thanks
> Sameer
>
>
|||thanks for your help Aaron.
I already did consider this option but i am at a point where with the no of
the database table, stored procs and application, it would take me a long
time to do any kind of developement to implement any of these 3 options. And
that is why i was curious if there is any Option in the database like the
'case-sesitive' Coalition which when set to ON would convert all the data
being stored in the datase to Upper case. again, please let me know if there
is any such switch in the database.
thanks
sameer
"Aaron [SQL Server MVP]" wrote:

> client
> Does the client really care how it is STORED? You could easily create views
> for all the relevant tables which perform UPPER() on varchar columns, and
> this would be much easier than using triggers on all the tables to do the
> convert on entry, IMHO.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||No, there are case-sensitive collations, but there is no global switch that
will force all data to upper case, sorry.
Tell the client if they really want this that there will be development time
involved.
http://www.aspfaq.com/
(Reverse address to reply.)
"sameer" <sameer@.discussions.microsoft.com> wrote in message
news:53744680-6679-4B86-A90B-51B80FEFA47F@.microsoft.com...
> thanks for your help Aaron.
> I already did consider this option but i am at a point where with the no
of
> the database table, stored procs and application, it would take me a long
> time to do any kind of developement to implement any of these 3 options.
And
> that is why i was curious if there is any Option in the database like the
> 'case-sesitive' Coalition which when set to ON would convert all the data
> being stored in the datase to Upper case. again, please let me know if
there[vbcol=seagreen]
> is any such switch in the database.
> thanks
> sameer
> "Aaron [SQL Server MVP]" wrote:
Upper[vbcol=seagreen]
views[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]

How to maintain users permission and access level

Hi guys,

We have a scenario where there are about 50 tables in our database and we want to build an intranet web application for users to with the office to access those tables.

Users ability to access tables falls into diferent category:

Some users can NOT view some tables at allSome users can ONLY view some tables but not insert/update any fieldSome users can view and also insert/update some tables (in the same time they might not have view(select) permision on some other tables)

Now, what is the right way to implement this.

I say we have to have a Role, RolePermission, User, UserPermission inside our database to implement this (something which would look like the Roles and Users inside MSSQL) and we only have one user for our Database (MachineName/ASPUSER) to access the database and all the tables within

My colleague says NO, instead of creating all these tables and implement this, we add every user of our application as a Database user inside MSSQL in the Databse Users.

All the web application I have seen so far, DNN, CommunityServer, ... the have tables to implement all these and they don't add users inside the MSSQL.

Now which way is the way to go with, and what problem might we fall into if we use SQL users, is this possible at all. How can I convince him that we have to make and use our own tables to manage this.

Thanks for any help,
Mehdi

If you are using asp.net 2.0 you can use login controls and restrict the users to access particular forms.

Other way is to use Windows based authentication. As you are creating Intranet based application...every user will have windows ID. You can restrict the user to access the database.

To learn about Windows Based Authentication, visit the following link:

http://msdn2.microsoft.com/en-us/library/aa480475.aspx

To learb about login controls, watch the video tutorial for Membership and Roles

http://www.asp.net/learn/videos/default.aspx?tabid=63#howdoi

|||go to http://www.codeproject.com/csharp/cgsecurity3.aspit was presented a library for managing user-level credentials

how to maintain database concurrence?

My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .

i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.

I hope you have uderstand my requirement.Avoid locking whenever possible! Use optimistic concurrency (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcoptimistic_concurrency.asp) instead of relying on locking.

-PatP

How to log uses of SELECT

Hello,
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>

How to log each database access (SQL 2000 / 2005)

Is it possible to log each database access in SQL 2000?
We use a third-party application with a limited number of licences. Some
users add / modify / delete data in the database, some just read it. As we
are running out of licences we would like to eliminate those users, who
havn't access the database for a certain time. The question is: how to find
out when the last activity of the user occured in the database.
Since the application is third-party we cannot modify it, so the solution
must based solely on SQL Server mechanisms.
If the above is not possible in SQL Server 2000, does SQL 2005 offer more
(please note that we need to log all accesses to the database including
reads)?
Thank you very much for any help!
Best regards,
AndrewYou can run Profiler Trace in sql server 2000 and save the result in
table. Then query the table for specific user.
NJ
Andrew Drake wrote:
> Is it possible to log each database access in SQL 2000?
> We use a third-party application with a limited number of licences. Some
> users add / modify / delete data in the database, some just read it. As we
> are running out of licences we would like to eliminate those users, who
> havn't access the database for a certain time. The question is: how to find
> out when the last activity of the user occured in the database.
> Since the application is third-party we cannot modify it, so the solution
> must based solely on SQL Server mechanisms.
> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
> (please note that we need to log all accesses to the database including
> reads)?
> Thank you very much for any help!
> Best regards,
> Andrew|||Thank you! It is a very good idea, the only problem could be that I have to
collect data over a long period, that is a couple of months, and I am not
sure if I can run Profiler Trace as a service on an SQL Server machine
(runing an interactive (desktop) application for several months and locking
down the server wouldn't work in our environment).
But I will definitely give it a try.
Thanks again!
Best regards,
Andrew
> You can run Profiler Trace in sql server 2000 and save the result in
> table. Then query the table for specific user.
> NJ
> Andrew Drake wrote:
>> Is it possible to log each database access in SQL 2000?
>> We use a third-party application with a limited number of licences. Some
>> users add / modify / delete data in the database, some just read it. As
>> we
>> are running out of licences we would like to eliminate those users, who
>> havn't access the database for a certain time. The question is: how to
>> find
>> out when the last activity of the user occured in the database.
>> Since the application is third-party we cannot modify it, so the solution
>> must based solely on SQL Server mechanisms.
>> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
>> (please note that we need to log all accesses to the database including
>> reads)?
>> Thank you very much for any help!
>> Best regards,
>> Andrew
>

How to log disconnects?

I need to log user disconnects for a specific DB. I do not know how to
do this relabily. The application owner is under the impression that
our SQL cluster is disconnecting their users. Which I am fairly
confident is not the case.
Any ideas on how to do this? Specifically disconnects that are not
'requested' but either forced or lost.
Thanks!Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!|||That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:
> That kind of logging 'should' happen on the client. For a limited time span,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connection
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
> >I need to log user disconnects for a specific DB. I do not know how to
> > do this relabily. The application owner is under the impression that
> > our SQL cluster is disconnecting their users. Which I am fairly
> > confident is not the case.
> >
> > Any ideas on how to do this? Specifically disconnects that are not
> > 'requested' but either forced or lost.
> >
> > Thanks!
> >

how to lock a table?

hi, I have a problem on building an application that can be accessed by
multiple clients concurrently. I have a table called [sub_bg_id_allocation],
it has 2 int columns: bg_id and sub_bg_id. The business rule states that
sub_bg_id should be unique within a bg_id, e.g.
bg_id subbg_id
--
1 110
1 111
1 112
2 110
2 111
... ...
the subbg_id is started by 110 and incremented by 1 within a bg_id, actually
this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. The
newly allocated sub_bg_id will be used for inserting a 'real' data record in
another table called [pro_child_subbg_info]. (don't ask me why I need the
[sub_bg_id_allocation] table, it's already there and used by existing system
)
Now I write a stored procedure like this:
CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
@.bg_id int,
@.subbg_id int output
@....
AS
BEGIN
BEGIN TRAN
-- calculate subbg_id
declare @.last_subbg_id int, @.subbg_id int
select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgid
= @.bg_id
if @.last_subbg_id >= 110
set @.subbg_id = @.last_subbg_id + 1
else
set @.subbg_id = 110
-- subbg_id allocation
insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- subbg info.
insert pro_child_subbg_info (
bg_id, sub_bg_id, ...) values (
@.bg_id, @.subbg_id, @....)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- end successfully
COMMIT TRAN
RETURN 0 -- ***** 0 means ok *****
ErrorHandler:
ROLLBACK TRAN
RETURN -1 -- ***** -1 means fail *****
Is there any problem in a concurrent client scenario? I wonder about the
MAX() function works properly in concurrent case, should I lock the
[sub_bg_id_allocation] table within this stored procedure? How can I lock it
?
Thanks a lot!Hi
You may want to add a hint such as UPDLOCK to the select statement.
http://msdn.microsoft.com/library/d...br />
4ox9.asp
This will potentially reduce concurrency such as
http://support.microsoft.com/defaul...kb;en-us;179362
so you may want to look at using an identity instead.
John
"nonno" wrote:

> hi, I have a problem on building an application that can be accessed by
> multiple clients concurrently. I have a table called [sub_bg_id_allocation
],
> it has 2 int columns: bg_id and sub_bg_id. The business rule states that
> sub_bg_id should be unique within a bg_id, e.g.
> bg_id subbg_id
> --
> 1 110
> 1 111
> 1 112
> 2 110
> 2 111
> ... ...
> the subbg_id is started by 110 and incremented by 1 within a bg_id, actual
ly
> this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. T
he
> newly allocated sub_bg_id will be used for inserting a 'real' data record
in
> another table called [pro_child_subbg_info]. (don't ask me why I need the
> [sub_bg_id_allocation] table, it's already there and used by existing syst
em)
> Now I write a stored procedure like this:
> CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
> @.bg_id int,
> @.subbg_id int output
> @....
> AS
> BEGIN
> BEGIN TRAN
> -- calculate subbg_id
> declare @.last_subbg_id int, @.subbg_id int
> select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgi
d
> = @.bg_id
> if @.last_subbg_id >= 110
> set @.subbg_id = @.last_subbg_id + 1
> else
> set @.subbg_id = 110
> -- subbg_id allocation
> insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- subbg info.
> insert pro_child_subbg_info (
> bg_id, sub_bg_id, ...) values (
> @.bg_id, @.subbg_id, @....)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- end successfully
> COMMIT TRAN
> RETURN 0 -- ***** 0 means ok *****
> ErrorHandler:
> ROLLBACK TRAN
> RETURN -1 -- ***** -1 means fail *****
> Is there any problem in a concurrent client scenario? I wonder about the
> MAX() function works properly in concurrent case, should I lock the
> [sub_bg_id_allocation] table within this stored procedure? How can I lock
it?
> Thanks a lot!|||Thanks John. How can I use identity in this case?
"John Bell" wrote:
> Hi
> You may want to add a hint such as UPDLOCK to the select statement.
> http://msdn.microsoft.com/library/d... />
z_4ox9.asp
> This will potentially reduce concurrency such as
> http://support.microsoft.com/defaul...kb;en-us;179362
> so you may want to look at using an identity instead.
>
> John
>
> "nonno" wrote:
>|||Hi
That would be a change in functionaility in that the subbg_id will be
continually increasing. Does the subbg_id really need to do what you are
trying to build?
When needed you can still rank the entries by using something
select a.bg_id, subbg_id as original_subbg_id, (select count(*) from
sub_bg_id_allocation b where a.subbg_id < b.subbg_id ) + 110 as
ranked_subbg_id FROM sub_bg_id_allocation a
John
"nonno" wrote:
> Thanks John. How can I use identity in this case?
> "John Bell" wrote:
>

how to lock a table or row in sql?

hi, i have an application that updates some records in sql tables, and i want to do a web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table so i dont have concurrency problems.
tnx in advance.Have you tried google? Search fordatabase concurrency.
It's not a simple answer. There are different types of concerrency and different levels of locks. You need to undestand what is out there. Once you get the difference, you'll probably know what to use and how to do it.
Now go. Google it.|||

ok , i will try doing something like in this article..

http://www.15seconds.com/issue/030604.htm

tnx Alex.

How to load a Legacy DTS Package from .NET?

If I have an object of type "Microsoft.SqlServer.DTS.Runtime.Application,"

can I use one of the following functions
- ExistsOnDTSServer
- ExistsOnSQLServer
- LoadFromDTSServer
- LoadFromSQLServer
- LoadFromSQLServer2

to interact/load with a Legacy (SQL 2000) DTS package, stored on a SQL 2005 machine?

If so, can someone post an example first-argument (the Package Path) ?
Thanks.

I suspect that this is not possible.

Why would you not use the DTS object model to load and run a DTS package? Although it's a COM library, you can certainly use it from .NET...

-Doug|||You're probably right. It was worth someone verifying before assuming correctly/incorrectly.

The "Microsoft DTSPackage Object" COM object is an adequate alternative.
But this would require SQL 2000 Client Tools to be installed on the machine, in addition to SQL 2005 Client Tools, to gain use of both objects.

How to leverage custom security with ReportViewer and SSRS Web Service

My company is building a WindowsForms application that will use SSRS 2005 for it's reporting needs. The application will use ClickOnce and will run in an extranet type of environment against a centralized database designed in an "ASP" fashion with key, customer-specific tables marked with an "OrgId."

The authentication/authorization within the app will be with custom classes implementing IPrincipal and IIdentity and leveraging the built-in .NET security framework. Because of this, I have come to the conclusion that we will have to author a custom security extension for SSRS.

Now, none of this is rocket science, but we would like to use the Windows Forms ReportViewer control within our app, in conjunction with the Reporting Services Web Service and I have yet to find a decent example that illustrates how to utilize custom security extensions with the web service and the reportviewer.

Any suggestions, tips, tricks, pitfalls?

Thanks,
Matthew Belk

This stuff is in BOL, but it's not very discoverable.

Here's an example of using the SSRS Forms Auth security extension in conjunction with the ReportViewer:

http://blogs.msdn.com/bimusings/archive/2005/11/04/489100.aspx

Here's an example of using the Forms Auth Security extension with the SSRS webservice (basically, just calling LoginUser(): implemented in the security extension)

http://blogs.msdn.com/bimusings/archive/2005/08/04/447939.aspx

Hope this helps

|||Thanks for the blog pointers.

Now, the next logical question is how to leverage the custom security extensions to restrict access to various items within the SSRS space so that the custom "CheckAccess" routines from the sample code will work properly.

I'd love to let SSRS handle this, but if the answer is "You have to do that from your app," then that's OK, too.

Thanks,
Matthew Belk
|||

Have you explored the Forms Auth security extension sample yet? If not, I would -- You could pretty much use 60-70% of it for your purposes (authorization included)...The only changes you'd have to make is how LogonUser gets handled, etc.

How to launch them?

Hi everyone,

Primary platform is Framework 2.0

Our vb application throws .dtsx by means of the usual methods.

When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when

I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.

Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from

Database Engine->Management->Legacy->Data Transformation Services.

How do I launch this kind of SSIS packages?

I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?

Thanks in advance for your input,

If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.

Hope this helps,
Ovidiu Burlacu

|||

so you're talking about that they really are dts no ssis. ok

How to launch them?

Hi everyone,

Primary platform is Framework 2.0

Our vb application throws .dtsx by means of the usual methods.

When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when

I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.

Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from

Database Engine->Management->Legacy->Data Transformation Services.

How do I launch this kind of SSIS packages?

I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?

Thanks in advance for your input,

If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.

Hope this helps,
Ovidiu Burlacu

|||

so you're talking about that they really are dts no ssis. ok

How to know What Query Statement that consume the Server resources

Hi all,
We have problem with SQL Server 2000 : there something in the
application that cause the server out of memory. I suspect this is
because an inefficient and heavy query.
In Oracle we have TOP SQL from Enterprise Manager to see what query
that consume the server resources most, How can I get that information
in SQL Server 2000 ?
Thank you for your help,
KristHi
It is possible that long running queries and /or queries that performs lots
READs are consumed resources. SQL Server Profiler tool is your friend
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>|||To really get a look at the internals of SQL executions and resource usage
you need to be on SQL 2005+. Not much info available in 2000.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<krislioe@.gmail.com> wrote in message
news:157f6947-088e-4bf7-bcad-753ceff7be3c@.s37g2000prg.googlegroups.com...
> Hi all,
> We have problem with SQL Server 2000 : there something in the
> application that cause the server out of memory. I suspect this is
> because an inefficient and heavy query.
> In Oracle we have TOP SQL from Enterprise Manager to see what query
> that consume the server resources most, How can I get that information
> in SQL Server 2000 ?
> Thank you for your help,
> Krist
>