|
|
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 all,
I am exporting record-set of select statement into a csv, but when I am doing it, if the fields already have values with , and then that comma is going into the csv as it is, and because of it the import is becoming tough, I want to have an escape for those comma which are coming from the field values.
Any help would be grateful, thanks in advance friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You could use the BCP command with the -t option. See bcp Utility
And there is a lot of other ways. Just google for something like MSSQL import into .csv
|
|
|
|
|
Hi all,
I am trying to get all the columns and rows of a csv file using a select statement, when I am trying as in the below query, its retrieving all the rows and columns as one column and one row.
SELECT * FROM OPENROWSET(
BULK 'D:\Users\Abdul\Provider.csv',
SINGLE_CLOB) AS DATA;
I have tried in the following way
select *
into #T
from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\Users\Abdul;Extensions=csv;',
'select * from \Provider.csv') Test;
select *
from #T;
Its giving me the following error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Can any body please help me, am I missing anything in the above query, any help would be very much grateful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 9-Apr-18 13:16pm.
|
|
|
|
|
|
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] <a href="max">varchar</a> NULL,
[Column 1] <a href="max">varchar</a> NULL,
[Column 2] <a href="max">varchar</a> NULL,
[Column 3] <a href="max">varchar</a> NULL,
[Column 4] <a href="max">varchar</a> NULL,
[Column 5] <a href="max">varchar</a> NULL,
[Column 6] <a href="max">varchar</a> NULL,
[Column 7] <a href="max">varchar</a> NULL,
[Column 8] <a href="max">varchar</a> NULL,
[Column 9] <a href="max">varchar</a> NULL,
[Column 10] <a href="max">varchar</a> NULL,
[Column 11] <a href="max">varchar</a> NULL,
[Column 12] <a href="max">varchar</a> NULL,
[Column 13] <a href="max">varchar</a> NULL,
[Column 14] <a href="max">varchar</a> NULL,
[Column 15] <a href="max">varchar</a> NULL,
[Column 16] <a href="max">varchar</a> NULL,
[Column 17] <a href="max">varchar</a> NULL,
[Column 18] <a href="max">varchar</a> NULL,
[Column 19] <a href="max">varchar</a> NULL,
[Column 20] <a href="max">varchar</a> NULL
)
if not exists (select * from sysobjects where name='Import_18Columns' and xtype='U')
CREATE TABLE [Import_18Columns](
[Column 0] <a href="max">varchar</a> NULL,
[Column 1] <a href="max">varchar</a> NULL,
[Column 2] <a href="max">varchar</a> NULL,
[Column 3] <a href="max">varchar</a> NULL,
[Column 4] <a href="max">varchar</a> NULL,
[Column 5] <a href="max">varchar</a> NULL,
[Column 6] <a href="max">varchar</a> NULL,
[Column 7] <a href="max">varchar</a> NULL,
[Column 8] <a href="max">varchar</a> NULL,
[Column 9] <a href="max">varchar</a> NULL,
[Column 10] <a href="max">varchar</a> NULL,
[Column 11] <a href="max">varchar</a> NULL,
[Column 12] <a href="max">varchar</a> NULL,
[Column 13] <a href="max">varchar</a> NULL,
[Column 14] <a href="max">varchar</a> NULL,
[Column 15] <a href="max">varchar</a> NULL,
[Column 16] <a href="max">varchar</a> NULL,
[Column 17] <a href="max">varchar</a> NULL,
[Column 18] <a href="max">varchar</a> NULL
)
if not exists (select * from sysobjects where name='Import_4Columns' and xtype='U')
create table Import_4Columns (
[Column 0] varchar(max) null
, [Column 1] varchar(max) null<br />
, [Column 2] varchar(max) null
, [Column 3] varchar(max) null
)
if not exists (select * from sysobjects where name='Import_2Columns' and xtype='U')
create table Import_2Columns (
[Column 0] varchar(max) null
, [Column 1] varchar(max) null<br />
)
truncate table Import_21Columns
SET QUOTED_IDENTIFIER OFF
BULK INSERT Import_21Columns FROM 'D:\Users\Abdul\Provider.csv' WITH (FIELDTERMINATOR = '","', ROWTERMINATOR = '\n')
with FIELDTERMINATOR = ',' I tried both the FIELDTERMINATOR values
Its putting couple of new rows into one single column which is not an expected behavior, the openrowset is giving me linked server error means the dbas are not allowing developers to run certain sql commands. any help please.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello every one,
I need to improve my SQL skills as much as I can 
I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck 
Any ideas?recommendations?
thanx a lot
Emily
|
|
|
|
|
Hello every one,
I need to improve my SQL skills as much as I can
I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck
Any ideas?recommendations?
thanx a lot
Emily
|
|
|
|
|
Presumably you are using a dedicated IDE, SQL Server Management Studio SSMS, or one dedicated to your database.
I would recommend SQL Server Central. Microsoft SQL Server tutorials, training & forum.[^] as an excellent resource dedicated to SQL Server, concentrate on TSQL rather than the DBA stuff. Also try researching books on writing SQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In addition to the suggestion from @Mycroft-Holmes (which I agree with by the way), don't forget that there are lots of articles here on Code Project that can help you improve on specific topics within the "SQL World" ... SQL articles on CodeProject[^]
Use the star-rating system to get a view on how "good" the rest of the membership think the article is
|
|
|
|
|
|
hi all,
I have few SSIS Packages that have ConnectionStrings configured within Package Config file, now I want to set multiple variables values using Package configuration file, but the package configuration file is taking only one variable value, do I need to create different Package Configuration file for each variable that I want to set or is there any way I can set all the variable values using Package Configuration file.
And one more thing is I am going to deploy the SSIS Packages on the Server, where I need to put the Package Configuration Files at different locations, how can I change the Package Configuration Files Path? I am using SSDT 2012 and VS 2012, is it a problem?
Please I need this help, any help would be grateful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 3-Apr-18 16:40pm.
|
|
|
|
|
Hi all,
I am trying to insert values into Table variable within Dynamic Sql, I am able to select values from Table variable but not able to insert values in to the Table variable.
Here is how I am selecting values from Table variable
CREATE TYPE IntegerTableType AS TABLE (ID INT);
go
DECLARE @TempVehicles IntegerTableType;
INSERT @TempVehicles
values (1);
DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT *
FROM @TempVehicles;';
EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;
IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='IntegerTableType')
DROP TYPE [dbo].IntegerTableType;
GO
But when I am trying to insert values as below, I am not able to insert.
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
CREATE TYPE Results AS TABLE (TableName nvarchar(500), ColumnName nvarchar(500))
GO
Declare @Tables table (Id int identity(1,1), TableName nvarchar(500))
declare @Columns table (Id int identity(1,1), ColumnName nvarchar(500))
declare @Results AS Results
declare @IdMin1 int, @IdMax1 int, @IdMin2 int, @IdMax2 int, @col varchar(500), @cmd varchar(max), @TableName nvarchar(500)
insert into @Tables(TableName)
SELECT t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo'
order by t.name
select @IdMin1=min(Id), @IdMax1=max(Id) from @Tables
while(@IdMin1<=@IdMax1)
begin
set @TableName = (select top 1 TableName from @Tables where Id = @IdMin1)
<pre>
delete from @Columns
insert into @Columns(ColumnName)
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName
order by c.name
--insert into @Results (TableName) values(@TableName)
select @IdMin2=min(Id), @IdMax2=max(Id) from @Columns
while(@IdMin2<=@IdMax2)
begin
set @col=''
select @col=ColumnName from @Columns where Id=@IdMin2
SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM ' + @TableName + ' WHERE [' + @col +
'] IS NULL) BEGIN insert into @Results (TableName, ColumnName) values( ''' + @TableName +''',''' + @col + ','') end';
begin try
--select @cmd
--EXEC(@cmd)
EXECUTE SP_EXECUTESQL @cmd, N'@Results Results READONLY', @Results;
end try
begin catch
SELECT @col=@col + ', Error Message: ' + ERROR_MESSAGE()
end catch
--insert into #Results (ColumnName) values(@col)
set @IdMin2+=1
end
set @IdMin1+=1
end
select * from @Results where TableName is not null order by TableName, ColumnName
IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
DROP TYPE [dbo].Results;
GO
Can anybody please help me how can I finish this and let me know why is it not inserting into Table variable using Dynamic Sql, is there any way to perform that action. Any help would be much helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Is there a reason why you are creating a user defined TYPE for each table? Why not create a table variable
Declare @Tbl TABLE(ID int)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Because I am trying to insert into the Table variable using dynamic sql, its not doing, can you please help me how can I insert into the Table variable within dynamic sql without using the User Defined Type? Thanks in advance buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: @Results Results READONLY
There's a slight clue in that line to suggest that you can't write to the table valued parameter. 
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
If you need to write to the table, use a temp table instead. It looks like you at least started trying to do that - one of the commented-out lines refers to #results instead of @results.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
hi Rick, yes that's true I did achieve by using the Temp Table but I am always puzzled if I have any way to insert using table variable rather than temp table, because I don't like to drop, hence like to use Table variable which will be lost after the scope.
Any other way I can insert into table variable within dynamic sql?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
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
|
|
|
|
|
Hi Everyone, so i have a problem with my MySQL database, whenever i try to import the database i get an error that says
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
I was running a site and using VestaCP i wanted to reinstall my server, so i simply exported the database from PHPmyAdmin and then reinstalled the whole server.
Then again i installed VestaCP and now when i am trying to import the Database i get the following error
ERROR 1064 (42000) at line 31734: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''C:24:"WPSEO_Sitemap_Cache_Data":152414:{a:2:{s:6:"status";s:2:"ok";s:3:"xml";s:' at line 2
It is a wordpress database, i really have no clue as to whats happening. If you guys can help me out i'd appreciate it.
I read some articles on Google, and i think its related to the Mysql or PHp versions. But i couldn't find answers or threads specifically for this issue. So please help me out ASAP because my website is down from days. Thanks
|
|
|
|
|
Can you not post the full SQL? But I suggest you look at the second of the first pair of apostrophe's, which on the face of it should either not be there or be escaped.
|
|
|
|
|
You are taking about these
''C:24 apostrophes? i just have to remove this from the sql file and upload it?
I cant post the full sql file because its 1 GB and very very large.
|
|
|
|
|
Quote: just have to remove this from the sql file
Not necessarily, it depends on what comes before it - if those are just two apostrophe's on their own in the middle of a longer statement they're fine, and the problem lies elsewhere. BUT you shouldn't really be posting strings in an SQL statement anyway - it's a security risk; look into paramatised queries instead And they are far less prone to syntax errors
|
|
|
|
|
Member 13755227 wrote: I cant post the full sql file because its 1 GB and very very large.
1 GB? 
I thought the limit is about 1 Mb...
|
|
|
|
|
What limit are you talking about? A 1 meg database would be pretty small these days.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
