Can it make any difference to query optimisation to have WHERE clauses in a different order for SQL Server? For example, would the query plan for this: select * from table where col1 = @var1 and col2 = @var2 be different from this?: select * from table where col2 = @var2 and col1 = @var1 Of course this is a contrived example, and ...
Hi, I have a SSRS (SQL Server Reporting Services) RDLC local mode report that is populated by an object data source. However the data from the database is returned as 0 but the report is displaying blank. My datasource is a List<> of a type "ReportData" that is populated from a SQL Server query. public class ReportData { public o...
Is there an easy way to do a find and replace on a string in every view and stored procedure in my SQL Server database. I need to replace something like 'X United Kingdom' with 'X(UK)'. ...
I have a table with the following fields Id Name IsPublic i need to write a sql query that updates IsPublic to false where name has a duplicate. Only one of the duplicates should have IsPublic = true. IsPublic is true by default ...
I always ignore this option when creating a new database on SQL Server 2005, simply because we can ignore something that we do not understand and leave it as it is. (I'm not so into DBA) so now I am curious what it is about. From your experience, when do you think we need to add secondary data files to my database and why do we need it...
Hello, Yesterday I asked the question on how I should save my files. After some research I've desided to go with storing the files "in" the database. I've checked the difference between storing the files using filestream and storing the files in the database itself. Each has it's advantages and disadvantages. To help me with my resear...
When creating this query in the SQL querybuilder window in visual studio (2008): UPDATE outgoing_messages SET readstatus = 5 FROM outgoing_messages INNER JOIN connections ON outgoing_messages.connectionid = connections.connectionid WHERE (outgoing_messages.msgreference = '1...
This feels like it should have a basic solution but I don't seem to be getting it. Take this query: SELECT Category FROM Article GROUP BY Category I want to effectively do this: SELECT Category, DatePublished FROM Article GROUP BY Category ORDER BY DatePublished DESC I d...
Have a win 2003 box with MSSQL 2005 running on it. There is a database which is populated every morning with new/modified SalesOrder made the previous day. The database has several tables: SalesOrder, SalesOrderItem, SalesOrderItemBom. Each with a corresponding Version table (i.e. SalesOrderVersion, SalesOrderItemVersion, SalesOrderItemB...
What I'd like to do is keep some configuration in an external XML file and my stored procedure to open this and use the settings defined in it. Is this possible? I dont want to store the XML in a table. ...
Simplistically I have a table that looks like this: ColA | ColB | -----+------+ EE34 | Woo | ER56 | fro | EE34 | eco | QW34 | Sddg | EE34 | zoo | ER56 | safe | I need a select statement for SQL Server that produces: ColA | Count | -----+-------+ EE34 | 3 | ER56 | 2 | QW34 | 1 | This could be running over a table wit...
I'm working on a project in Visual Studio, and I want to create a local database file (.mdf) within the project directory so that it can be checked into SubVersion and have configuration management. Unfortunately, trying to create/attach a database on a network share leads to an error. All of the resources I have found to enable NAS fun...
I searched SO for a pertinent question existing already but couldn't find one. I am setting up a Read-only database role for a few databases in our server farm. Here is a sample of the permissions for one table: GRANT SELECT ON [dbo].[Table] TO [ReadOnly] GRANT VIEW DEFINITION ON [dbo].[Table] TO [ReadOnly] DENY ALTER ON [dbo].[Table] ...
We're required to add Ordering to our EventVenue table, which we've done by adding an int field to store the EventVenue's placing in the order. The ordering is based upon the parent Event's ID and the Event's VenueGrouping. If the VenueGrouping is false the EventVenues will be ordered sequentially. However, if true, the EventVenues for ...
I've got to write a test that requires large amounts of data to be stored in a text column. When I try this (insert 2 billion X characters): INSERT INTO table VALUES ( REPLICATE('X', 2000000000) ) This is what I get: SELECT *, DATALENGTH(textCol) FROM table XXXXXXXXXXXXX.... 8000 I was hoping for more than 8000. Any ideas w...
I have a trigger where I want to send out an email on updates to a row in a table SalesClosing. Now the user (dbuser) who execute the trigger has very limited permissions. So I want the trigger to execute as dbmailuser. A user who has rights to send out email. I tested that dbmailuser can execute sp_send_dbmail when logged in as that use...
I've hit an interesting snag (interesting to me at least). Below is a general idea of what my query looks like. Assume @AuthorType is an input to the stored procedure and that there are various specialized conditions each place I've put comments. SELECT * FROM TBooks WHERE (--...SOME CONDITIONS) OR (@AuthorType = 1 AND --...DIFFERENT ...
Can anybody suggest programming examples that illustrate recursive functions? For example fibonacci series or factorial.. ...
i'm in ubuntu 9.04 and using sql squirrel as my sql client. i connect to a remote SQL Server. There are some stored procedures in the db. I don't know how to execute them. No explicit gui. Earlier i was in windows and i could use management studio. I can right click on stored procedures and give Execute. You guys have any idea? Let me kn...
cat_id prod_name parent_cat_id ------ ---------- ------------ 1 prod_1 2 2 prod_2 5 3 prod_3 1 4 prod_4 3 5 prod_5 7 6 prod_6 5 In a recursive function, make a table and by using these, if cat_id = 1 and parent_cat_id = 1 take that product name and if that product category id and parent category id is sam...