Can you create a linked server in SQL Server 2008 and then refer to it with an alias. So, I create a linked server to "SalesServer", but I give it the alias "Sales", so I can use it like this: SELECT * FROM Sales.DB1.dbo.DailySales ...
Hi al, we have 5 tables over which we should query with user search input throughout a stored procedure. We do a union all of the similar data inside a view. Because of this the view can not be materialized. We are not able to change these 5 tables drastically (like creating a 6th table that contains the similar data of the 5 tables and...
I am development a app multi tenant. I chose the approach Shared Database/ Separeted Schema. My idea is has a schema default (dbo) and when deploy this schema, do update schema of outhers tenants (tanantA, tenantB, tenantC). In other words, make sincronization schemas. How can sincronized this schemas of tenants with schema default? ...
Hi Team, I am working with SQL Server 2005. I need to find out only those rows for which there is a special character in “Body” column. In the following scenario, the result should be only the row with TemplateID = 2. How do we write the query for this? CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100)) INSERT INTO #Template ...
I'm using SQL Server Management Studio 2005, and I hate the way that the tabbed interface displays the current database and filename. It crams both together in the tab title so that most tabs end up with a name like "databas...ccountQuery.sql". I've tried the MDI interface, but it is not much better. Is there any way to make SSMS disp...
Im a bit new to T-SQL, Coming from a MySQL background Im still adapting to the different nuances in the syntax. Im looking to add a new column AFTER a specific one. I've found out that AFTER is a valid keyword but I don't think it's the right one for the job. ALTER TABLE [dbo].[InvStockStatus] ADD [Abbreviation] [nvarchar](32) DEFAULT ...
Lets say we have 3 tables (actually I have 2 at the moment, but this example might illustrate the thought better): [Person] ID: int, primary key Name: nvarchar(xx) [Group] ID: int, primary key Name: nvarchar(xx) [Role] ID: int, primary key Name: nvarchar(xx) [PersonGroupRole] Person_ID: int, PRIMARY COMPOSITE OR NOT? Group...
I have an application that is now 4+ years old that is exhibiting some odd behavior on our latest deployment. The application uses nHibernate for all inserts / updates / selects, etc. We are currently using .NET 2.0, and nHibernate 1.2 (I know, we need to upgrade) This deployment is on Windows 2008 Server x64, IIS 7.5 - what I have see...
Simple question I couldn't figure out (not a SQL expert... sorry): I want to do a select on the result set of sp_who2. How can I? for ex. select SPID from [result set from sp_who2] ...
I have a table, which contains many columns of float data type with 15 digit precision. Each column consumes 8 bytes of storage. Most of the time the data does not require this amount of precision and could be stored as a real data type. In many cases the value can be 0, in which case I could get away with storing a single byte. My goal...
I am hoping this question fares a little better than the similar Create a table without columns. Yes, I am asking about something that will strike most as pointlessly academic. It is easy to produce a SELECT result with 0 rows (but with columns), e.g. SELECT a = 1 WHERE 1 = 0. Is it possible to produce a SELECT result with 0 columns (...
Note: I'm not trying to call a SQL Server stored proc using a L2SQL datacontext. I use LINQPad for some fairly complex "reporting" that takes L2SQL output saved to an Array and is processed further. For example, it's usually much easier to do multiple levels of grouping with LINQ to Objects instead of trying to optimize a T-SQL quer...
Here is what sp_helpfile says about my current database (recovery model is Simple) in SQL2000: name fileid filename size maxsize growth usage MasterScratchPad_Data 1 C:\SQLDATA\MasterScratchPad_Data.MDF 6041600 KB Unlimited 5120000 KB data only Maste...
I have a folder with multiple XML files. I need to bulk insert each one into a table in sql server. I am at a complete loss as to how to get this to work, as I am new to SSIS. Currently, My SSIS package pulls the files off an FTP server and uses a command line to unzip the xml (the come as .xml.gz). This all works great, but now I'm ...
I'm trying to modify a stored procedure hooked into an ORM tool. I want to add a few more rows based on a loop of some distinct values in a column. Here's the current SP: SELECT GRP = STAT_CD, CODE = REASN_CD FROM dbo.STATUS_TABLE WITH (NOLOCK) Order by STAT_CD, SRT_ORDR For each distinct STAT_CD, I'd also like to insert a REA...
I have 2 tables, that look like: CustomerInfo(CustomterID, CustomerName) CustomerReviews(ReviewID, CustomerID, Review, Score) I want to search reviews for a string and return CustomerInfo.CustomerID and CustomerInfo.CustomerName. However, I only want to show distinct CustomerID and CustomerName along with just one of their CustomerRevi...
This code was once working on sql server 2005. Now isolated in a visual basic 6 sub routine using ADODB to connect to a sql server 2008 database it throws an error saying: "Login failed for user 'admin' " I have since verified the connection string does work if i replace the body of this sub with the alternative code below this sub. ...
The project I'm working on creates a local copy of the SQL Server database for each SVN branch you work on. We're running SQL Server 2008 Express with Advanced Services on our local machine to host it. When we create a new branch, the build script will create a new database with the ID of that branch, creates the schema objects, and cop...
We are getting the following ODBC timeout errors when executing sql code against a SQL Server database. "Connectivity error: [Microsoft][ODBC SQL Server Driver]Timeout expired" "S1T00" The confusing thing here is that our stack dump says that the SQL code it is executing when the timeout occurrs - is very simple: SELECT @@TRANCOUNT AS...
I have 2 tables. One is for categories, the second is for Questions. category table: category_id category_name questions table: question_name question_id category_id How can I loop though all the category names and show the questions grouped under each category name? Using Coldfusion, so I am assume I should use CFLOOP Th...