HI There, i have to make my application SAAS compliant .For achieving multi tenancy , i was thing of partitioning the data , and each partition will be for a tenant. and this partitioning will be done dynamically . has anybody done something like this ? what do you think the better approach be ? i am using SQL 2005 Regards DEE ...
How can I calculate the size of a T-SQL query in bytes transfered across the network? I know I can approximate it by examining data types of columns (varchars are an interesting twist though), but are there tools that'll give me the number of bytes (including and excluding TCP/IP headers) used up while transferring the query and its res...
Story: today one of our customers asked us if all the data he deleted in the program was not recoverable. Aside scheduled backups, we shrink the log file once a day, and we use the DELETE command to remove records inside our tables where needed. Though, just for the sake of it, I opened the .mdf file with an editor (used PSPad), and se...
I'm trying to restore backups of our production databases to our development server. When I run the following script which has previously worked: RESTORE DATABASE M2MDATA01 FROM DISK = 'C:\Install\SQLBackup\M2MDATA01.SQLBackup' WITH REPLACE, MOVE 'M2MDATA01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2...
I want to inner join with a child table based on ID and get the top Row of the child table, I am not joining to take any data out of the child table, its just to validate that child table record exists for parent table. If I dont include TOP row there is chances of getting multiple rows of parent in the result set. -- Chances of multipl...
Question title is the crux of the problem. I have an Access 2007 (2003 format) front-end with a SQL Server 2008 Express back-end. The input form has a subform linked to another table. When adding a record in the main form, the PK field of the table (set to auto increment) is skipping about four IDs (I say about because sometimes it's thr...
Why does this quite simple xquery takes 10min to execute in sql server (the 2mb xml document stored in one column) compared to 14 seconds when using oxygen/file based querying? SELECT model.query('declare default element namespace "http://www.sbml.org/sbml/level2"; for $all_species in //species, $all_reactions in //reaction where data(...
I have a datetime field in my table. I want to delete records based on a date but I am not interested in the time element. How would I write the SQL for this ? I am using MS SQL 2008. ...
Hello, I'm migrating from SQL Server to PostgreSQL. I've seen from http://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query that there is no such thing as temporary variables in native sql queries. Well, I pretty badly need a few... How would I go about mixing in plpgsql? Must I create a function and th...
When creating an index for a specific query is it good practice to index all columns that are in the where clause and then have any columns selected in the "included columns" section? Should I leave bit columns out of the index? ...
Hi all, I have the following query and it's not working exactly as i want it to and it is really slow so i figured i'd ask for some help. CREATE PROCEDURE [dbo].[SummaryReport] @event varchar(7) = null, @pet_num varchar(12) = null AS BEGIN WITH pet_counts AS (SELECT event, pet_num, pageid, ...
In this post "select with nested select" I read that SQL Compact 3.5 (SP1) support nested SELECT clause. But my request not work: t1 - table 1 t2 - table 2 c1, c2 = columns select t1.c1, t1.c2, (select count(t2.c1) from t2 where t2.id = t1.id) as count_t from t1 Does SQL Compact 3.5 SP1 support nested SELECT clause in this c...
We have a third party application that accept calls using an XML RPC mechanism for calling stored procs. We send a ZIP-compressed dataset containing multiple tables with a bunch of update/delete/insert using this mechanism. On the other end, a CLR sproc decompress the data and gets the dataset. Then, the following code gets executed: ...
In our application we're going to be allowing users to type in arithmetic expressions (+ - * /) using other database columns for numbers, which would then be parsed by the application and written into the database as a computed column. However there are problems that arise with allowing users to create expressions that could cause excep...
We just moved our SQL 2000 databases to a new SQL 2008 box. After the move, we bound the IP address of the SQL 2000 box to the new SQL 2008 box. This works, except in a VB6 application running on a Windows 2000 SP4 box where we are getting the error: "Query cannot be updated because the FROM clause is not a single simple table name" ...
When running a SQL statement or a stored procedure on a database, can you connect to an external database and pull data from there? something like: SELECT a.UserID, b.DataIWantToGet FROM mydb.Users as a, externaldb.Data as b ...
I am looking for a SQL Query which will help me differentiate between the various versions of SQL Server: 2000/2005/2008 Development/Standard/EE. ...
Hello Everyone, I am wondering how can I create Cross Tabs queries with SQL Server 2008. I have fields for Job Numbers and Employees and I want to show that how many hours an Employee has worked on the specific job. For sample, this is what I want. link text I tried PIVOT but that didn't work for. Later I want to export this data to EX...
I am trying to retieve data from a MySQL table and insert into a SQL Server table using ADO .NET connections in SQL Server 2008 SSIS. In my data flow task I have an ADO .NET Source which queries the MySQL table (select all invoices) and an ADO .NET Destination which inserts the data in my SQL Server table. Now, I would like to add a para...
Is it possible to change a SQL Server 2005 Product License Key without reinstalling SQL? We have two identical Dell servers that will be deployed by the DOD. Each server was purchased with a SQL Server 2005 Standard Edition license. We installed and configured one of the servers. We took a ghost image of it and put it on the second ser...