I have just had the same problem yesterday, and here is how I was able solve it:
Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio),
the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there.
After I added the key to the registry, I was able ...
Determine if "Disallow adhoc access" is enabled for your provider. This can be found in SQL Management Studio via the following navigation path:
Server Objects/Linked Servers/Providers/Microsoft.ACE.OLEDB.12.0
Right click the "Microsoft.ACE.OLEDB.12.0" provider and select "Properties" from the context menu.
In the pop-up window, make sure that the "Disallow ...
I see this question is tagged both Excel and SQL Server.
In Excel you could use the approach from here
The formula in cell F2 above is
Pay attention to the note in the linked article
The formulas in this ...
PowerShell is the way to go:
# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
# Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !
We finally resolved the issue. It turns out that SSIS calculates the length based on the first handful of rows in the excel file. When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).
A Fast Method
A probably faster way to do this is the following:
Provide the data in a database table
Execute a MERGE statement that merges the data from this table into the source table
To provide the data in a database table you can use sql loader or [external tables].
See the mydata.csv file at bottom of the answer.
I created a table xy
CREATE TABLE `xy`
`fred` int(11) DEFAULT NULL,
`mary` int(11) DEFAULT NULL,
`billy` varchar(50) DEFAULT NULL
From the documentation here, I tried this
LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script.
For this script to work, install Excel::Writer::XLSX and Text::CSV perl modules, either through apt-get/yum, or through sudo perl -MCPAN -e 'install Excel::Writer::XLSX' and sudo perl -MCPAN -e 'install Text::CSV'.
You should use the open source stored procedure sp_BlitzLock to analyze these deadlocks. It will give you what you're looking for in terms of timing, which tables / objects are involved in deadlocks most often, etc.
I downloaded your .xel file and ran the proc like this:
@EventSessionPath = 'C:\Program Files\Microsoft SQL ...
Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.
The issue was with the status expression.
Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.
I added a check at the beginning of the expression:
CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL
...which correctly leaves those fields empty.
This can easily be done through an SSIS package and scheduled with SQL Server Agent. See this link for a tutorial/demonstration on the task: http://www.codeproject.com/Articles/402958/SSIS-Package-Export-Data-from-Database-Daily-to-Ne
You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table.
In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.
The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL experience and they described the process you mention as the "ETL" process they were familiar with, it's very likely that the interview would end politely and ...
The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.
This will create a package for you, which you can schedule via SQL agent job as explained in Scheduling SSIS Package Execution in SQL Server Agent, as per the desired refresh frequency.
Also, refer to ...
While you could schedule an JET OLEDB type query as a SQL Agent job with something like the code below:
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\PathToExcelFile\ExcelFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]
I would only recommend this approach if you have not, can not or do not want ...
First, make sure you've got the query options set up the way you want.
Go to the Query menu, and choose Query Options:
The first of the two highlighted options tells SSMS to include the column headers in your CSV file. The second tells SSMS to put single quotes around columns that include a comma.
I suspect you've already done this, but thought I should ...
For something simple like what you have, I would highly recommend Google Docs or one of variants. It's basically a spreadsheet where everyone that you give permissions to can edit at the same time. Everyone can see what everyone else is doing, so there aren't many conflicts.
A SQL database would certainly support simultaneous accesses, but I think it's ...
When you do Export data from the database (using the wizard - Import\Export)
select -- write a query to specify data transfer.
If you are creating package from scratch using BIDS, then in the execute sql task editor, in the sql statement
then in the data flow task in the source query specify T-SQL query as below :
The columns that are aligned to the right are deemed as numeric fields by Microsoft Excel, anything with a letter in it is deemed an alphanumeric field.
I do not believe there is an export method in SQL Management that would have Excel change the alignment of the cells, any change in formatting would need to be done in Excel post-export.
Alternatively, if ...
Full disclosure.. I am the author of the following program.
Give it a try and let me know what you think.
I have tried many applications including the excel add-in from MySQL, but was not satisfied with them, so I wrote my own.
The current version of my program is slow (just like the MySQL add-in), doesn't support international characters, and doesn't ...
Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need.
Setup to simulate the source data:
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3));
INSERT INTO t1 VALUES ('010-041','...
You'll need to configure SSIS to run in 32-bit runtime as Excel does not support 64-bit
Go to the Property page for the Solution, select Debugging and change Run64BitRuntime to False
Running 32-bit SSIS in a 64-bit Environment
Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######.
So, that is a Excel limit. Further, you can type in strings that look like dates, but Excel may not think of them as dates. For example, if I make a column of date type and enter into the columns -53689, 01/01/1753, and 01/01/1900 (using my ...
If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below:
a.) Create a table with the same structure as your excel file
b.) Convert your Excel File to .csv format file
c.) Create a Loader Control File. Refer ...
Save as text (csv) file.
CREATE TABLE ... with the columns approximating the Excel columns.
LOAD DATA LOCAL FILE ... into that table in MySQL.
(If you need 'automation', there may be better ways.)
A poor man's solution might be simply installing pgAdminIII, setting up the connection to the database and using it's built in filtering and data view/modification functionality.
I admit this does not give you a very good user experience (about 8 clicks to get to the table you want to view/edit), but it is very easy to set up.
One important thing: you may ...