Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How to allow remote MSSQL user to bulk insert my local txt file?

M
magpie QA Engineer last edited by

I have a txt file on my local PC. I run localy Java app which connects to remote MSSQL. Now I need to bulk insert from that text file into my database table.

However, SQL login can't read my file. I get:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot bulk load because the file "D:/My_Files/my_file.txt" could not be opened. Operating system error code 21(The device is not ready.)

I set permissions on My_Files folder for 'Everyone'. I can't create a Windows user with the same name like SQL login because it is too long for Windows user. Is there a solution?
Reply Quote 0
1 Reply Last reply
Y
yajahira last edited by

As mustaccio pointed out, a local path is not going to be accessible from the SQL Server, which is the error you're receiving regarding "D:/My_Files/my_file.txt. It's looking for a file on the drive on the server that your SQL instance is installed on (not your local computer's drive).

You may be able to create a network share to that folder and reference it by UNC path in the SQL Server instance though. How to do that is more so in the scope of a https://serverfault.com/ question.

Alternatively you can add code in your Java program to read the local file and then do a BULK INSERT command from the app to your database.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

BULK INSERT Data from file but ignore the first occurence of field terminator
SQL, Database Testing • sql server insert bulkc languageopy • • Marcee

2

0
Votes

2
Posts

0
Views

If the amount of data is small enough or you can otherwise afford to spend a bit more resources on the operation, there are ways around this. Most of the scenarios where I've done bulk inserts required additional data manipulation in any case, so I usually just insert the bulk data to a specially created bulk insert table, and then manipulate and transfer the data to the real table from there. Makes tasks such as this one a lot easier. But of course there's an increased overhead from handling the same data twice.
What's a good SQL Server backup schedule?
SQL, Database Testing • sql server backup • • Laycee

2

0
Votes

2
Posts

0
Views

R

As with all things in SQL Server, it depends. First thing you need to do is make sure you understand what each type of backup does. Books Online has all the gooey details, but here's my summary. A FULL backup contains everything within the database. A DIFFERENTIAL backup is cumlative NOT incremental. In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure. A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model. If you're using the simple recovery model then transaction log backups are not needed. Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster. I would recommend starting with a full backup each day. You can always reduce the frequency later. Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days. If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon. The transaction log backup only backs up the log. The frequency of the log backup will determine how much data you're willing to lose in the event of a failure. If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed. 15 minutes is a good frequency, but every 30 minutes works perfectly for my environment. As I said earlier, it all depends on your environment. After you've designed and setup your backup schedule, remember to test it on an alternate server. Practice restoring your full, diff, and log backups so that you know everything works like you designed it. Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.
S

How to insert (file) data into a PostgreSQL bytea column?
SQL, Database Testing • postgresql plpgsql blob datafile • • shizuka

2

0
Votes

2
Posts

0
Views

Use pg_read_file('location_of file')::bytea. For example, create table test(id int, image bytea); insert into test values (1, pg_read_file('/home/xyz')::bytea); Manual
Y

Support Native Error 25009
Software Programming • sql server • • yajahira

2

0
Votes

2
Posts

0
Views

T

Give him a @ before the quotes:System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=C:\Base de datos\MisPedidos.sdf"); Update:Since you're using a smart device I assume with Windows CE, such operating system requires that all route start with a backslash\"Let's update the connection chain:System.Data.SqlServerCe.SqlCeConnection con = new System.Data.SqlServerCe.SqlCeConnection(@"Data Source=\\Base de datos\\MisPedidos.sdf"); Reference: https://stackoverflow.com/q/20229175/3613462
How to collect NTFS file properties and insert into SQL Server table
SQL, Database Testing • sql server sql server 2012 powershell • • Demir

2

0
Votes

2
Posts

0
Views

SSIS is well equipped to handle CSV files and load them into SQL Server. You can have a very simple package using the Flat File Source. The dialogue and setup is a familiar windows "wizard" like process, and most of it is automated... what you need to pay attention to is that it has correctly guessed your file for lengths and data types. You can either adjust the settings in the connection manager or you can later change data types with SSIS tasks. Note that if you have say 10,000 rows of integers and then start getting characters the flat file source may easily assign an integer data type to that column, then fail when it encounters the characters. Thus with large files that may not be well structured you have to pay more attention to these settings. The Suggest Types... button allows you to increase the number of inspected rows, but I have found that even this can still recommend the wrong data types. SSIS is a huge tool and you can perform data clean-up tasks or even split data into different tables from the single CSV. If you have different tables use tasks like Multicast or Conditional Split. You may also find that Data Conversion and Derived Column can help you efficiently produce the data you need as it moves through your package. I wouldn't do much more than clean, split, modify, and load the data into SQL Server with SSIS though. SQL Server is highly optimized to produce aggregates, sorts, etc., while SSIS is less capable for such tasks. Tasks like Aggregate are blocking transforms which essentially means it can stall your SSIS package and consume a lot of memory. As an example the below SSIS dataflow performs the following tasks: Reads a CSV file Creates derived columns which are just trimmed versions of the originals Performs a look-up to see if the record already exists in the destination If the record was not found then it is inserted in the destination
A

Error when creating a distribution of an executable with sql bank using the Entity Framework
Software Programming • c sharp sql server winforms entity framework • • Amritpal

2

0
Votes

2
Posts

0
Views

O

I ended up finding a better solution for my need: SQLite. SQLite also supports the Entity Framework and ends up being much simpler to use in a stand-alone application, requires no additional installation on the user machine, and the package already generates the required dlls. For those interested in a tutorial, in this http://www.andrealveslima.com.br/blog/index.php/2016/09/28/trabalhando-com-sqlite-no-c-e-vb-net/ There is a step by step of how to configure and use SQLite including with the Entity Framework.
S

SQL Data in form of Date+Time bulk insert into one column
SQL, Database Testing • sql server t sql import • • Saumya

2

0
Votes

2
Posts

0
Views

Y

You can process the data using regular T-SQL as part of the bulk loading process, using the OPENROWSET(BULK... function and the XML format file. INSERT WITH (TABLOCKX) SELECT FROM OPENROWSET ( BULK , FORMATFILE = , ... ) AS alias; The key part is the in the select clause. This provides a lot of flexibility. A more concrete example: INSERT dbo.zz_u_test_TEMP WITH (TABLOCKX) ( col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty, col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty ) SELECT REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)), REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)), REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)), REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)), REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)), REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)), RowSource.col7_dirty, RowSource.col8_dirty, RowSource.col9_dirty, RowSource.col10_dirty FROM OPENROWSET ( BULK 'C:\Documents and Settings\Paul\Test.data', FORMATFILE = 'C:\Documents and Settings\Paul\format.xml', CODEPAGE = 'RAW', FIRSTROW = 0, LASTROW = 0, MAXERRORS = 0, ROWS_PER_BATCH = 0 ) AS RowSource; This gives you the potential benefits of minimally-logged fast bulk load, while applying transformations in the SELECT clause. That is a code example I wrote years ago for a different problem, but it should work for you. If I get a moment to create a table and file (not supplied in the question), I might add a more specific version.
C

Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login
SQL, Database Testing • sql server • • Cleofas

