|
Mycroft Holmes wrote: loop
Loops are a last resort in databases. They are built to work on sets.
|
|
|
|
|
Hello,
I am trying to load a json file from network location into a table on sql server 2016. The domain account has access to both the file on network as well as the database.
I keep receiving this message:
Cannot bulk load because the file "\\<networklocation>\loaddata.json" could not be opened. Operating system error code 5(Access is denied.).
In the above error message, does the user not have access to file or to perform the "bulk load" action?
Much appreciated!
|
|
|
|
|
Also, I am using Windows authentication for SQL server, and this same user also has access to the share on network.
|
|
|
|
|
I think it means that the SQL Server identity (not the user) does not have access to the network drive. I always move the file to the SQL Server server or loaded it via c#.
Loading via c# I would put into a datatable EXACTLY matching the destination table and use BULKCOPY [^] to insert it into the destination
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'll swear I have this right
The field is smalldatetime. I'm just picking up the record and inserting it.
On select, I used
CONVERT(varchar(33), ADD_DATE, 121) AS ADD_DATE,
Oninsert, the sql looks like this
'1900-01-01 00:00:00.000'
I read up on sql date times and the different formats. If I pick it raw, and try o insert it, I get an object error.
In SQL Manager, I get
Conversion failed when converting character string to smalldatetime data type.
Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, bool given in C:\App\Dev\PCAD\repository\projectOrder.repository.php on line 1182
Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 295 [code] => 295 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. ) ) addProjectCost 1749 INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 ) - 1
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
That is why you used parameterized queries instead of strings.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
jkirkerx wrote: INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 )
I guess the VALUES list does not correspond the fields list, so the value form ADD_DATE seems to be '0' rather than '1900-01-01 00:00:00.000'.
|
|
|
|
|
I thought about what you both said, and would like to say thanks and show gratitude as well.
I went back and keep the Select statement raw, took the convert out.
The dumped my CRUD function to add the record, and converted the calling function to SQL.
Then used PHP to convert the raw date object I picked up to a string, and declared it in SQL.
I suppose I could go back to my add CRUD function and do the conversion there instead
$result2 = sqlsrv_query($conn, $query2) or die(" setDbProjectCost " . LINE . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
$row2 = sqlsrv_fetch_array($result2);
$addDateString = $row2[13]->format('Y-m-d H:i:s') . '.000';
$query3 = "
DECLARE @addDate AS VARCHAR(33) = '$addDateString';
INSERT INTO [proj_cost]
VALUES ... @addDate
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
If I run a SQL statement, like this:
SELECT * FROM my_table
is there any SQL statement / trick to find the name of every column from '*' ?
Because if I write:
SELECT id, name, age FROM my_table
yes, I could extract the columns name, by text analyze. But how about '*' case ?
|
|
|
|
|
|
Yes, I know that, but the question is, how can I know the columns name for the following SELECT (programmatically):
SELECT * FROM table1, table 2 WHERE table1.id = table2.id;
?
modified 3-Jun-21 12:17pm.
|
|
|
|
|
I'd just do it the same way as for a single table:
- obtain column names for table1;
- obtain column names for table2;
- ...
|
|
|
|
|
So, you are saying that I need to do a static analyze on SELECT text and see what columns I have, if I enumerate the columns in the SELECT statement, then I have it, if I don't (I have '*') then I have to query the tables names / columns name and will find them. I thought there is a SQL trick to find the columns name in any circumstances.
|
|
|
|
|
I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses
SELECT [TABLE_NAME]
,[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
where TABLE_NAME = <your_temp_view> to return the recordset with the tables/columns names,
then delete the temp view.
PS: this should work with SQL Server 2008 and above.
|
|
|
|
|
Sound good ! Because this solution should be cross server platform, it is available on the other SQL servers, like Oracle, Informix, MySQL, and so on ?
|
|
|
|
|
You have to test it yourself! Just check out the links I gave you here
|
|
|
|
|
|
AFAIK, the OP develops in C++.
However, I may be wrong.
|
|
|
|
|
|
But it is managed C++/CLI. 
It has nothing to do with the native C++. 
|
|
|
|
|
Yeah, but ...
Using ADO.NET in MFC Projects
(And it's a "database" forum)
Before ADO.NET there was ADO, and MFC did ADO.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
modified 4-Jun-21 15:09pm.
|
|
|
|
|
Interesting idea. Thank you!
BTW, I used ADO in my big VS2010 project (C++/MFC with ADO with SQL Server) from 2009 to 2015, of course without any mixture with managed code! 
|
|
|
|
|
Try this select * from table1 where 1=1 this should return an empty datatable with all the columns. Simply iterate the columns to get their names.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Try:
SELECT [name]. [column_id] AS [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id];
That will get the column names and ordinal position.
|
|
|
|
|
I suggest you read the documentation:
Column IDs might not be sequential.
If a column has ever been dropped from the table, you will end up with gaps in the column ID sequence.
To get a true ordinal position, you'd need to use the ROW_NUMBER windowing function - for example:
SELECT [name], ROW_NUMBER() OVER (ORDER BY [column_id]) As [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
