What Is The Purpose Of Using WITH NOLOCK
Aug 12, 2013What is the purpose of using WITH(NOLOCK)?
View 3 Replies
ADVERTISEMENT
What Is The Purpose Of Having SqlDataReader Here?
Jul 23, 2007May I know what is the purpose of having SqlDataReader in Example A? I can see the same output when I tried out both. Should I use Example A or Example B? Currently, I'm using Example B since it is lesser code.Example A Dim objDR As SqlDataReader 'Create Data Reader
LoginConn.Open()
strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName "
cmd = New SqlCommand(strSQL, LoginConn) objDR = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataSource = objDR ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close() Example BLoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) ddlNationality.DataSource = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close()
SQL Replication For DR Purpose
Sep 26, 2005Can you really use the SQL replication to mirror data to another server for the purposr of DR ? What is the best method ? I would want to be able to switch to DR very quickly, allowing users to continue work. Or is there something I should know ? I would be very grateful for any help. :D
View 3 Replies View RelatedPurpose Of The Sql Row Type
Oct 30, 2007From a research paper I got to know that in transformation of one to many relation ship, in the "many type we have collection atribute consists of attribuet key from the one type. If we have a single key we will use a collection of simple type. Otherwise, we will have a collection of ROW type.
My probelm here is,is there any conditions to be cecked before using ROW type?Can't we use MULTISET instead of ROW type alone?
Please help me out by ansereing this.
Thanks
What Is Purpose Of N In SQL Examples?
Jul 23, 2005What is the purpose of the "N" preceding the parameter values in theSQL examples?Here is an example copied from Books Online, SP_ATTACH_DB:EXEC sp_attach_db @dbname = N'pubs',@filename1 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs.mdf',@filename2 = N'c:Program FilesMicrosoft SQLServerMSSQLDatapubs_log.ldf'I've found that my sp_attach_db routine works without the "N", but Ineed to know what it is that I don't know.Thank you everybody for all your help.
View 1 Replies View RelatedPurpose Of 0E0, 100E0
Mar 28, 2008In Chapter 7 of Microsoft Press "Inside Microsoft SQL Server 2005: T-SQL Querying" about use of TOP and APPLY, the authors use "TOP(0E0) PERCENT to TOP(100E0) ". What is the purpose of this 0E0, 100E0 notation?
PURPOSE OF TRIGGERS IN SQL SERVER
Feb 21, 2008WHY DO WE USE TRIGGERS IN SQL SERVER2005.
WAT IS ITS IMPORTANCE.
AND SOME SAMPLES
PLEASE GIVE ME SOLUTIONS
Purpose Of Dtproperties Table
Feb 4, 2002What is the purpose of the dtproperties table? I looked in BOL and the references are pretty thin.
View 1 Replies View RelatedWhat Is The Purpose Of A Database Owner
Dec 28, 2013What is the purpose of a database owner i.e. the dbo.DBNAME that comes before the name of the database?
View 7 Replies View RelatedWhat Is The Purpose Of Cube And ROLLUP?
Mar 30, 2007Hai all !
What is purpose of Cube and Rollup?
Can u explain with small example?
Regards
Umapathy
Umapathy
What Is The Purpose Of The ROWGUIDCOL Property?
Dec 4, 2007Greetings,What is the point of Microsoft defining a ROWGUIDCOL property that canbe attached to a 'uniqueidentifier' column? This is defined as a columnthat is 'globally unique', but doesn't the uniqueidentifier datatypealready guarantee that? To make matters more confusing, they tell youin Books Online to add a Unique constraint because ROWGUIDCOL does notguarantee uniqueness...so what's the point?Apparently the only functionality attached to this property is that onlyone such column can exist per table and that it can be queried using the$ROWGUIDCOL keyword in SQL.Can anyone tell me the rationale for when to use this and when not to,or what the purpose of this property is?Thanks,Sam BendayanDB ArchitectUltimate SoftwareJoin Bytes!*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedUser Without Login Purpose
Nov 16, 2006HI, we have a database that has a user created without login. I was wondering why using a user without login? Is there a reason for this? Can we use this user and how since it is not mapped to any login?
Thank you,
Ccote
What Is Purpose Of Semi Colon After Procedure Name
Jul 23, 2005I've seen some system sp's that have a semi colon and number after thethe name such ascreate procedure sp_procedure_params_rowset;2what does this do?
View 1 Replies View RelatedPurpose Of OUTPUT Keyword In Sql Server
May 17, 2006hello sirspurpose OUTPUT keyword with examples
View 3 Replies View RelatedPurpose Of Writing Dates In This Format
Jan 15, 2007hi Experts,what is the purpose writing a date in the following format:where x.event_date >= {ts '1980-01-01 00:00:00'}versus like this:where x.event_date >= '1980-01-01 00:00:00'what benifit does it add to later form of writing?Thanks in advance.schal.
View 4 Replies View RelatedAny Idea What The Purpose Of This SPROC Might Be? (no Prizes, Sorry!)
Jan 19, 2007I've been asked to document an application and I'm going through allthe Stored Procedures and trying to work out what they're supposed todo.Can anyone give me an idea of what the Stored ProcedurewsBookingListsGetAll below is trying to achieve? Is it incomplete? Ican't see any reason to pass in the Parameter, and what is the UNIONSELECT 0 all about?Many thanksEdwardCREATE Procedure wsBookingListsGetAll@DebtorIDvarchar(15)Asset nocount onSELECTfldBookingListIDFROMtblWsBookingListUNIONSELECT 0returnGO/* 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]GOCREATE 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]GO
View 1 Replies View RelatedConvert 0 Or NULL To 1 For Devision Purpose
Jul 20, 2005hi,I have data like 0, null or some value in one column. I want to usethis column for devision of some other column data.It gives me devision by 0 error.how can I replace all 0 and null with 1 in fly.thanks in adv.t.s.negi
View 2 Replies View RelatedWhat Is The Purpose Of The Rptproj.user File?
Dec 20, 2007I'm concluding that...
.rdl files are critical and should be recorded in source control
.rdl.data is useful only in preview (saving trips) and therefore should not be recorded in source control
.rptproj file has useful datasource, path and deployment info and should be recorded in source control
.sln contains useful solution container info and should be recorded in source control
.suo contains user specific setting/preference info and should not be recorded in source control
.rds contains important data source info and should be recorded in source control
...but cannot find info on the purpose of rptproj.user file and therefore dont know if recording such files in source control is useful.
What Is The Purpose Of [Reporting Currency].[Local] ?
Apr 28, 2007Hello,
Below is the many to one currency conversion MDX query generated by BI wizard. I am having hard time understanding the purpose of [[Reporting Currency].[Local]. Can anyone explain, why do we need [Reporting Currency].[Local], how does it work in the conversion and where are the relationships established between [Reporting Currency].[Local] member and other measures/dimensions? Thanks.
// This is the Many to One section
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension
Scope
(
{ Measures.[Internet Sales Amount], Measures.[Reseller Sales Amount]} );
Scope( Leaves([Date]) ,
[Reporting Currency].[USD],
Leaves([Destination Currency]));
// Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Average Rate]
Scope( { Measures.[Internet Sales Amount], Measures.[Reseller Sales Amount]} );
This = [Reporting Currency].[Local] * Measures.[Average Rate];
End Scope;
End Scope;
// This is the One to Many section
// All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
Scope
(
Leaves([Date]) ,
Except
(
[Reporting Currency].[Destination Currency Code].[Destination Currency Code].Members,
{
[Reporting Currency].[Destination Currency Code].[Destination Currency Code].[USD],
[Reporting Currency].[Destination Currency Code].[Destination Currency Code].[Local]
}
)
);
- SB.
Get The Search Term From A URL For Reporting Purpose
Jul 10, 2007
Hi guys,
I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.
Examples of the URLs from google :
http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables
Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).
Thanks in advance.
NOLOCK Or Not To NOLOCK
Mar 9, 2005I need some help to under stand when the right time is for NOLOCK. I work in a small dev group and NOLOCK seams to be a buzz word and others are throwing it in all over for no apparent reason.
I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.
Also I am not even sure it’s being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)
However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)
I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it’s the bomb!
Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.
Thanks
NOLOCk
Jan 29, 1999Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?
View 1 Replies View RelatedDTS- WITH (NOLOCK)
Mar 7, 2001Hi,
I have a job that runs 3 seperate DTS packages.
The first step imports a file and runs successfully.
The second step which is the 2nd DTS package is hanging in the execute mode until I manually stop the job. Apparently,We discovered a bulk insert that is blocking a select statement--both proccesses are within this second DTS package. I tried using the WITH (UNLOCK) on the tables but this DTS package is still failing.
Does anyone have any suggestion? It would be greatly appreciated.
Db2, Nolock
Mar 4, 2004Hi guys,
I am a new user for DB2.
In DB2 V8, i have a table with some no of columns.
table test
col1
col2
col3
col4
Now i want to issue a query like this.
select col1 from test with(NOLOCK) where col1 = <some value>
when i am executing this query on that time i am getting a error message like this...
SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811
Anyone can help me???
i tried to find out.
I did the same thing with SQL server 2000.
I am not facing any problem there.
Thanks & Regards,
Muthu
NOLOCK Help
Jun 6, 2006Paul Randal,
There has been a discussion/debate going on this thread about the benefits and drawbacks of using the NOLOCK hint:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294
It occurred to me that you might know more about this than any of us, or at least be able to point us to a white paper or knowledge base article that explains the subject in more detail. Any light you can shed on the subject would be a big help.
CODO ERGO SUM
With (nolock)
Jan 8, 2008
is really helpful use "with (nolock)" sentence in selects??
100% useful?? disadvantages?
best regards
Joseph
Incorrect Use Of Update Trigger For Audit Purpose
Jan 24, 2001I have a update trigger on a table which fires trapping the userid and time in the same record. The code is below. The tirgger only works if I have the recursive triggers option unchecked on databae properties. Is the way I am trying the only way to accomplish this in the update trigger or is there an more efficient way. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
Create An Extra Table (for Audit Purpose)
Mar 28, 2008Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
Purpose Of NT AUTHORITYSYSTEM Login In SQL Server 2005
Jan 29, 2007Hi All
Does anybody know what the "NT AUTHORITYSYSTEM" login create during a SQL Server 2005 instillation is used for?
Does this login pose a security risk, and can it be removed safely? It seems to me as if it is similar to the "BultinAdministrator" login which we remove from our production servers?
Regards
Stevo
Purpose Of OLEDB Source Error Output?
Nov 6, 2007
This might be an ignorant question, but I can't figure this out.
What is the purpose of the Error output data flow from the OLEDB Source? I am trying to understand an example of what kind of "error" would cause a row to go down this path, and I can't come up with one.
Does anyone have a good example of how this could be useful?
SQL Express License Hosting For Business Purpose?
Apr 25, 2006Hi every one,
SQL Server 2005 Express License allow hosting for business purpose, as DB size is limited to 4 GB?
Any restriction or limitation ?
regards
Transact SQL :: Self Linked Server For Purpose Of Alias?
Oct 2, 2015 basically, we have two databases, one with fix name, eg. DB1 in all environments, and another is named with prefix based on environment, eg. DB2_DEV, DB2_TEST, that is generated by some managed application.
Then we have queries or view/SPs residing in the first database, eg. DB1 that access database resources from the second database, DB2_***. Both databases are residing in the same SQL instance.
Currently, we have hardcoded the database name in the query but would prefer not to manually rename or write other scripts to update the query with the correct database name when deploying to other environments.
For example:
SELECT * FROM DB2_DEV.dbo.vAccounts
When deploy to TEST, we need to update the query to:
SELECT * FROM DB2_TEST.dbo.vAccounts
So, I looked at Synonyms but it seems it can only create 'alias' for tables or views, and not database.
Hence, last thought is to create self linked server, so I can write my query like below and setup the linked server accordingly.
SELECT * FROM SRCDB.dbo.vAccounts
Any consideration to think of, eg. performance, security, etc?
NOLOCK On Views
Jun 2, 1999Hello,
Does anyone know if you place NOLOCK after a view in a select statement, if the effects trickle down to the tables in the view? Or does one have to add NOLOCK to each table within the view?
Thanks