I'm importing from an Excel spreadsheet into an SQL Table using DTS.
Two things:
1. I don't want anything imported into Column 1 of the table as this has been designed to automatically increment (creating a primary key). I haven't worked out how to do this.
2. How can I get the DTS package to ignore the top 2 rows of the Excel spreadsheet (header information)?
An answer to one will be great but both will be tremendous!!
All,
So I have this query that is ignoring my date filter and for the life of me I can't figure out why. Was hoping some guru could explain it to me. Here goes:
This query ignores my date filter:
SELECT
rcv.Name AS MachineName,
r.JobId,
j.Name AS JobName,
r.CreateTime AS JCreateTime,
rsv.Name AS JResultStatus,
rpv.Path + rpv.Name AS ResourcePool,
rcvv.ResourceConfigurationVal AS Dimension
FROM
dbo.Result_View AS r
INNER JOIN dbo.ResourceConfiguration_View AS rcv ON r.ResourceConfigurationId = rcv.Id
INNER JOIN dbo.Job_View AS j ON r.JobId = j.Id
INNER JOIN dbo.ResultStatus_View AS rsv ON r.ResultStatusId = rsv.Id
INNER JOIN dbo.Resource_View AS rv ON rcv.ResourceId = rv.Id
INNER JOIN dbo.ResourcePool_View AS rpv ON rv.ResourcePoolId = rpv.Id
RIGHT JOIN dbo.ResourceConfigurationValue_View AS rcvv ON rv.LatestResourceConfigurationId = rcvv.ResourceConfigurationId
LEFT JOIN dbo.Dimension_View AS d ON rcvv.DimensionId = d.Id
WHERE
(r.CreateTime > DATEADD(DAY, -15, GETDATE()))
AND
(rcv.Name LIKE 'PNP%')
AND
(d.Id = 859)
OR
(d.Id = 860)
If I comment out the last two joins and associated select/filters, all of the sudden the date filter works again. From everything I have read, the joins are supposed to be processed BEFORE the filters are applied in the virtual table. My DB goes back a number of years and contains millions of records. W/O the date filter, the query takes a very, very long time to run.
Any ideas on why this would happen?
Thanks,
Allen
Hi,
View 1 Replies View RelatedI have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes
I am using the SqlDataSource to access the dB from my page. Basically this is what I do with it ds.SelectParameters.Clear();
ds.SelectParameters("Id", TypeCode.Int32, id.ToString());
DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.MaximumRows = 1;
dssa.AddSupportedCapabilities(DataSourceCapabilities.Page);
DataView dv = (DataView)ds.Select(dssa);
if (dv.Count > 0)
{
// collect the information
string title = (string)dv[index].Row.ItemArray[0];
}
And the SelectCommand attribute of the SqlDataSource is set in design mode to "SELECT * from vw_Items ORDER BY Category".
So, since I am trying to retrieve just the item with the given Id I was expecting just one record but when I step through I see that the data view has a count of 9 (all records in the table) !!!
What am I doing wrong here? why can't it return just one? as per the select statement which after adding the parameter should be something like "SELECT * FROM vw_Items WHERE ID = 5 ORDER BY Category
If you have a table called Person and has ID, Age, Gender and Name. And you have a stored procedure:
CREATE PROCEDURE [dbo].[GetName]@Age TINYINT,@Gender TINYINTASBEGIN SELECT Name FROM Person WHERE Age = @Age AND Gender = @Gender END
You want some times to send @Age as null to get all ages (or in other words, you are not concerned with the age) or @Gender as null to get all genders.
A way to do that is to build your query string dynamically and execute it with EXEC command. However, this is a bad solution because you are losing most of the SP benefits and writting longer and more complicated procedures, specially that you have a lot of parameters!
Another solution that I thought of is having your select like this: SELECT Name FROM Person WHERE Age = ISNULL(@Age,Age) AND Gender = ISNULL(@Gender, Gender)
However, I noticed that when the value of the column is NULL, you will get false results, obviously because you are using = operator in comparing a value with null while you should be using IS operator, so this method did not work.
I am wondering if any one has a good solution for this.
Adam Tibi
In our schools we have a number of East-European, Turkish, Scandinavian, ... students. Their names contain "special" characters, like Ö, Ü, Ø, ... Our users want to be able to search for student names without having to enter those special characters. Most often they don't know the exact spelling of the names and they get "no match found" messages as a result.
They want to have persons with the name Ösgür, Osgueld, ... in the result set after entering "osgu" in the search field.
What is the best way to do this? I was thinking about using another collation near the LIKE, but I don't know if that would work and how it should be done. The Database collation is Latin1_General_CI_AS.
Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.
Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part
Here is the beginning of the Transactions table create script
CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,
The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).
Here is an example of what I get.
130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).
068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072
Hi,
This may be a silly question out of ignorance, but I'm working in an environment where I am a DBA for a server, but I'm not a local admin on the windows server for the first time, so sorry if it is
Anyway, I have set up some traces that are writing to a file stored in a directory I have full control on. However, the trace files themselves are not inheriting the folder permissions, even though the folder is configured to do so. The only logins with access are the Local System account, the SQL Server service account local group, and the local administrators group.
Is this something that SQL Server does on its own, or would this have to be something the system admins have set up with group policy or something? If its SQL Server, is there some configuration setting I can change to get it to stop doing that so I can stop bugging the admins to give me access every time I run a trace?
Thanks,
Larry
SQL Version: 2008 (not r2)
Problem: My Select statement seems to be unaffected by some of the conditions in the WHERE Clause. For instance
JCCD.Mth >= cutoffs.FiscalYear_FirstMonth (value '20130101') AND JCCD.Mth <= @WIPMonthCurrent (value '20130101')AND LTRIM(RTRIM(JCCD.Job)) = '71-' (see output and code below)
SQL Code:
declare @WIPMonthCurrent date = '20130101'
SELECT
JCCD.JCCo, JCCD.Job, JCCD.Mth, JCCD.Source, sum(JCCD.ActualCost) AS CostToDate
[code]....
Hi,
I am looking to delete a single row from a relatively small table. Unfortunately, there is a foreign key relationship between this table and a much much larger table. The checking of this foreign key when I am deleting this row seems to significantly impact the performance of the operation. Previously there was an index on this larger table that helped this query run. This index has been dropped to improve the performance of a more frequently executed operation.
Is there a way I can use a hint or something to stop SQL checking this foreign key when deleting the row? I am certain that there are no associated rows in the larger table.
I have read elsewhere that I could disable the foreign key, perform the delete, then enable the foreign key. This delete statement is not a one off process and could happen in the normal operation of the application so I don't really know what the implications of doing this are.
Any suggestions would be appreciated.
Thanks
Rich
I would like to populate a grid with data from 2 different tables.
Table1: [PK]id(int), name(nvarchar), areaID(int)
Table2: [PK][FK]areaID(int), areaDescription(nvarchar)
My cerrent query is:
SELECT Table1.id, Table1.name, Table2.areaDescription FROM Table1 INNER JOIN Table2 ON Table1.areaID = Table2.areaID
However, sometimes the areaID in Table1 will only be populated at a later stage and therefore will be NULL in Table1. Table2 is used as a lookup table when inserting into Table1. This query therefore ommits any records in Table1 which do not have an areaID. I would like to view ALL records(ones without an areaID as well) as they would be populated in the grid and selected to be updated on web forms because they are incomplete and then subsequently assigned an areaID.
Any help with this query would be much appreciated...
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
[code]....
The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.
Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps?
Eg:
1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00
2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00
Total Duration should be 01:15:00 and not 01:45:00
I have this data returning from a query:
Procedure Intermediate New Old Total avg
Proc1 6 0 0 6 2.000000
Proc2 74 13 0 87 29.000000
Proc3 29 0 0 29 9.666666
Proc4 16 0 0 16 5.333333
And I want to dynamically calculate the average rather than divide the total by the number of columns every time, I want to divide only by the number of non zero values per row for intermediate, new and old. So row 1 average would just be itself = 6, row 2 would be 74+13+87/3 etc.
;with cte as
(
select
f.Procedure,
SUM(case when f.old_new = 'O' then f.Value else 0 end) "Intermediate",
SUM(case when f.old_new = 'Y' then f.Value else 0 end) "New",
SUM(case when f.old_new = 'N' then f.Value else 0 end) "Old"
[code]....
This works fine in SQL 2000, but not in SQL 2005
View 12 Replies View RelatedHi,
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @Startdate is fine because the timestamps is at 12:00:00AM but my @EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
Thanks,
Hi,
Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows
All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.
All NOT NULL values of Col1 should be processed by the Look up component.
If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.
Regards,
Gopi
Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?
Hello,
I have a calculated member that composed of dividing two other calculated members. The calculation is incorrect and is ignoring the decimal places in both of the source measures of the equation.
Example:
Calculated Member 1 = 300.01
Calculated Member 2 = 10.25
Calculated Member 3 = (CM1 / CM2 ) = 30 INCORRECT!!!
Answer should be = 29.269
The MDX that I am using is as follows:
Calculated Member 1 (Booked Amount YTD):
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Booked Amount])
Calculated Member 2 (Units YTD)
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Units])
Calculated Member 3 (Booked Amount / Unit)
Measures.[Booked Amount YTD] / Measures.[Units YTD]
This seems like a fairly common and straight forward type of calculation, taking the YTD amount / YTD units to come up with YTD amount per unit. When I create a calculated member with the hardcoded values of 30.01 / 10.25 I get the correct answer of 29.269. Am I missing something with formatting? Is this a bug in Analysis Services?
Any help would be appreciated.
Thanks in advance.
Brian
I am setting the 'To' field in the Send Email task using an expression in the expression textbox because the email address is received from a variable.But the taks throws error, as the 'To' field in the task is empty and only the expresssion field is filled. Is there any workaround ?
View 10 Replies View Related
hi im using the import export wizard to import a file where my recoreds are comma delimited but when i HAVE to use a comma im using quotes to escape
"CN=Administrator,CN=Users,DC=aboneng,DC=local",user,Administrator,Built-in account for administering the computer/domain,"CN=Administrator,CN=Users,DC=aboneng,DC=local",4,20050421014154.0Z,20060518065932.0Z,
with this example
CN=Administrator,CN=Users,DC=aboneng,DC=local === should be the first column
user, ==== the second column
Administrator === third and so forth.
i thought it was a standard to use quotes when escaping your delimiter but sql is bringing in the records as comma delimited as normal is there anyway around this?
Hi,
I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings:
CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS
(
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
This works very well, other than instances of the delimter are, themselves, considered to be results. For example:
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result
returns:
pn s
1 foo
2 bar
which is exactly the result I would want.
However,
SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar'
returns
pn s
1
2 foo
3 bar
4
And
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar'
returns
pn s
1 foo
2
3
4 bar
I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course.
In other words, all three examples above should produce the same result:
pn s
1 foo
2 bar
How can I do this? Any thoughts much appreciated...
To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO
But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)
I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.
I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.
Executed as user: xxxxxx-sql. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:14:05 p.m. Error: 2008-05-12 16:14:07.84 Code: 0xC00220DE Source: EPT Dimensions - x Description: Error 0x80070003 while loading package file "E:ETLLoadGroup_Dimensions_Daily.dtsx". The system cannot find the path specified. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:14:05 p.m. Finished: 4:14:07 p.m. Elapsed: 2.25 seconds. The package execution failed. The step failed.
Hi Im using project real framework and have BIDS installed on same server as SQL Server 2005, when I run from BIDs all
good when I run from SQL Server agent via File system --the first package that master package calls fails
I have double checked the environment variables in Project Real and all good because otherwise BIDs would have failed
and of course in each package the expression data uses @[User::RootDir] to point to the package file path
@[User::RootDir] + \LoadGroup_Dimensions_Daily.dtsx
the issue is the package under SQL Agent is ignoring @[User::RootDir] + \ and finding an old package path somewhere in Bids SLN metadata
Any clues how can refresh the metadata of the package for deployment
thanks in advance
Dave
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedHi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?