2

0
Votes

2
Posts

0
Views

A

All logins exist in the master database. Why it is default is probably related to this, but you can change the default database to your user database if you wanted to. The default being master simply means they have to explicitly set the user database in the connection string for the application. The default database has nothing to do on why the logon can connect to the instance. This comes from being granted connection privileges to the instance. This is done by default when using SSMS to create the login. Additional Info Public role is granted connect permissions and any login created is automatically part of that role. The only permissions granted for every endpoint is Connect. The only thing they get at the server level is view any database. You actually cannot remove a login from the public role either, SQL Server does not let you because it is hard coded in there somewhere. You will most security standards for things like HIPPA or DoD (STIGs) want you to simply harden what the public role can do at the server level and within each database. See "INF: SQL Security - Restricting access to public..." for a bit more information on it all. Creating a login straight from GUI or from just CREATE LOGIN and you will automatically see these permissions at the server level:
SQL SERVER and Active Directory integration
Software Programming • sql server ac languagetive direc lan • • emmalee

2

0
Votes

2
Posts

0
Views

A

It is not possible, because AD uses its own basis, so the information is concentrated on this basis. However, it is possible to do the reverse. Just create an application adapt your apps to Single Sign On, i.e. all login screens of your application, will be directed to a specific application and this application will authenticate in AD. http://en.wikipedia.org/wiki/Single_sign-on#Integrated_Windows_Authentication http://searchsecurity.techtarget.com/definition/single-sign-on
J

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?
SQL, Database Testing • sqlite index primary key c languagelustered primar • • jules

