Hot answers tagged

11

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 ...


8

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 ...


7

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 =IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match") Pay attention to the note in the linked article The formulas in this ...


6

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]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


6

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script. csv2xlsx: 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'. #!/usr/bin/perl -w ...


6

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 ! # http://www.vistax64.com/powershell/...


5

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' ...


5

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: EXEC master.dbo.sp_BlitzLock @EventSessionPath = 'C:\Program Files\Microsoft SQL ...


4

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.


4

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 ...


4

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 ...


4

While you could schedule an JET OLEDB type query as a SQL Agent job with something like the code below: SELECT * 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 ...


4

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 ...


4

Use a window function ...sum(PL) over(order by [Date] range between 3 preceding and current row)


4

I don't have an easy way to test this theory right now, but I'd bet that there are some validation checks of the OPENROWSET parameters going on when the batch compiles (the whole batch, regardless of IF / ELSE and whether or not those blocks eventually get executed). Whenever I encounter this problem, using dynamic SQL is usually a solution. Give that a ...


3

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 ...


3

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


3

Step 1: Add IMEX=1; MAXROWSTOSCAN=0 in the connections string (e.g. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Source\filename.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1; MAXROWSTOSCAN=0"). Step 2: Close the solution. Open the SSIS package in text editor, find the columns that kept reversing back to the pre defined data type. Update it ...


3

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 ...


3

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 ...


3

For 1-time: 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.)


3

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 ...


3

Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is stored). Have a look at the writeback tables in your data source and you'll see it contains fields for all your measures, and the key of all your dimensions, so ...


3

Like MguerraTorres suggests, SSRS has probably got confused somewhere, and the value suggests it's getting cast to some kind of floating point datatype somewhere along the line. Try checking the datatype SSRS is assigning by looking at the xml in the .rdl file raw text. for a simple bigint column I'm seeing an SSRS field definition something like this: &...


3

OHHH Man I completely misread your initial question. This is 100% an excel issue. You NEED to convert that value to text/string before you export it to Excel. You will get the same exact issue if you try this experiment: Query the procedure via SSMS and display the results in a grid Copy the results and paste them directly into Excel. Noting this ...


3

I was having same trouble with field and limiting the length of the output didn't help at all because it has to do with the carriage returns. Before you run the data you want output to Excel, change your query on those free text fields to deal with those returns: SELECT REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), ''), ... Once I did that, Excel ...


3

A common cause of this problem can be the difference between 32 bit and 64 bit installations. If your SSIS package is running in 64 bit the Access driver needs to be installed in 32 bit too. My guess would be that on your machine you installed the 64 bit driver, but on the server the 32 bit driver has been installed. You could test this by executing the ...


3

You cannot specify a database name, or schema name, when using a #temporary table. Remove the # from the name of the stats_ddl table, and you'll see it works. If you want to insert the rows into a temporary table, then remove the reference to the database name: IF OBJECT_ID('tempdb..#stats_ddl') is not null DROP TABLE #stats_ddl GO CREATE TABLE #...


Only top voted, non community-wiki answers of a minimum length are eligible