|
Would be good to see the actual error message.
Does the data that you are trying to import only have the 1 column? does the import get to a certain row number?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
SSIS has a problem importing data from Excel if the first few rows (5 I think) are in numeric format, but subsequent rows contain data that cannot be parsed as numerical. There is a way of changing the connection from SSIS to get it t not guess the column type - you need to set IMEX=1 in the connection string
Check this article - Importing data from Excel having Mixed Data Types in a column (SSIS) | Tech Updates
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Yes, I posted the same question in QA and it's been there for a day with no responses, so I posted it here too...
I'm using the SQL Server merge statement to update one table from another (if a record in the target matches a record in the source, nothing happens, otherwise an insert is performed.
Theoretically, if I run the same query twice in a row on the same source table, there shouldn't be any inserts performed on the 2nd run of the query, but I'm getting a handful of inserts that are still being performed. I have no idea why.
When I compare the record that already exists with the record I'm trying to insert, the joined column values are identical (which is supposed to indicate a match, and thus no insert), yet the Merge statement still inserts the source record.
Every time I execute the Merge statement, the same handful of records are re-inserted.
I've been playing with this code. Please review the comments in the code block for the weirdness:
DECLARE @nextOrdinal INT = (SELECT CASE WHEN MAX(InsertOrdinal) IS NULL THEN 1
ELSE MAX(InsertOrdinal)+1
END
FROM [Essentris].[dbo].[VancoMycin]);
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
DROP TABLE #tempVanco;
END
CREATE TABLE #tempVanco
(
[ABX_NAME] [nvarchar](255) NULL,
[ROUTE] [nvarchar](255) NULL,
[DELIVERY_TIME] [datetime] NULL,
[HOSPNO] [int] NULL,
[PTNAME] [nvarchar](255) NULL,
[UNIT] [nvarchar](255) NULL,
[ATTENDING] [nvarchar](255) NULL,
[SERVICE] [nvarchar](255) NULL,
[ADX] [nvarchar](255) NULL
);
update [Essentris].[dbo].[IMPORTED_Vanco]
SET [ABX_NAME] = UPPER(RTRIM(LTRIM([ABX_NAME])))
,[ROUTE] = UPPER(RTRIM(LTRIM([ROUTE])))
,[PTNAME] = UPPER(RTRIM(LTRIM([PTNAME])))
,[UNIT] = UPPER(RTRIM(LTRIM([UNIT])))
,[ATTENDING]= UPPER(RTRIM(LTRIM([ATTENDING])))
,[SERVICE] = UPPER(RTRIM(LTRIM([SERVICE])))
,[ADX] = UPPER(RTRIM(LTRIM([ADX])));
;with cte as
(
SELECT [ABX_NAME]
,[ROUTE]
,[DELIVERY_TIME]
,CAST([HOSPNO] AS INT) AS [HOSPNO]
,[PTNAME]
,[UNIT]
,[ATTENDING]
,[SERVICE]
,[ADX]
FROM [Essentris].[dbo].[IMPORTED_Vanco]
GROUP BY [ABX_NAME]
,[ROUTE]
,[DELIVERY_TIME]
,CAST([HOSPNO] AS INT)
,[PTNAME]
,[UNIT]
,[ATTENDING]
,[SERVICE]
,[ADX]
)
insert into #tempvanco
select * from cte;
MERGE INTO [Essentris].[dbo].[VancoMycin] AS t
USING #tempVanco AS s
ON
(
t.[DELIVERY_TIME] = s.[DELIVERY_TIME]
AND t.[HOSPNO] = s.[HOSPNO]
AND t.[PTNAME] like s.[PTNAME]
)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[ABX_NAME]
,[ROUTE]
,[DELIVERY_TIME]
,[HOSPNO]
,[PTNAME]
,[UNIT]
,[ATTENDING]
,[SERVICE]
,[ADX]
,[ABX_NAME_SHORT]
,[DELIVERY_DATE]
,InsertOrdinal
)
VALUES
(
s.[ABX_NAME]
,s.[ROUTE]
,s.[DELIVERY_TIME]
,s.[HOSPNO]
,s.[PTNAME]
,s.[UNIT]
,s.[ATTENDING]
,s.[SERVICE]
,s.[ADX]
,'VANCOMYCIN'
,CONVERT(DATE, s.[DELIVERY_TIME])
,@nextOrdinal
);
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
DROP TABLE #tempVanco;
END
".45 ACP - because shooting twice is just silly" - JSOP, 2010
- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 31-Aug-17 11:06am.
|
|
|
|
|
I found a work around. Instead of directly using the Imported_XXX table directly, or using a temporary table, I creates a view that performed the grouping, and that seems to have fixed my problem.
It has been suggested that there might be a bug in the Merge functionality.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Hi,
I have few SSIS Packages which have Connections which deal with sensitive Data, so far how I am doing is, I have a variable which holds the Connection string of the sensitive Data Connection, I am going to set that variable value at runtime from .Net code which executes the Package, the variable value is stored in the Database in AES Encrypted format, to protect my Connection String so far I am deleting the User Id Password values from the variable, before putting SSIS Package onto the FileSystem to be executed by .Net code (this is so that unwanted people can't see the credentials). But when I need to test, debug or edit the Package I need to put the values statically and execute the Package to see the results, because Dataflow needs static connection with proper connection string (there are Dynamic Dataflows are there which can pickup the connection string at run time, but we are not using those here though).
So far this is what going on to protect the credentials for the sensitive Database. But if I can encrypt the Connection string even at the static level and edit or execute the Package, it would be great, is there anyway to do it? Or is there any better approach to secure my credentials than this approach, please give me your great suggestions. Obviously I believe few more brains are better than one brain in lot of situations.
Thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I have table 1 and table 2. Click link show pic
Table 1 - Table 2
I need help to create table 3 from table 1 and table 2 using SQL query
^_^_^
modified 25-Aug-17 22:15pm.
|
|
|
|
|
No, you won't get many people to click some link. Please just put the relevant information in the question.
|
|
|
|
|
With Numbering
As
(Select *,(case when len(phone) > 0 then 1
else 0
end) as Number
From dbo.table2)
select classid,count(student_name),sum(number)
from Numbering
group by classid;
|
|
|
|
|
Hi,
I have the following expression which either I want to execute in Select statement and get the result into a variable or some tables column anything is fine or I want to convert it into Select statements case anything which is possible please let me know, I need this friends.
MCTR_DESCR == "Large" ? "LG" : MCTR_DESCR == "Midsize" ? "LG" : MCTR_DESCR == "Strategic Account" ? "LG" : MCTR_DESCR == "Strategic National" ? "LG" : MCTR_DESCR == "Large National" ? "LG" : MCTR_DESCR == "Small" ? "SG" : ISNULL([Copy of Column 11]) ? "SG" : [Copy of Column 11] == "" ? "SG" : LEFT([Copy of Column 11],1) == "X" ? "IFP" : [Copy of Column 11] == "W0051412" ? "Calpers" : [Copy of Column 11] == "W0051411" ? "Calpers" : "SG"
Thanks in advance buddies,
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
SELECT CASE WHEN MCTR_DESCR IN ('Large', 'Midsize') THEN 'LG' ELSE '' END ...
etc. Just build it out. It's all pretty straightforward.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Hi All,
Ok so I am hoping I can get some help here please?
My situation is that we have three environments 2 are using sql server and 1 is using sql server clustered.
We have a SSIS package that is used to create xml files which works perfectly in environment 1 and 2. However in environment 3 the using clustered sql the xml file generated is out of sequence. It's lists all odd numbered records first then all the even numbered ones.
Everything between the environment is like for like so I cannot see why this is happening.
Are there any steps or an approach I can use to see why this could be happening?
Thanks
KP
|
|
|
|
|
Member 13357915 wrote: It's lists all odd numbered records first then all the even numbered ones.
Why is that a problem?
Member 13357915 wrote: so I cannot see why this is happening.
Assuming ordering perhaps. If you are not explicitly ordering the records then you are assuming the ordering. There is no assurance of ordering. It might normally be ordering (default ordering) by the primary index and that might change with a cluster.
|
|
|
|
|
Hi ,
ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
1 123 PST 2017-06-24 00:00:00.000
1 123 MST 2017-08-02 00:00:00.000
2 345 CST 2017-07-01 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000
The above table is Name Timetable .I looking for below result if i pass date parameter
as ‘2017-08-01 00:00:00.000’
ID Device id TimeZone Effective Date
1 123 CST 2017-01-07 00:00:00.000
2 345 PST 2017-08-01 00:00:00.000
4 678 CST 2017-08-02 00:00:00.000
|
|
|
|
|
That is the wrong way to hold date and time information in a database. You should always use DateTime types, and store UTC values.
|
|
|
|
|
Assuming you're using Microsoft SQL Server, something like this should work:
WITH cte As
(
SELECT
ID,
[Device id],
TimeZone,
[Effective Date],
ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
[Effective Date] DESC
) As RN
FROM
Timetable
)
SELECT
ID,
[Device id],
TimeZone,
[Effective Date]
FROM
cte
WHERE
RN = 1
;
ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]
NB: You should avoid using spaces or special characters in table and column names. It makes it harder to query the data correctly. Instead, change the display names in your application's UI.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you .it has solved partially but still have one problem
If we have rows which are less than or equal for the supplied parameter(Date) we are pulling highest date row here this is working correct .
if we don't have any rows which are less than or equal for the supplied parameter(Date) and if we have rows which are greater than supplied parameter we need to pull lowest date.can you help in this regard
|
|
|
|
|
Try changing the ROW_NUMBER ordering:
ROW_NUMBER() OVER
(
PARTITION BY
[Device id]
ORDER BY
CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
CASE WHEN [Effective Date] <= @YourDateParameter THEN [Effective Date] ELSE Null END DESC,
CASE WHEN [Effective Date] <= @YourDateParameter THEN Null ELSE [Effective Date] END
) As RN
- Dates on or before the date parameter come first;
- Dates on or before the parameter are sorted in descending order, so the latest comes first;
- Dates after the parameter are sorted in ascending order, so the earliest comes first;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Interesting, why this...
SELECT
COUNT(*)
FROM TBL1
LEFT JOIN TBL2 ON TBL2.ID = TBL1.TBL2_ID
WHERE (TBL1.INTFIELD IS NULL) <> (TBL2.INTFIELD IS NULL)
... Ends in an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.
Where I'm wrong? Is it not allowed to compare two booleans?
At least in (MS)SQL not
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I think boolean expressions require boolean operators: AND, OR etc.
|
|
|
|
|
Yes seems so in SQL. But e.g. in c++ (and I think also in c#) you can easy have something like:
bool b1= true;
bool b2= false;
bool res= b1 == b2;
modified 19-Jan-21 21:04pm.
|
|
|
|
|
(SQL != C++) && (SQL != C#)
|
|
|
|
|
No ...
(SQL != C++) && (SQL != C#)
... does also not work
modified 19-Jan-21 21:04pm.
|
|
|
|
|
And SQL := SQL. Only for Information, MySQL 5.6 does evaluate it like I would expect.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I think you mean SQL != MySQL. Different SQL implementations have different features, that is well known.
|
|
|
|
|