2

0
Votes

2
Posts

0
Views

R

The documentation says: Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics. Here, "significantly" means that the new data changes the characteristics so much that the index is no longer as useful, or that it is much more useful than previously. However, for a primary key, the selectivity usually does not change, so it should not be necessary to run ANALYZE after larger inserts. (With or WITHOUT ROWID does not make any difference for this.)
S

JOIN CONDITIONED SQL SERVER
Software Programming • sql server sql • • simrah

2

0
Votes

2
Posts

0
Views

K

Try this one and watch the details that could come out. Greetings SELECT SUBSTRING (p.begins, 0, 9) as begins, COALESCE(al.id_almacen,al2.id_almacen), u.group_identifier, e.cargo, CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN DATEDIFF(hour, start_hour, end_hour ) - (break_minutes/60) ELSE 0 END as horas, CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN DATEDIFF(hour, start_hour, end_hour ) - (break_minutes/60) ELSE 0 END as horas, CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN md.meta_diaria ELSE 0 END AS meta_diaria, c.semana_bono, CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN (ROUND(md.meta_diaria, 2) / SUM( CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN DATEDIFF(hour, start_hour, end_hour ) - (break_minutes/60) END) OVER(PARTITION BY SUBSTRING (p.begins, 0, 9), al.id_almacen) ) * ( DATEDIFF(hour, start_hour, end_hour ) - (break_minutes/60) ) ELSE 0 END as meta, SUM( CASE WHEN e.cargo <> 'JEFE(A) DE TIENDA' THEN DATEDIFF(hour, start_hour, end_hour ) - (break_minutes/60) END) OVER(PARTITION BY SUBSTRING (p.begins, 0, 9), COALESCE(al.id_almacen,al2.id_almacen)) AS total_horas_turno_dia, ROUND(SUM(CASE WHEN e.cargo = 'JEFE(A) DE TIENDA' THEN md.meta_diaria END) OVER(PARTITION BY semana_bono, COALESCE(al.id_almacen,al2.id_almacen)), 2) as meta_semanal_tienda FROM usuarios as u INNER JOIN planificacion as p ON u.identifier = p.identifier INNER JOIN turnos as t ON t.id_shift = p.hashed_shift_id INNER JOIN empleado as e ON REPLACE(e.cod_empleado, '-', '') = u.identifier LEFT JOIN almacen as al ON ( al.cod_almacen = u.group_identifier) LEFT JOIN almacen as al2 ON ( al2.cod_almacen = e.cod_almacen_ingreso) WHERE P.begins = 20160701 AND al.cod_almacen = '101' AND u.enabled = 1 ORDER BY begins, group_identifier ASC; The following fields to which table belong ?(hour, start_hour, end_hour)
J

Why does a query on a local instance with local database files cause NETWORK_IO waits?
SQL, Database Testing • sql server sql server 2012 network extended events wait types • • jules

2

0
Votes

2
Posts

0
Views

Usually this surfaces as ASYNC_NETWORK_IO outside of Extended Events, but yes, it is the time SQL Server spent waiting for the caller (in this case SSMS) to consume the results. If the caller is slow (like SSMS) then SQL Server has to sit there and wait before feeding more rows. If you have a wide result set or large values, you should compare results to grid and results to text. But really, you shouldn't be timing anything based on response times in SSMS, since it does unrealistic things with the data and how it renders that your application won't be doing (hopefully). I wrote about this wait type (and 9 other common ones) here: What to do (or not do) about top wait stats
T

How to find error rows in bulk insert?
SQL, Database Testing • mysql mysql 5.6 bulk insert c languagetas • • tahishae

2

0
Votes

2
Posts

0
Views

E

