I need to create a T-sql script to create logins in bulk so users can access views on the database. Usernames as used in the local domain are all stored in a table.
Users already exist in the domain (let’s say: MYDOMAIN)ADSI.dbo.Ad.username is the column wich contains the usernames as used in the Active Directory domain (+/- 350)Script needs to check if login already exists to make sure login is not added twice.If possible give all these users db_datareader role, in let’s say: MYDATABASE
I want to be able to run the following command from SSMS (as an ad-hoc query).
BULK INSERT Database_Name.dbo.Table_Name FROM 'serverfile.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', MAXERRORS = 0);
When I do I get:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "serverfile.txt" could not be opened. Operating system error code 5(Access is denied.).
I have full access to the file.I can do the same command successfully if the file is stored on a local drive on the server.
According to my DBA I can not run it with a remote file location because I don't have the SA permission. His solution is for me to create a job that runs the command. I have done so and the job works correctly.
Is he correct that there is no way for me to be able to run it from SSMS without SA permissions?
I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.
i then select the database i want the login to be associated with, but once i click ok i get this exception: Create failed for Login ''. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) "An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)
I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time. Any ideas?
I am using a SQL server 2008 database.I am working on a windows user.I noticed when I create a user in a database for a login, even that login does not exist in the database, the user is still successful created.I suppose it should error out.
Or I miss something?
I am using this script: IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mydomainmyuser') CREATE USER [mydomainmyuser] FOR LOGIN [mydomainmyuser] WITH DEFAULT_SCHEMA=[dbo] GO
I am using SQL Server 2000. But i am not able to see the BuiltinAdministrator account in the logins list in Enterprise Manager but by default the owner of the system gets access to all databases and objects in the system. I would like to restrict that by restricting the access of BuiltInAdministrator account but it is not visible. Is there any option to be enabled to make that visible?
I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.
I am not sure if SQL Server 2008 has this capabilities.
Can any of you tell me how to create a login in SQL Server 6.5 with System Administrator privileges, like we can do with SQL 7 or SQL 2000 ? I don't want to use the sa login.
I have installed on my PC Visual Basic 2005 Express Edition which installed SQL Server 2005 Express Edition. In addition I downloaded and installed SQL Server Management Studio Express(SSME).
Now using SSME I can connect into SQL server express with the default login that was created by the installation. I want to create a new login:
Right click on Security > New > Login
I filled in all the details. However, when I click OK I get the following error:
Create failed for Login 'Mauriceewlogin'. Windows NT user or group 'Mauriceewlogin' not found. Check the name again.
Please, help. I am getting fraustrated with this error.
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
My PC is Window XP Pro and I'm using Microsoft SQL Server 2005 Express and Microsoft SQL Server Management Studio Express. My question is how to create a login userid and password under "SQL Server Authentication"? (as shown in http://www.findingsteve.net/print_screen.jpg) Any tutorial about this I can read?
I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected.
BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt' WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR=' ',FIRSTROW=172)
The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option.
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
I have a CSV file that I am trying to bulk load into a temp table. The data in the file is all jumbled together, as in, there does not appear to be a row terminator. However, I do see a bunch of little rectangular boxes that I assume are the row terminators.
When I run the bulk insert, the data is treated as one string. For example... If I have 10 columns in the table, the 10 columns will be populated, but the remainder of the data is dumped into the last column.
Here are the row terminators I have used so far that haven't worked.
USE TEST GO /****** BULK INSERT ******/ BULK INSERT [Table01] FROM 'C:empdata.csv'
[code]....
I am using above code to insert csv file data which consist of arabic data as well. Upload is successful however Arabic field data is uploaded with invalid characters and getting the following error Msg 4864, Level 16, State 1, Line 3...Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
I realise this may be a very simple question for most of you, but if you can help this could save me a ton of work. Yes, I am a newbie. I need to create a database and bulk insert data. The format for the table is set as an html file, the schema file is set as a txt file, and the data is in the form of a text file. I can see that this should be simple, but I just can't get the syntax right. I have tried writing a simple query to do this and have read and re-read the appropriate microsoft pages but still no luck - not for the want of trying. What I want to do is eventually manipulate the data from Visual Basic. I tried doing all this firstly via Visual Basic, then via MySQL and finally decided that seting up a database via Microsoft SQL ought to be the easiest way to go since it can easily be accessed via Visual Basic. Any help will be appreciated.
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
I have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.
I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.
I am running a script that creates a staging table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.
Is it possible to write something like:
delete from OrigTable O join DuplicateTable D on O.Key = D.key
Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.) I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there. My SQL is in the form of:
Bulk Insert [table name] From '[server][share][filename]' With (FirstRow = 2, FormatFile='FormatFile.xml')
Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error:
Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "[server][share][filename]" could not be opened. Operating system error code 5(Access is denied.).
I found this snip at
BULK INSERT (Transact-SQL)Security Account Delegation (Impersonation), which says, in part (emphasis mine):
To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation.
How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work. Later we tried constrained delegation and it still does not work.
I have verified the SPNs:
C:>setspn adsvc_sqlRegistered ServicePrincipalNames for CN=SVC_SQL,OU=Service Accounts,OU=Users,OU=ad domain,DC=ad,DC=local: MSSQLSvc/SQLQA.ad.local:1433 MSSQLSvc/SQLDev.ad.local:1433 MSSQLSvc/SQLQA.ad.local MSSQLSvc/SQLDev.ad.local I have verified that my SQL connection is TCP and I am getting/using a Kerberos security token. C:>sqlcmd -S tcp:SQLQA.ad.local,1433 -E1> Select dec.net_transport, dec.auth_scheme From sys.dm_exec_connections As dec Where session_id = @@Spid;2> gonet_transport auth_scheme------------- -----------TCP KERBEROS(1 rows affected)1>
If I move the source file to a local drive (on the SQL server), all works fine, but I must be able to read from a file share?
I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example]( col1 [varchar](10) NOT NULL, col2 [datetime] NOT NULL, col3 [date] NOT NULL, col4 [varchar](6) NOT NULL, col5 [varchar](3) NOT NULL,
The first column should store double (in col2 and col3) in my table
My file: Col1,Col2,Col3,Col4,Col5,Col6,Col7 2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS 2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command: bulk insert data.example from 'R:epoolexample.csv' WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error: Msg 4823, Level 16, State 1, Line 2 Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as: used ";" and "," as column delimiter changed file type from UNIX to DOS and adjusted the format file with " " for row delimiter
Removed this line from format file 1 SQLCHAR 0 10 "@|@" 2 Col2 "" Nothing works ....
we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
Have just tried my sql server 2005 connected asp classic website on II7 for the first time and have gotten some errors. First off I have unchecked show friendly errors in IE and have enabled ASP to send error messages to the browser.
My error I get is when I execute a basic .asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed. /index.asp, line 10 which is dbConnect.Open cstring
from this peice of code:
sub dbOpen() set dbConnect = Server.CreateObject("ADODB.Connection") cstring = "DSN=mysqldsn; Trusted Connection=yes" dbConnect.Open cstring end sub
I have gone into ODBC and have setup both a user dsn and file dsn that equals mysqldsn and points to my database (I know you only need to set up one DSN but I'm not sure which)
I also notice under mywebsite home panel there is a connection string option. Do I do anything with that?
