What does TOP 1 mean in an sql query?
What does TOP 1 mean in an sql query? SELECT TOP 1 RequestId FROM PublisherRequests ...
sql-server
sql server 2005:is it safe to use @@identity?
i have a procedure in which i am inserting record in employee table.nad getting empid by using @@identity ? when this procedure will be called by more than one user at same time,there can be possibility that it returns identity of some other employee inserted at same time.because there is no lock on identity by system? --code --identit...
Why is my SqlCommand returning a string when it should be an int?
I have a query that should always be returning a single int. I have now logged it returning a string entirely unrelated to what it should be. We've been getting some random FormatExceptions that we've tracked down to several database queries. After some additional logging, I found that, this morning, the query below returned the string ...
Is deleting all records in a table a bad practice in SQL Server?
I am moving a system from a VB/Access app to SQL server. One common thing in the access database is the use of tables to hold data that is being calculated and then using that data for a report. eg. delete from treporttable insert into treporttable (.... this thing and that thing) Update treportable set x = x * price where (...etc) ...
SQL Server SET DATEFIRST scope
I'm using SS 2005 if that I've seen sample code like DECLARE @restore = SELECT @@DATEFIRST SET DATEFIRST 1 SELECT datepart(dw,ADateTimeColumn) as MondayBasedDate,.... FROM famousShipwrecks -- SET DATEFIRST @restore Suppose while the query is running another query sets DATEFIRST? If another query relies on datefirst being 7 (for exam...
Running Sql Server stored proc in context of caller
This is making me nuts and I'm sure the answer is SO easy. I have multiple schemas that each have a view named "Task". I want to make a single stored proc that users running in multiple default schemas can execute successfully -- that stored proc does a select on the Task view. So say I have these objects: View: fr.Task (users with d...
How to fetch a file from an FTP site using a stored procedure
I want to create a stored procedure (on SQL Server 2005) that fetches a file from an FTP site, saves it locally and then runs an SSIS package to import the contents of the file into a table. I'm after some suggestions on how to fetch the file by calling a stored procedure. Should I use SQL CLR, call an SSIS package that does it, xp_cmds...
Import DBF files into Sql Server
I need a little help figuring this out because I'm new to stored procedures. I am trying to import a .DBF table into Sql Server 2008 using this store procedure. CREATE PROCEDURE spImportDB -- Add the parameters for the stored procedure here AS BEGIN -- Insert statements for procedure here SELECT * into Products FROM OPENROWSET('vfp...
How to determine which table of a multitable view are updatable?
Update: My problem doesn't seem to be with the SQL server. I executed an update statement inside the database manually against the view and I was able to update the Bar table. I'll close this and research the OleDbDataAdapters more since I think the problem lies with them. This question applies to MS SQL Server 2000. How do I determi...
Login Problem Windows Authentication
Duplicate of: http://stackoverflow.com/questions/881928/windows-authentication-trusted-connection-problem I logged in the Windows Server(Machine 1) as "abc\user1 ". Windows Server machine is in abc domain. MSSQL Server is in the "abc" domain on Machine 1 and have mixed mode.authentication. It has account "abc\user1 " and "abc\user2 ". ...
How do you model custom attributes of entities?
Let's say we're having an application which should be able to store all kind of products. Each product has at least an ID and a Name but all other attributes can be defined by the user himself. E.g. He could create a productgroup Ipods which would contain attributes capacity and generation E.g. He could create a productgroup TShirts wi...
How can I get this query to return 0 instead of null?
I have this query: SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID FROM tblTransaction GROUP BY tblTransaction.TenantID But there's a problem with it; there are other TenantID's that don't have transactions and I want to get those too. For example, the transactio...
MERGE INTO insertion order
I have a statement that looks something like this: MERGE INTO someTable st USING ( SELECT id,field1,field2,etc FROM otherTable ) ot on st.field1=ot.field1 WHEN NOT MATCHED THEN INSERT (field1,field2,etc) VALUES (ot.field1,ot.field2,ot.etc) where otherTable has an autoincrementing id field. I would like the insertion into ...
TSQL: Try-Catch Transaction in Trigger
I am trying to put a try-catch statement inside a trigger using Microsoft Server 2005. BEGIN TRANSACTION BEGIN TRY --Some More SQL COMMIT TRANSACTION END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRANSACTION; END; END CATCH The problem is that I don't want the trigger to fail if something is caught by the try-catch ...
How would a single script truncate a particular table in every database?
I have a Microsoft SQL Server 2008 with many databases and most of them have a Logs table. I would like to be able to schedule a script to run and truncate the Logs table in every one of these databases (dynamically). I imagine I have to get the name of every user database then truncate the table Logs in the databases that contain a Logs...
Indexed view in Sql Server 2005 Error
I tried to add an index on a view in Sql Server 2005 an I got this error: "Cannot create index on view 'AllAssignmentNotes' because the view is not schema bound." I didn't want to put too much information here as it might be overload. Just wondering if anyone could give me some help. I went to the url the error gave me and got me nowhe...
Service not fully stopped after ServiceController.Stop()
ServiceController serviceController = new ServiceController(someService); serviceController.Stop(); serviceController.WaitForStopped(); DoSomething(); SomeService works on a sqlserver file. DoSomething() wants to copy that SQL file. If SomeService isn't closed fully it will throw an error because the database file is still locked. In t...
Linq insert on primary key field that is autosync
I more or less want to do what this question suggests. http://stackoverflow.com/questions/507515/how-do-i-manually-set-an-identity-field-in-linq-to-sql-identity-insert However, I want to explain. I have a client db. I load Linq objects from here and the send them across WCF. On the other side, I attach them to a data context and post th...
Results of UNION Selects in SQL Server
Is it possible to UNION queries from tables or views that don't have any result in common? What I'm trying to do is combine data from different views into one result. I have select a,b,null,c from VIEW1, VIEW2 where VIEW1.a = VIEW2.a UNION select null,null,z,null from VIEW3 I would like the result to be a,b,z,c. Is this where I wou...