To locate the rows you want to see, you must do two things: change the way you create the table change the way you load data into the table Why Change the Way You Create the Table ??? When you did this create table tgt as select * from src; You create the tgt table without any indexes. You can verify this by running show create table src \G show create table tgt \G and you will see src with its indexes and tgt without them. Consequently, with no indexes around, the table load would be the fastest. If you at least had a primary key. you could navigate the table by some numeric id column. So, to create table with the indexes in place, do this: create table tgt like src; You could then run show create table src \G show create table tgt \G and see that they are the same in structure. Why Change the Way You Load Data into the Table ??? You could just start loading like this insert into tgt select * from src; When the load is done, you could run SELECT * FROM tgt WHERE id = 265975; SELECT * FROM tgt WHERE id = 265976; You could get an glimpse at when the row looks like but the created_timestamp may not be populated. If it is populated correctly, then the problem is essentially solved. Another Way to See Which Row Without Loading Data You could isolate which rows have an issue with the created_timestamp by doing something else # mysqldump --skip-extended-insert mydb src > dumpfile.txt # head -265976 dumpfile.txt | tail -2 This will show you the rows that would have been inserted. This is all the help I can suggest since I do not know what the table structures are.
J

Replace function removing Ones / 1 when removing char(185) (superscript 1)
SQL, Database Testing • sql server sql server 2008 r2 collation temporary tables unic languageode • • juvenalb

2

0
Votes

2
Posts

0
Views

A

CHAR(185), being VARCHAR data and using an 8-bit code page, can be a different character depending on the code page being used (which is determined by the collation of the current database when using the CHAR() function). In many code pages, including the common Windows-1252 (Latin1_General), the character is a superscript 1 (i.e. "1" ). This character, depending on the collation / code page being used, might have a "best fit" mapping to a regular "1". Also, when using a Windows collation, case-sensitivity can affect the outcome of matches between subscript, superscript, and regular versions of the same characters (seriously! see example below). This also covers NVARCHAR / Unicode data (including UTF-8 in VARCHAR starting in SQL Server 2019). When needing to match on specific characters you need to use a binary collation (i.e. one ending in _BIN2 unless on SQL Server 2005 or older, in which case only the _BIN collations are available). SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE SQL_Latin1_General_CP1_CI_AS -- no rows (SQL Server collation, code page 1252, case insensitive) SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CI_AS -- 1 row (SQL Server collation, code page 437, case insensitive) SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CS_AS -- 1 row (SQL Server collation, code page 437, case sensitive) SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE Latin1_General_100_CI_AS -- 1 row (Windows collation, code page 1252, case insensitive) SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE Latin1_General_100_CS_AS -- no rows (Windows collation, code page 1252, case sensitive) SELECT CHAR(185) WHERE CHAR(185) = '1' COLLATE Latin1_General_100_BIN2; -- no rows (Windows collation, code page 1252, _BIN2 collation; always works!) As noted above, Unicode data (regardless of type of collation) works the same as VARCHAR data used with a Windows collation (because both scenarios use the same Unicode rules): SELECT NCHAR(185) WHERE NCHAR(185) = N'1' COLLATE Latin1_General_100_CI_AS -- 1 row (Windows collation, Unicode data, case insensitive) SELECT NCHAR(185) WHERE NCHAR(185) = N'1' COLLATE Latin1_General_100_CS_AS -- no rows (Windows collation, Unicode data, case sensitive)
Z

How do I create an insert-only user for a Postgres table with an index?
SQL, Database Testing • postgresql permissions schema • • zymir

2

0
Votes

2
Posts

1
Views

You cannot use GRANT on an index. Indexes do not have separate permissions, they are "implementations details" of the owning table. You already covered the INSERT privilege (as table owner or superuser): GRANT INSERT ON dbname.surveys TO SurveyWriter; If you want the role to log in, it needs access to the database. GRANT CONNECT ON DATABASE my_database TO SurveyWriter; Plus, the role needs the LOGIN privilege. CREATE USER does that implicitly. The manual: CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, [...] And access in pg_hba.conf. (Else you might want to use SET ROLE instead.) You need at least USAGE on the schema - which is given by default in the public schema, unless you revoked it. The schema in your example has the (misleading!) name "dbname". So: GRANT USAGE ON SCHEMA dbname TO SurveyWriter; You are not confusing databases and schemas, are you? See: PostgreSQL and default Schemas Since you use a serial, you also need at least USAGE on the connected SEQUENCE: GRANT USAGE ON SEQUENCE public.surveys_id_seq TO SurveyWriter; Or consider an IDENTITY column instead, where no separate privileges are required. Requires Postgres 10 or later. See: Prevent explicit insert into serial columns Related: How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA? Aside 1: Use legal, lower-case names everywhere to avoid confusion. SurveyWriter will be created as surveywriter. I suggest survey_writer instead. See: Are PostgreSQL column names case-sensitive? Aside 2: Consider timestamp with time zone: created_at timestamptz DEFAULT current_timestamp. See: Ignoring time zones altogether in Rails and PostgreSQL

