I have the following T-SQL to display all the permissions granted to principals on my SQL server 2005: select dp.NAME AS principal_name, --1 dp.type_desc AS principal_type_desc, --2 o.NAME AS object_name, --3 p.permission_name, --4 p.state_desc AS permission_state_desc --5 from sys.database_permissions p left OUTER JOIN ...
Take the tsql query below: DECLARE @table TABLE(data VARCHAR(20)) INSERT INTO @table VALUES ('not duplicate row') INSERT INTO @table VALUES ('duplicate row') INSERT INTO @table VALUES ('duplicate row') INSERT INTO @table VALUES ('second duplicate row') INSERT INTO @table VALUES ('second duplicate row') SELECT data INT...
I have a table like this: rowInt Value 2 23 3 45 17 10 9 0 .... The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt: SELECT * FROM myTable ORDER BY rowInt; This will list values by rowInt. How can get get the difference of Valu...
What's the best method to bind a linq2sql collection to a GridView, given that I want ot use builtin pagination and sorting? I've tried: public static void BindEnquiryList(EnquiryQuery query, GridView view) { DataContext db = DataContextManager.Context //view.DataSource = (from e in EnquiryMethods.BuildQ...
Hi, I tried doing this but it failed. SELECT table2.ID, table1.* FROM table2 LEFT JOIN table1 ON table1.ID = table2.table1ID How do you select all columns from a table? EDIT: There is no error in the above query. I don't know what caused the error but the code is now working. ...
I have a table that looks something like this CREATE TABLE MyTable( [RecordID] [bigint] IDENTITY(1,1) NOT NULL, [PortName] [nvarchar](50) NULL, [ReceivedEvent] [datetime] NULL, [SentEvent] [datetime] NULL, ); The data could then be RecordID | PortName | ReceivedEvent | SentEvent 1 | Port1 | 2009-...
Currently I have a large import process that I'm trying to wrap inside a transaction so if anything breaks - i could rollback. The issue I have is that when the TSQL inside the trans blows up, it won't rollback when the following SQL error occurs Msg 8152, Level 16, State 14, Line 249 String or binary data would be truncated. The state...
Is there any function to encode HTML strings in T-SQL? I have a legacy database which contains dodgey characters such as '<', '>' etc. I can write a function to replace the characters but is there a better way? I have an ASP.Net application and when it returns a string it contains characters which cause an error. The ASP.Net application...
Hi. I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction. This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was commited and the flight booked. I'm new to T-SQL, and not sure on how to u...
My work company has a MSSQL server 2005. I have two questions about finding out current log user and any way to send out a warning message: First question is if there is any T-SQL or SP available to find out current login user name and machine name. If the user is using SQL server sa name to remotely access to SQL server, is there any w...
Hi there, I have a T-SQL that works below: SELECT WP_VTDID AS UTIL_VTDID, (SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED > 0) AND WP_VTDID='L083') AS UTIL_RUN, (SELECT COUNT(WP_ENGINE) FROM WAYPOINTS WHERE (WP_ENGINE = 1) AND (WP_SPEED = 0) AND WP_VTDID='L083') AS UTIL_IDLE, (SELECT COUNT(WP_ENGINE) FROM ...
I have a SP that calls another SP and the resultset needs to be filtered to only the columns that I am interested in. DECLARE @myTable TABLE ( Field1 INT, Field2 INT, Field3 INT ) --If someSP returns say 30 params and I need only 3 params, I don't want to declare all 30 in my temp table @myTable INSERT INTO @myTable (Field1, Fie...
I have an application I am working on that will need the use of transactions. I am familiar with how they work, but need some suggestions on a current implementation. Here is the scenario... A user is working on a project in our system. In order to publish the project on our system, they must first pay some fees so the project can be pu...
I am not sure I am doing any of this correctly. Is it because I am opening two connections? I am closing them regardless of errors. I did try putting in some inner transaction scopes and setting the second one to RequiresNew. The two methods are independent of each other, however, if one fails I need them both to rollback. I may have to ...
Is there any system view or tables available to get history logins to MS SQL server 2005? If it were available, I would like to use TSQL to get information about login information for a datetime range. I have a scheduled job failed one day because of a thread deadlock exception on a table accessed by a stored procedure. By checking log...
I was given a database design which stores information about an organization and any changes that have happened or will happen to the organizations. Since pretty much anything can change about an organization, there is one table that only contains the unique OrganizationID's in a table called "Organizations". The changes to that organiza...
Pretty simple question - I have an attribute that I would like to have double quotes in. How do I escape them? I've tried \" "" \\" And I've made the @xml variable both xml type and varchar(max) for all of them. declare @xml xml --(or varchar(max) tried both) set @xml = '<transaction><item value="hi "mom" lol" ItemId="106"...
Our system runs on SQL Server 2000, and we are in the process of preparing for an upgrade to SQL Server 2008. We have a lot of trigger code where we need to detect a change in a given column and then operate on that column if it has changed. Obviously SQL Server provides the UPDATE() and COLUMNS_UPDATED() functions, but these functions...
Let's say you have the following two tables: X Table X_ID Y_ID_F X_Value 1 1 Q 2 1 G 3 1 T 4 2 W 5 2 K ... Y Table Y_ID Y_Value 1 A 2 B ... You want to query only those properties whose Y parent's value is A and update them so you write a query as follows (I realize there is a bette...
My team is looking into geospatial features offered by different database platforms. Are all of the implementations database specific, or is there a ANSI SQL standard, or similar type of standard, which is being offered, or will be offered in the future? I ask, because I would like the implemented code to be as database agnostic as pos...