|
Hello All,
I have a SSIS package wherein the first step is excution of C# program to convert a csv file to excel file. The second step is the loading of this excel file into a table.
This programs with no errors when execute from IDE.
Now, i am setting the process as SQL server agent Job, and i give the path to the package for execution. The job fails with message that "Access is denied".
I moved the package to C:\ drive. Now the program fails on first step with null reference.
why the difference in results when executed from SSIS package environment versus the SQL server agent job?
I did resolve this issue by removing the C# program execution and set it up as scheduled task, but would like to understand the behaviour.
Many thanks
|
|
|
|
|
Sorry I can't help with the problem, but I am intriqued as to why you would convert a CSV to "excel" just to load it to a table... the step is totally unnecessary unless you need the xlsx file for something else as well.
|
|
|
|
|
There is one field in the csv file, a freeform field. The users have ability to type anything in this field, along with special characters. Since there was no common delimiter, had to go with converting the csv file to excel file route to load this file using SSIS.
|
|
|
|
|
So it would be surrounded by quotes in the csv.. I stand by my comment
|
|
|
|
|
You should specify UNC paths when you're loading files in your packages. That way, it will work no matter where you put the file (as long as you're using the correct UNC path of course).
BTW, you can write a script task in a package that will import that data (as opposed to running an external application).
I wrote an article not to long ago that includes code to import excel and CSV files - SQLXAgent - Jobs for SQL Express - Part 3 of 6[^]
I think you might find it useful. I'm currently extracting that code into it's own assembly for easier inclusion into projects like yours. It even goes to some length to determine the data type represented in each column.
".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 taken a backup of Database A from Server A1 then I restored this Database with name B on Server B1, now the application is working fine with Database A on Server A1 but not working with Database B on Server B1, I have checked the users, I have the users and I have given access to the users when it didn't work I have dropped the User which is connecting from Web application and recreated that user and granted the same permissions it had previously on this Database but still I am not able to use the application.
Here is the error that I am getting:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Any help would be greatly helpful thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
It's nothing to do with the database - your code can't connect to SQL Server.
- Check the name and instance name in your connection string.
- Check that SQL Server on server B1 is configured to allow remote connections.
- If you're using the Express version, check that the SQL Browser service is running on server B1.
- Using the SQL Server Configuration Manager on server B1, check under "SQL Server Network Configuration" that there is at least one enabled protocol;
- Using the SQL Server Configuration Manager on the client, check under "SQL Native Client Configuration" (both 64- and 32-bit) that at least one of the server protocols from B1 is enabled;
- Check any firewalls between the client and the server;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yeah that's true I messed up connection string, thanks my friend.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello All,
We have job set up on a newly built server, windows 2012, with sql server 2012.
We have a job set up to run everyday. The first day the job ran look 7 hours. Slowly, over one months time it has creeped up to 8 hours now.
We are puzzled as to why this steady increase in time. WE have confirmed with our network folks no other job/process is running on the viritual server at the same time.
Looked at the event viewer, nothing to report. Is there any other place i could view the logs to get a better insight?
thanks!
|
|
|
|
|
VK19 wrote: Looked at the event viewer, nothing to report. Is there any other place i could view the logs to get a better insight?
Perhaps, in SQL Server Logs?
|
|
|
|
|
Would the logs files be in the following location:
\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
|
|
|
|
|
It seems to be correct.
My Logs are in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
Alternatively you can see the log in SSMS under Object Explorer -> Management -> SQL Server Logs
|
|
|
|
|
What is the nature of this job? Does it create records ? Or read the data? How big is this database ? How are many disks are installed on the server? How are they being used by the database engine? Are the Operating System and database files on different disks?
On the database server from a command prompt type "perfmon" to bring up the Performance Monitoring Utility. You want to add a counter for Logical disk / Avg Disk Queue Length (All disks) Look at the graph while the job is running, if you see high activity on one disk and low activity on another disk, you may want to consider moving some DB files around.
Here are a couple of things to consider:
1) Count the number of rows in the tables involved. Check these values daily, weekly, monthly.
2) Identify the tables that are growing.
3) Are there indexes on these tables? Check the fragmentation of the indexes. Possible create new indexes or revise existing ones.
NOTE: Be careful when adding indexes, it may help your one job, but have a negative impact on the rest of the system.
With the limited information given, I'm giving you some basic areas to investigate. I'll be glad to help you.
|
|
|
|
|
Hi ,
I have A nvarchar value Like This =id1-1,2 ; id2-1,3 ; .......i want return the nvarchar value with replace of id1,id2 values(i have a table id,value)
|
|
|
|
|
REPLACE (Transact-SQL)[^]
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
Hi ,
I have A nvarchar value Like This =id1-1,2 ; id2-1,3 ; .......i want return the nvarchar value with replace of id1,id2 values(i have a table id,value)
|
|
|
|
|
Do you ask about using CAST or/and CONVERT?
Or what did you mean?
|
|
|
|
|
I ned to convert below DB2 query to SQL
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',DEL.COVERAGE_CODE))) as VARCHAR(1024)), 3) as COVERAGE_CODE
I am not able to get equivalents of xml related things in SQL. Any help would be appreciated
|
|
|
|
|
|
For that specific query, there are various equivalent methods in SQL:
Concatenating Row Values in Transact-SQL - Simple Talk[^]
STUFF
(
(
SELECT ', ' + DEL2.COVERAGE_CODE
FROM YourTable As DEL2
WHERE DEL2.GroupingField = DEL.GroupingField
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'),
1, 2, ''
)
Or, if you're using SQL 2017, you can use the new STRING_AGG function:
STRING_AGG (Transact-SQL) | Microsoft Docs[^]
STRING_AGG(DEL.COVERAGE_CODE, ', ')
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Just a random question since I already have a different solution but I had the following situation.
I have a table with a billion rows (actually probably about 1.1 billion.)
I was trying to span the table, read every row and do an analysis.
Certainly couldn't load the entire table. I was using a paged query (limit/count). Each page took about 90 minutes for the query itself. So not really something that was going to allow me to do much analysis.
Any other ideas on spanning it or speeding it up?
At one point I was even considering just dumping it and writing an app to do the analysis outside of the database. That was about the only other solution I had.
The database was MySQL (AWS Aurora actually).
The relevant parts of the table were as follows and the id has a primary key. (I didn't design the table.)
Table T
id char(22)
RefId1 char(22)
RefId2 char(22)
...
|
|
|
|
|
The obvious question is: What kind of analysis?
|
|
|
|
|
Jörgen Andersson wrote: The obvious question is: What kind of analysis?
Since I needed to span the database not sure how that matters unless you would perhaps suggest random sampling.
But in this case I needed to validate that the ids in each row did in fact match my expectations of what they were pointing to.
|
|
|
|
|
jschell wrote: What kind of analysis?
I think that is critical, the design of your analysis is the driving factor and is going to be the one area you can gain the most benefit from optimization.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