How to allow remote MSSQL user to bulk insert my local txt file?

Suggested Topics

BULK INSERT Data from file but ignore the first occurence of field terminator SQL, Database Testing • sql server insert bulkc languageopy • • Marcee

What's a good SQL Server backup schedule? SQL, Database Testing • sql server backup • • Laycee

How to insert (file) data into a PostgreSQL bytea column? SQL, Database Testing • postgresql plpgsql blob datafile • • shizuka

Support Native Error 25009 Software Programming • sql server • • yajahira

How to collect NTFS file properties and insert into SQL Server table SQL, Database Testing • sql server sql server 2012 powershell • • Demir

Error when creating a distribution of an executable with sql bank using the Entity Framework Software Programming • c sharp sql server winforms entity framework • • Amritpal

SQL Data in form of Date+Time bulk insert into one column SQL, Database Testing • sql server t sql import • • Saumya

Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login SQL, Database Testing • sql server • • Cleofas

SQL SERVER and Active Directory integration Software Programming • sql server ac languagetive direc lan • • emmalee

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite? SQL, Database Testing • sqlite index primary key c languagelustered primar • • jules

JOIN CONDITIONED SQL SERVER Software Programming • sql server sql • • simrah

Why does a query on a local instance with local database files cause NETWORK_IO waits? SQL, Database Testing • sql server sql server 2012 network extended events wait types • • jules

How to find error rows in bulk insert? SQL, Database Testing • mysql mysql 5.6 bulk insert c languagetas • • tahishae

Replace function removing Ones / 1 when removing char(185) (superscript 1) SQL, Database Testing • sql server sql server 2008 r2 collation temporary tables unic languageode • • juvenalb

How do I create an insert-only user for a Postgres table with an index? SQL, Database Testing • postgresql permissions schema • • zymir

BULK INSERT Data from file but ignore the first occurence of field terminator
SQL, Database Testing • sql server insert bulkc languageopy • • Marcee

What's a good SQL Server backup schedule?
SQL, Database Testing • sql server backup • • Laycee

How to insert (file) data into a PostgreSQL bytea column?
SQL, Database Testing • postgresql plpgsql blob datafile • • shizuka

Support Native Error 25009
Software Programming • sql server • • yajahira

How to collect NTFS file properties and insert into SQL Server table
SQL, Database Testing • sql server sql server 2012 powershell • • Demir

Error when creating a distribution of an executable with sql bank using the Entity Framework
Software Programming • c sharp sql server winforms entity framework • • Amritpal

SQL Data in form of Date+Time bulk insert into one column
SQL, Database Testing • sql server t sql import • • Saumya

Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login
SQL, Database Testing • sql server • • Cleofas

SQL SERVER and Active Directory integration
Software Programming • sql server ac languagetive direc lan • • emmalee

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?
SQL, Database Testing • sqlite index primary key c languagelustered primar • • jules

JOIN CONDITIONED SQL SERVER
Software Programming • sql server sql • • simrah

Why does a query on a local instance with local database files cause NETWORK_IO waits?
SQL, Database Testing • sql server sql server 2012 network extended events wait types • • jules

How to find error rows in bulk insert?
SQL, Database Testing • mysql mysql 5.6 bulk insert c languagetas • • tahishae

Replace function removing Ones / 1 when removing char(185) (superscript 1)
SQL, Database Testing • sql server sql server 2008 r2 collation temporary tables unic languageode • • juvenalb

How do I create an insert-only user for a Postgres table with an index?
SQL, Database Testing • postgresql permissions schema • • zymir