Database

that did it thanks so much

Yeah that makes sense - thanks a lot Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Thanks Eddy.

Try using CROSS APPLY instead: Making OUTER and CROSS APPLY work for you[^] select * from @entities as e CROSS APPLY ( select top(1) act_finish_time_local, oper_id from job as cleaningJob where cleaningJob.oper_id like '%'+ e.ent_name +'%' and cleaningJob.act_finish_time_local < e.startDT order by act_finish_time_local desc ) as j "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

hi, I don't have permissions to run the open rowset and it needs the format file creation, I tried creating the format file, but some reason its not letting me do it. I tried the same using the SSIS package, problem is its giving me error as below: Error: 0xC0202055 at Data Flow Task 1, Source - ProviderDetails_csv [61]: The column delimiter for column "Column 18" was not found. Error: 0xC0202092 at Data Flow Task 1, Source - ProviderDetails_csv [61]: An error occurred while processing file "C:\xxxxxxx\OPS\ProviderODS-ScriptsAndData\ProviderDetails.csv" on data row 542006. Error: 0xC0047038 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - ProviderDetails_csv returned error code 0xC0202092. The component returned a failure code when the But when I trying to import using if not exists (select * from sysobjects where name='Import_21Columns' and xtype='U') CREATE TABLE Import_21Columns( [Column 0] [varchar](max) NULL, [Column 1] [varchar](max) NULL, [Column 2] [varchar](max) NULL, [Column 3] [varchar](max) NULL, [Column 4] [varchar](max) NULL, [Column 5] [varchar](max) NULL, [Column 6] [varchar](max) NULL, [Column 7] [varchar](max) NULL, [Column 8] [varchar](max) NULL, [Column 9] [varchar](max) NULL, [Column 10] [varchar](max) NULL, [Column 11] [varchar](max) NULL, [Column 12] [varchar](max) NULL, [Column 13] [varchar](max) NULL, [Column 14] [varchar](max) NULL, [Column 15] [varchar](max) NULL, [Column 16] [varchar](max) NULL, [Column 17] [varchar](max) NULL, [Column 18] [varchar](max) NULL, [Column 19] [varchar](max) NULL, [Column 20] [varchar](max) NULL ) if not exists (select * from sysobjects where name='Import_18Columns' and xtype='U') CREATE TABLE [Import_18Columns]( [Column 0] [varchar](max) NULL, [Column 1] [varchar](max) NULL, [Column 2] [varchar](max) NULL, [Column 3] [varchar](max) NULL, [Column 4] [varchar](max) NULL, [Column 5] [varchar](max) NULL, [Column 6] [varchar](max) NULL, [Column 7] [varchar](max) NULL, [Column 8] [varchar](max) NULL, [Column 9] [varchar](max) NULL, [Column 10] [varchar](max) NULL, [Column 11] [varchar](max) NULL, [Column 12] [varchar](max) NULL, [Column 13] [varchar](max) NULL, [Column 14] [varchar](max) NULL, [Column 15] [varchar](max) NULL, [Column 16] [varchar](max) NULL, [Column 17] [varchar](max) NULL, [Column 18] [varchar](max) NULL ) if not exists (select * from sysobjects

You could use the BCP command with the -t option. See [bcp Utility](https://msdn.microsoft.com/nl-be/library/ms162802(v=sql.120).aspx) And there is a lot of other ways. Just google for something like MSSQL import into .csv

SQL Quiz (W3Schools)[^] SQL Online Test | TestDome.com[^] Bastard Programmer from Hell :suss: 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.

For clarification on that the value is actually based on the UUID from java. And that UUID would seem to have a uniform distribution because the layout is not strictly ordered by time - the UUID uses the minor time (probably seconds/millis) as the most significant part.

If you pass a table valued parameter around, you can't modify the contents. You can only modify it in the procedure where it's declared. A temp table is definitely the way to go, and there's no danger in dropping it. Each active connection gets its own set of temp tables, so there's no chance of the code executing on one connection affecting a temp table from another connection. "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jschell wrote: What limit are you talking about? A 1 meg database would be pretty small these days. The SQL file. I meant a limit for SQL query, not a limit of the database itself. If I incorrectly understood the OP then sorry!

So it would be surrounded by quotes in the csv.. I stand by my comment

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."

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. :cool:

Your THIRD copy of this question: Replace Ids With value - Database Discussion Boards[^] Replace ids with values[^] "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Do you ask about using CAST or/and CONVERT? Or what did you mean?

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

So then adjust your index to start at 100 just to be sure.

This is a little late, but here go some of my annoyances. 1) The sky is falling emails. Just because your application isn't working, doesn't mean the entire environment is down. 2) CCing everyone in management because you are having an issue... 3) Failure to plan, just because you failed to plan out a release doesn't constitute an emergency on my part. Not communicating releases and complaining when something doesn't work right. [The DW team, loves to chew up 100s of gigs of space in a single release without communicating then screaming about when the drives fill up] 4) Trying to use the Database server as a File server, send mail server, or other random stuff. No, the database server is there to serve up data, not be some Swiss utility knife. 5) Not keeping in mind my time, and the totality of the environment. Please start out with where your problem is at. I have 1700 databases spread across Dev, Test, PreProd and Production. No I don't just know where you are having an issue at, if I was psychic I would come up with the winning lotto numbers so I no longer need to work. 6) Security rules are not just setup randomly, they are often established by controls put in place by auditors. I am accountable to those rules, so everyone needs to follow them. Things like demanding SA rights to a db instance, no, there is nothing at the Database Server level you need to change. Things have been setup that way for a reason. 7) Not taking advice on a designs that are just terrible. Just because it worked with 10 rows of data doesn't mean it will scale with 10M rows. Complaining about the server isn't going to solve your issue. Setting aside the petty grips, a lot of the issues boil down to communication and a willingness to collaborate. My time gets stretched in a lot of directions, I go from meetings with network, server, dev, auditors, management, vendors and yes even end users. It’s not always possible for me to just drop everything to help someone out. Common sense is admitting there is cause and effect and that you can exert some control over what you understand.