Hi. Given an input of the format (currently a flat file): Name, StartDate, EndDate with sample data like Peter, 2010-09-01, 2010-09-04 I was wondering which SSIS task I could use to split this single row into a row per day: Peter, 2010-09-01 Peter, 2010-09-02 Peter, 2010-09-03 Peter, 2010-09-04 I know I can probably do it with a s...
Hi all, I'm trying to establish a temp table for a running inventory demand report in SQL 2008. I can get the results that I am looking for until I hit an invoice with multiple lines. Here is the code so far: --Gather Current Order Data DECLARE @b TABLE ([Planned Shipment Date] DATE, [Order] VARCHAR(15), [Line Number] VARCHAR(15), [L...
I have a number of read only databases alter database [ReferenceData] set READ_ONLY that is used by our application and was wondering what type of things I can tweak for maximum performance? So far, I came up with the following goals: Force padding in indexes to 100% fill factor index fragementation be less than 10% If space isn't a p...
I have data in following format Table1 e_id e_name e_type ------------------------- 1 CBC 2 2 ABC 3 3 N2 1 4 CBC1 3 5 ABC1 3 6 N1 1 table2 N_ID N_Name --------------- 3 N2 6 N1 Table3 N_ID E_ID ------------ 3 1 3 2 3 3 6 4 6 5 6 6 And...
Hello everyone, I am using SQL Server 2008 Enterprise. I am using the following statement in SQL Server Management Studio as a part of a store procedure, and there is following error (compile error when I press F5 to run the store procedure). But when I removed count(), all error disappears. Any ideas what is wrong with count()? Another...
I've been playing around with hosting on discountasp.net and am in the process of hosting my second web app. Being that discountasp charges you per database and not per sql server or total space used by all of your databases, both apps need to share one database I need to create all of the tables from the database used by the second ap...
I have a MERGE query to create an upsert operation on my database with data entered through my application. When I go to call it to save my data on large transactions (>5000) it takes a very long time (~20-40 seconds). Here's my MERGE statement MERGE TestTable AS target USING (SELECT @Guid) AS source (target.Guid = source.Guid) WHEN M...
UPDATE : This is what I did - set @dyn_sql = ' select @UserName=UserName from ( select E.ID as EmployeeID, E.UserName as Username ...
One of our system finally launch in production and during the peak hour (about 200 concurrent user at any one time), traffic can ramp up to 30,000 user transaction within an hour. What we notice is a strange behavior that right after our SQL server restarted, the performance is very fast. Even with 200 concurrent user at the beginning,...
Hi I have created a table that has a strongly type XML column in SQL2008 database. I'm having trouble understanding the documentation or samples that show how I can do a simple query to select the rows that have a value of true for a particular element. The closest I can get without having errors thrown is: SELECT Person.* FROM [Profi...
I have a query that has appropriate indexes and is shown in the query plan with an estimated subtree cost of circa 1.5. The plan shows an Index Seek, followed by Key Lookup - which is fine for a query expected to return 1 row from a set of between 5 and 20 rows (i.e. the Index Seek should find between 5 and 20 rows, and after 5 - 20 Key...
I have two queries that I thought meant the same thing, but I keep getting different results and I was hoping someone could explain how these are different: 1. select * from table1 a left join table2 b on a.Id = b.Id and a.val = 0 where b.Id is null 2. select * from table1 a left join table2 b on a.Id = b.Id where b.Id is null ...
Hi All, Trying to understand why you can't use sql_variant for the value type when using the XML nodes function within SQL Server? I have a scenario where I will be dynamically parsing some XML input and being able to use the sql_variant would be a nice alternative to having to assign a datatype variable or doing a lookup on sys.columns...
We're customizing a CMS that has a few hundred tables in it. We are considering moving our custom tables to a separate database on the same server. Should we expect much of a slow down in SQL Server? There will be many multi-join stored procedures joining data across the CMS database and our custom database. ...
ReporterTbl is one to many relationship >>> AttachmentTbl in ReporterTbl i have Id (101) and i can have AttachmentTbl more than one attachments related with ReporterTbl.Id SELECT ISNULL(ReporterTbl.Id, 0) AS Id, CONVERT(char(10), ReporterTbl.StartDate, 101) AS StartDate, ISNULL(ReporterTbl.PriorityId, 0) AS PriorityId, ISNULL(d...
Hi all, i have an audit table in SQL server 2008 database which keeps track of all changes made in tables. It does a great job, except it can't catch username of the currently logged user, because my connection string uses one username for all connections. This app is written in ASP.NET MVC, this is why i have username stored in connec...
How does SSMS calculate the query cost (relative to the batch) value that is displayed when you select to include the actual execution plan? Mainly, I have a few saved execution plan XML files that I want to compare with each other. It must be calculating that number somehow from the data in the XML files, but I am not seeing how this ...
If you drop a table in SQL Server that has Keys, Indexes, Contraints, etc on it will it drop those as well? I was just wondering when I am building my scripts if I need to create drop scripts for all of those as well or if I can simply drop the tables? Thanks, S ...
In Oracle, you can create a temp table using something like: CREATE GLOBAL TEMPORARY TABLE temp_table ( field1 NUMBER, field2 NUMBER ) ON COMMIT DELETE ROWS; ... which could be pretty nifty, as this creates a table visible to everyone, but the data one INSERTs into the table is visible only to him or her. Additionally, that da...
Hi, I am trying to use the Quartz plugin for Grails to schedule a job in a web app I am working on. Using the RAMstore worked fine, but I need the job to persist in our SQLServer database, which has caused problems... I have changed the jdbcStore variable in QuartzConfig.groovy to true, set def volatility = false in the job file, and...