What is everyone's recommendations on where to place database objects (tables, procs, views etc.) for a custom add-on to another application's database? Should they be kept separated like so: ErpInventoryAccountingWare.dbo CustomIntegratedPortal.dbo Or, could the add-ons objects be placed in the same database. The objects would be ...
I would like to find out how many tables are returned by each of a large number of undocumented stored procedures. Is there any way that I can get this information programatically? Are there any statistics tools that might do this for me? Edit - to be clear, I'm looking for the number of result sets not the number of referenced tables....
I know you can get metadata and schema info from relational stores in Microsoft SQL Server 2005+ via: INFORMATION_SCHEMA Object Catalog Views What is the equivalent of this in Microsoft Analysis Services? ...
I'm helping troubleshoot some deadlocking in a .NET application that uses SQL Server 2005. I have the XML data from the trace below. What really puzzles me is the RangeX-X lock on PK_Exp_Experience_PriorFirm when the transaction isolation level is read committed. Everything I've read indicates that you only get a key-range lock of you ...
Hey, Lets say we have a query which generates list of city names as output. 1.India 2.America Now if we want to make this data look like a CSV i.e. India,America then how we will generate it in MS SQL 2000 In 2005I have done this using XM Path ...
This is a bit of an open question but I would really like to hear people opinions. I rarely make use of explicitly declared temporary tables (either table variables or regular #tmp tables) as I believe not doing so leads to more concise, readable and debuggable T-SQL. I also think that SQL can do a better job than I of making use of tem...
In SQL Server 2008 and given TableA(A_ID, A_Data), TableB(B_ID, B_Data), and ViewC(A_or_B_ID, A_or_B_Data), is it possible to define TableZ(A_or_B_ID, Z_Data) such that Z's A_or_B_ID column is constrained to the values found in ViewC? Can this be done with a foreign key against the view? ...
Hi I have a table which has a column which is of type xml. I have to extract data from this table and load the data into another environment. i am using bcp to extract and laod the target table but there are some special characters that is causing some issues when i bcp them into the target table. are there any workarounds thanks Ben ...
Hi, I have a simple table with 6 columns. Most of the time any insert statements to it works just fine, but once in a while I'm getting a DB Timeout exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. Timeout is set to 10 seconds...
I have two SQL Server 2005 databases, one is for development and the other is on the final production server. I would like to know the fastest way of ensuring that the production database has the exact same stored procedures (number and most recent version). Assumptions: Databases have same table schema. Production database is curren...
I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting optio...
We have a warehouse database that contains a year of data up to now. I want to create report database that represents the last 3 months of data for reporting purposes. I want to be able to keep the two databases in sync. Right now, every 10 minutes I execute a package that will grab the most recent rows from the warehouse and adds the...
I'm trying to access a large Oracle database through SQL Server using OPENROWSET in client-side Javascript, and not having much luck. Here are the particulars: A SQL Server view that accesses the Oracle database using OPENROWSET works perfectly, so I know I have valid connection string parameters. However, the new requirement is for ...
I have a SQL query where I am going to be transferring a fair amount of response data down the wire, but I want to get the total rowcount as quickly as possible to facilitate binding in the UI. Basically I need to get a snapshot of all of the rows that meet a certain criteria, and then be able to page through all of the resulting rows. ...
I am looking for either a NAnt Task for SQL Server bcp, or the file format for bcp native output. I supposed I could build a NAntContrib Task for bcp but I don't have time at the moment (Do we ever?). Has anybody strolled this path before? Advice? Thanks - Jon ...
I'm starting to look into SSRS Report Builder 2.0, and I see in the documentation that it supports connections to SQL 2005. Does anyone know if the reports made against SQL 2005 can then be upload to the SSRS 2005 website and ran from the site? Or, can they only be run from the SSRS 2008 website if they were generated in Report Builder...
Is there a limit to the rows that IEnumerable.Count() (or IQueryable.Count()) using LINQ to SQL? For whatever reason, if my query returns more than 200 records, I only get 200 from IEnumerable.Count(). I've even tried using IEnumerable.LongCount() (even though the number of results shouldn't be high enough to need it). I've also verif...
I have a stored procedure which does bulk insert on a SQL server 2005 database. When I call this stored procedure from some SQL (passing in the name of a local format file and data file) it works fine. Every time. However, when this same stored procedure gets called from C# .NET 3.5 code using SqlCommand.ExecuteNonQuery it works intermi...
I'm trying to create a bunch of entries in a database with a single script and the problem I'm encountering is how to reference the generated primary key of the previous entry I created. For example if I created a customer, then tried to create an order for that customer, how do I get the primary key generated for the customer? I'm usi...
Hi If I have scripts in multiple files, and I would like to execute each one in a known sequence, can I simply import them into a T-SQL script and execute them, or must I run sqlcmd or similar against each file? I'm sure Oracle has a feature to import/include script content from another file (maybe with @@ ?). I want all of the scripts...