In our company we tend to us views and store procedures. We've recently started to implement the NOLOCK statement to alot of our views. i was wondering if applying NOLOCK to a view, it "trickles down" to the store procedure... Say i have a view call viewPartyPackage and view statement was... SELECT PartyPackageID, Name, Created,...
I need to clear many tables (preferably truncate table). But tables have many FK constraints. I tried something like this, but failed:- ALTER TABLE Table1 NOCHECK CONSTRAINT ALL TRUNCATE TABLE Table1 ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT ALL This is the error i am getting:- Cannot truncate table 'Test' because it is being r...
Hi I need help with the syntax on this code: IF OBJECT_ID('TEMPDB..#LTS_MAP') IS NOT NULL BEGIN DROP TABLE #LTS_MAP END SELECT dtMicIssue as LATE_CHARGE_FACTOR ,CASE WHEN (dtMicIssue is NOT NULL) AND (dtMicIssue <> '1900-01-01 00:00:00') THEN CONVERT(SMALLDATETIME,dtMicIssue) ELSE NULL END INTO #LTS_MAP FROM SBA...
T-SQL is nice. But I need a more flexible scripting language in Sql Server like javascript. Can anyone help? ...
Hello, Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!) I have two tables on SQL Server 2005 TABLE 1 and TABLE2: TABLE1 COL1 COL2 1 10 2 20 3 30 4 10 4 20 5 20 6 30 7 10 7 20 TABLE2 COL1 CO...
I need to do the following query (for example): SELECT c1.CustomerName FROM Customer as c1 INNER JOIN [ExternalServer].[Database].[dbo].[Customer] as c2 ON c2.RefId = c1.RefId For some security reason my client doesn't allow me to create a linked server. The user under whom I execute this query has access to both tables. Is it possibl...
I'm very new to SQL so forgive my ineptitude. I'm trying to write a trigger that, on insert to table 1, inserts that record into table 2. table 1 is [rps_lab_dev].[dbo].[Lab_Employee_Time_Off] table 2 is [dbo].[Lab_Employee_Time_Off_Detail] CREATE TRIGGER updatetrig ON [rps_lab_dev].[dbo].[Lab_Employee_Time_Off] FOR INSERT AS ... ...
64 bit windows server 2008, sql server 2005. 4 processors. Database is in simple recovery model- when the following batch script is run, the transaction starts to rollback at "ALTER TABLE PS_RC_CASE_AUDIT ADD Z_TMP_ALTER_1 NVARCHAR(80) NULL" - the following are the wait types observed. We have had to roll back this transaction already a ...
Is there a way to call a HTTP web service from T-SQL (no SQLCLR) in Sql Server 2008? I just need to send information out, I do not need to receive anything into T-SQL. Thanks. ...
Hi all, I have 3 columns/fields as part of a generic list: strID, seq, unit there are a bunch of strIDs that belong to the same unit and they each have a different seq. I'm interested in the row that has the minimum seq per unit(the leader of the group). How would I accomplish this with a LINQ query (a tsql query would also be fine)...
Hi I have a script which creates a database, stored procs, views, tables, udf. I want to include a script to create a user 'user_1' and give execute permission on the database. update I tried following to create grant exec command for all stored procs use DB; go declare @permission varchar(max) select @permission = COALESCE(@permi...
I have a Notes table with a uniqueidentifier column that I use as a FK for a variety of other tables in the database (don't worry, the uniqueidentifier columns on the other tables aren't clustered PKs). These other tables represent something of a hierarchy of business objects. As a simple representation, let's say I have two other tabl...
Below is my query which is updating a record in the User table , I want the query to return the UserId which was updated, How can I do this? UPDATE USER SET GroupID = @New_GroupID FROM USER LEFT OUTER JOIN DOCUMENTS ON User.UserID = Documents.UserID WHERE (Documents.UNC = @UNC) AND...
I have column of strings with a ctiy state and number in each. SPOKANE, WA 232/107 LAS VEGAS, NV 232/117 PORTLAND, OR 232/128 Theres many more than just that but I am wondering how either I could cut off the numbers in this column and jsut show the city and state or even better cut off the numbers and make c...
whos could be much efficient if I use nestted subquery, JOINs Or maybe temp tables .. another question : in subqueries if i use IN Clause twice with the same query it should be execute a twice too !? like this : Select ... From X Where Exists( Select 1 From Y Where Idx = Y.SomeColumn ) Or Exists( Select 1 From Y Idy = Y.SomeColumn ...
Sorry, I am pretty much an SQL noob. This has to work in MSFT SQL, Oracle as well as Sybase. In the following snippet I need to change an inner join between IJ and KL on IJ.PO_id = KL.PO_id into a left join also on IJ.PO_id = KL.PO_id. So, I believe I have to re-factor this. Well, implicit joins are not the most readable, at least in my ...
Hello everyone. I have a table containing user-account permissions and I'm trying to write a query to return one row for each user-account combination. Here is what I have. CltKey AcctKey TranTypeID Access 10 2499 10 0 10 2499 11 1 10 2499 12 1 10 2764 10 1 10 ...
We are using SQL Server 2008. We have a table called response which has a primary key called response_id. It also has a column called bid_id. When we execute the query ‘select * from response where bid_id = x’ without an ‘order by’ we are getting results in mostly ascending order (default), but once in a while in descending order (ve...
Hey there I'd like to create a synonym on a local db that points to a db on the web that's only accessible with a user name and password. Is there a way to set up a synonym for that kind of situation? I would be very grateful for an example if this is even possible... Thanks ...
CREATE USER [anc] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[anc] GO what is the difference between the user and the login. i am not clear as to y do we create these kind of users?? ...