Database Discussion Boards

Re: how to access temp table field in subquery?

joost.versteegen11-Apr-18 23:53

joost.versteegen

11-Apr-18 23:53

thank you, i will try it

Re: how to access temp table field in subquery?

Richard Deeming19-Apr-18 2:34

Richard Deeming

19-Apr-18 2:34

Try using CROSS APPLY instead:
Making OUTER and CROSS APPLY work for you[^]

SQL

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

Exporting Select statement values into csv with escape characters

indian1439-Apr-18 12:16

indian143

9-Apr-18 12:16

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

Re: Exporting Select statement values into csv with escape characters

Victor Nijegorodov9-Apr-18 20:27

Victor Nijegorodov

9-Apr-18 20:27

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

Reading csv file data using selecte statement

indian1439-Apr-18 6:50

indian143

9-Apr-18 6:50

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.

Re: Reading csv file data using selecte statement

Victor Nijegorodov9-Apr-18 8:37

Victor Nijegorodov

9-Apr-18 8:37

Did you try what was suggested in:
sql server - SQL Bulk import from CSV - Stack Overflow
Using OPENROWSET to import CSV files

Re: Reading csv file data using select statement

indian14311-Apr-18 9:18

indian143

11-Apr-18 9:18

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

writing complex queries in Sql

emilyz788-Apr-18 8:42

emilyz78

8-Apr-18 8:42

Hello every one,

I need to improve my SQL skills as much as I can Smile | :)

I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck Confused | :confused:

Any ideas?recommendations?
thanx a lot

Emily

writing complex queries in Sql

emilyz788-Apr-18 8:42

emilyz78

8-Apr-18 8:42

Hello every one,

I need to improve my SQL skills as much as I can Smile | :)

I know how to do the basic stuff but when I try to do something a bit complex
I'm stuck Confused | :confused:

Any ideas?recommendations?
thanx a lot

Emily

Re: writing complex queries in Sql

Mycroft Holmes8-Apr-18 12:57

Mycroft Holmes

8-Apr-18 12:57

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

Re: writing complex queries in Sql

CHill608-Apr-18 23:33

CHill60

8-Apr-18 23:33

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

Re: writing complex queries in Sql

Eddy Vluggen9-Apr-18 2:16

Eddy Vluggen

9-Apr-18 2:16

SQL Quiz (W3Schools)[^]

SQL Online Test | TestDome.com[^]

Bastard Programmer from Hell Suspicious | :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.

Configuring multiple properties or values in SSIS Package using Package Configuration file

indian1432-Apr-18 14:18

indian143

2-Apr-18 14:18

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.

Trying to insert values into Table Variable in Dynamic Sql

indian1432-Apr-18 13:17

indian143

2-Apr-18 13:17

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

Re: Trying to insert values into Table Variable in Dynamic Sql

Mycroft Holmes2-Apr-18 14:22

Mycroft Holmes

2-Apr-18 14:22

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

Re: Trying to insert values into Table Variable in Dynamic Sql

indian1433-Apr-18 7:49

indian143

3-Apr-18 7:49

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

Re: Trying to insert values into Table Variable in Dynamic Sql

Richard Deeming3-Apr-18 8:44

Richard Deeming

3-Apr-18 8:44

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. Smile | :)

Use Table-Valued Parameters (Database Engine) | Microsoft Docs[^]:
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

Re: Trying to insert values into Table Variable in Dynamic Sql

indian1434-Apr-18 12:35

indian143

4-Apr-18 12:35

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

Re: Trying to insert values into Table Variable in Dynamic Sql

Richard Deeming5-Apr-18 8:06

Richard Deeming

5-Apr-18 8:06

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

Mysql database problem

Member 1375522730-Mar-18 18:12

Member 13755227

30-Mar-18 18:12

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

Re: Mysql database problem

A_Griffin30-Mar-18 21:41

A_Griffin

30-Mar-18 21:41

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.

Re: Mysql database problem

Member 1375522730-Mar-18 21:55

Member 13755227

30-Mar-18 21:55

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.

Re: Mysql database problem

A_Griffin30-Mar-18 22:12

A_Griffin

30-Mar-18 22:12

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

Re: Mysql database problem

Victor Nijegorodov30-Mar-18 22:45

Victor Nijegorodov

30-Mar-18 22:45

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... Confused | :confused:

Re: Mysql database problem

jschell31-Mar-18 7:29

jschell

31-Mar-18 7:29

What limit are you talking about? A 1 meg database would be pretty small these days.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.