|
Ok guys - I guess the verdict's in. It's what I thought initially thought would be the case too. Its just that the doco on Correlated vs Uncorrelated sub-queries threw me a little, where correlated sub-queries are re-evaluated for each row of the main query. I do agree, however, that re-evaluating the sub-query will be significantly better than searching the full result set.
Cheers people.
|
|
|
|
|
Hi All,
I am trying to write a SQL Query data on to Excel directly, but it is giving me the following error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
My code is below
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;Database=C:\Test.xlsx;',
'SELECT [ApplicationName], Date FROM [Sheet1$]')
SELECT [ApplicationName], GETDATE() FROM Application
GO
GO
When initially it gave me above error I copied the excel file to the Server itself and ran the linked server script as below
exec sp_addlinkedserver @server = N'webdevsql1',
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xlsx;',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;
Then ran the script again, now it is giving me little bit different error (ie. Cannot execute the query "SELECT [ApplicationName], Date FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)").
Can anybody please help me in this regards? any help a code snippet, a link or even a suggestion helps me. I am also searching and trying from online, but if you have already familiar may be your experience helps me. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 8-Mar-16 14:01pm.
|
|
|
|
|
Hi guys some how I could able to find a way to write selected query values into an xls file but not on the xlsx file yet. If there is a way out for it from somebody can help me, if not for now I will live with it.
But my bigger issue is the below script is writing on to Excel file without headers, can somebody please help me in writing on the excel file along with headers?
Here is my script
DECLARE @cmd VARCHAR(255)='',
@sqlQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@FilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xls"'
SET @cmd = 'bcp '+ @sqlQuery + ' queryout ' + @FilePath + ' -T -c'
Exec xp_cmdshell @cmd
Any help a code snippet, a link or even a suggestion would be greatly helpful, thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation.
This also indicates a workaround[^].
All of which is a kludge!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK I got an Idea I will create a template file with headers then can I append the contents of the headers file + the output file of the bcp into a final file so that I can have both the headers and content.
Or you can give me how to append row or data in the beginning of the file.
Can you please give me some suggestion or link or code snippet to append header file + content file into a final file using bcp or command shell etc?
Any help is appreciated, thanks in advance I am also searching for it.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 12:00pm.
|
|
|
|
|
Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file
DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '
+ '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'
SET @cmd = 'bcp '+ @sqlTemplQuery + ' queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'bcp '+ @sqlContentQuery + ' queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmd
SET @cmd = 'copy ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmd
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:55pm.
|
|
|
|
|
But one small limitation here, bcp is creating files only in xls format (97-2003) but any body know how to do the same thing to get document in xlsx format like (2007) format? For now I can live with it but just want to acquire more knowledge on it.
Any help is appreciated, thanks in advance friends 
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 9-Mar-16 19:57pm.
|
|
|
|
|
Hi All,
I am migrating data from one table another table in different databases. When source table has line feed and carriage return the destination table is not getting that. All I am doing is inserting the records using select statements.
Can anybody please help me by giving a suggestion how to select columns by preserving line feed and carriage return characters. I am also searching if you know sure that would be helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Your code must be doing something to change the data. The SQL should return exactly what is stored in the database for each record.
|
|
|
|
|
Can you share a sample of the code?
|
|
|
|
|
Sorry, yeah it was code issue, thanks for all your support friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Sorry for a so stupid question.I am new beginer in C# and ASP.net.
Now I am writing the code for my web connect to database.
I am confuse about the function and procedure.
In VB, I store it in .vb file and then I can call in any form.
How can I do the same in website project asp.net.
|
|
|
|
|
|
Hi All.
I have SQL Server, link to Oracle DB.
In the SQL server I have a T-SQL command
select Moto.ne_code,
Moto.supplier_id,
Moto.ne_type_id,
Moto.sta_datetime,
from PMCALCULATE..[PM_TOOL].[MOTOROLA_CELL_HOURLY_KPI] Moto
and I get the result.
But the sta_datetime in the result is not ok
it is similar to: 2016-02-03 00:00:00 00000000
How to format it to yyyy/mm/dd hh24
|
|
|
|
|
What you are getting is a DATETIME data type which is correct. If you want to display it in a particular format then user CONVERT to get the format you need.
You MUST store the data in your SQL Server table as DATETIME, NEVER store DATETIME as a VARCHAR.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have try
select
convert(datetime2(0),Moto.sta_datetime) as Sta_Date1,
convert(datetime,Moto.sta_datetime) as Sta_Date2,
But the result is not as i want
All The result is:2016-02-03 00:00:00
I Want date only and time only
How can I do this.
|
|
|
|
|
|
Where are you using the result:
storing it in the database - do NOT format the data
displaying it in a UI - the UI needs to do the formatting
using it in a report - the report needs to do the formatting
exporting it to a file - this is the ONLY scenario where you should convert the datetime to a string (varchar) convert(varchar(20),DateValue,103) is an example.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I only Want to get Yesterday data from Oracle store in the SQL database.So that why I want to get exacly date to compare with the date in the SQL Database.
|
|
|
|
|
If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Now I Use T-SQL
select
Moto.ne_code,
Moto.sta_datetime,
convert(varchar(10),sta_datetime,111) as Sdate,
convert(time,sta_datetime) as S_Time,
Moto.province_id,
Moto.ne_id,
bss_cssr cssrv1,
tchtc traffic,
cssr cssrv2,
hosr_v2 hosrv2,
dropc dcr,
dcr_v2 dcrv2,
cavl calvol,
hansr hosr,
tchblor tchblr,
sdcchbr sdcchblkr
from OracleDBLink
where
convert(varchar(10),sta_datetime,111) = CONVERT(varchar(10), getdate()-1,111)
and ((bss_cssr <=95) or (dropc>=3) or (hansr<=95)) and cavl>=50
it seem to be OK get the result as I Want.
But I can Put it in the View.
I Want to creat a job to do it everytime
|
|
|
|
|
Oracle has two major temporal types, Date and Timestamp, where Timestamp has higher precision but less functionality.
The easiest way to get a date without the time component is Trunc(MyDate)
|
|
|
|
|
Hi,
I have below date text and I want to know how can I convert it to valid MySQL datetime which is yyyy-mm-dd HH:mm
Nov 20 2015 10:51AM
Nov 20 2015 10:16PM
Nov 20 2015 10:01AM
Nov 20 2015 8:43PM
Nov 20 2015 8:04PM
Nov 20 2015 5:49PM
Nov 20 2015 5:38PM
Nov 20 2015 5:23PM
Nov 20 2015 5:03PM
Nov 20 2015 4:57PM
Nov 20 2015 2:10PM
Nov 20 2015 1:51PM
Nov 19 2015 12:24AM
Nov 19 2015 11:56AM
Nov 19 2015 11:32PM
Nov 19 2015 11:27PM
Nov 19 2015 10:15AM
Nov 19 2015 10:12PM
How can I do this please?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
This should work:
SELECT DATE_FORMAT(STR_TO_DATE('Nov 20 2015 10:51AM', '%b %d %Y %h:%i%p'), '%Y-%m-%d %H:%i'); #Returns 2015-11-20 10:51
SELECT DATE_FORMAT(STR_TO_DATE('Nov 20 2015 8:43PM', '%b %d %Y %h:%i%p'), '%Y-%m-%d %H:%i'); #Returns 2015-11-20 20:43
-NP
Never underestimate the creativity of the end-user.
|
|
|
|
|
Wow doesn't MySQL have the convert function?
SELECT CONVERT(DATETIME,'Nov 20 2015 8:43PM')
Is the SQL Server method, so simples!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
