6
votes
How to write script to kill MS Office database locks in MSSQL
You can look at
select session_id, program_name, host_name
from sys.dm_exec_sessions
But those program names can be chosen by the client, so there's no guarantee that you can identify the Excel ...
- 47.3k
5
votes
SQL Server: SSMS "Save Results As" creates CSV that Excel mangles
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 ...
- 11.7k
5
votes
Tools for SQL Deadlock Statistics
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 ...
- 29.6k
4
votes
Accepted
Calculate sum of N preceding values
Use a window function
...sum(PL) over(order by [Date] range between 3 preceding and current row)
- 26.6k
4
votes
Problem with IF / BEGIN / ELSE
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 ...
- 29.6k
3
votes
SQL Server: SSMS "Save Results As" creates CSV that Excel mangles
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 ...
- 131
3
votes
Accepted
Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?
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 ...
- 2,244
3
votes
Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?
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 ...
- 606
3
votes
PostgreSQL: alternative ways to connect to database
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 ...
- 31.5k
3
votes
Accepted
High ASYNC_NETWORK_IO when user locks computer with Excel open
ASYNC_NETWORK_IO can cause database contention issues if the results being sent to the client never spool anywhere (e.g., via an ORDER BY clause forcing a sort in tempdb).
If this issue is happening ...
- 465
3
votes
Altering SSAS writeback behavior?
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 ...
- 15.7k
3
votes
How do you export a database schema from SQL Server Management Studio to Excel?
I needed a similar solution, but with tables and column information too.
We needed to translate a database to another language, including table and column names. This job was done by a translator that ...
- 131
3
votes
SSIS keeps force changing excel source string to float
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; ...
- 31
3
votes
SSIS ACE.OLEDB.12.0 Driver issue
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 ...
- 15.7k
3
votes
Accepted
Database name 'mydb' ignored, referencing object in tempdb. Bulk Insert
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 ...
- 70.3k
3
votes
Accepted
Foreach Loop Enumerator with multiple File Names
If I understand correctly what you are trying to do I don't think you should put your expression in the Directory property.
I think you need to put:
Your directory name (C:\NewExcelFile\) in as the ...
- 15.7k
3
votes
Extracting data from extremely large csv files
Powershell to the rescue!
A CSV file is just a text file structured in a certain way. It doesn't have to open up in Excel, that's just what your default settings are configured to do at the moment. ...
- 4,861
3
votes
Extracting data from extremely large csv files
Bulk insert to SQL Server
Who's that nutjob telling you to use Powershell?! Off topic! This is a site for DBAs, not developer nutjobs!
Use the developer edition of SQL Server you've got installed on ...
- 4,861
3
votes
Import an xlsx file to Oracle SQL Developer and interpret it as a table
No. SQL Developer is just a client that interacts with an Oracle database. It is not a database in and of itself so it has no way to store and manipulate data other than via an Oracle database.
You ...
- 20.3k
2
votes
Microsoft Access Export to Excel
To export all the tables on Linux try this command:
mdb-tables -d ',' database.mdb | xargs -L1 -d',' -I{} bash -c 'mdb-export database.mdb "$1" >"$1".csv' -- {}
You can use mdbtools also into ...
2
votes
Problem with MS Excel MDS addon - unable to connect to the web app
One of the business user in my organization ran into the same issue. The MDS 2016 URL worked from his laptop browser but the same URL from Excel throwing the same exact error message. One of my ...
2
votes
Updating SQL table from Excel sheet (using powershell)
Bulk load processes do just that - load data. They are not intended for updating existing data, or even for adding rows that don't already exist. As you surmised, the best method for those things is ...
- 11.7k
2
votes
Accepted
How to import excel to MS Access table design?
If you want to create a table using DDL (data definition language) you can use a create table statement
You don't need the start or end position. But you do need a data type and field length.
With ...
- 3,233
2
votes
Designing a database for Reading Comprehension
1st things first:
Use TEXT for all character fields including small ones! In SQLite, VARCHAR is just TEXT behind the scenes anyway (same is true for PostgreSQL if ever you want to upgrade to a multi-...
- 30.3k
2
votes
Transfer data from Excel to table
You must install the Microsoft Access Database Engine 2016, as Justin Onstot already wrote (https://www.microsoft.com/en-us/download/details.aspx?id=54920), and ALSO you MUST run the correct version ...
2
votes
Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException
Using the profiler I found, that Excel sends an EXEC sys.sp_prepare to return the meta data and compiles the execution plan.
When I executed the prepare statement in SSMS it hangs too, the same when ...
- 875
2
votes
Accepted
Extracting data from extremely large csv files
Copy into PostgreSQL
Psha... Microsoft Sheeple. PostgreSQL is the clearly superior platform for this task. None of that faffing about with downloading things and figuring out an arcane configuration ...
- 4,861
2
votes
Sometimes, not always, when loading an MSSQL table from Excel a new table suffixed with a $ is created by MSSQL. Why?
From this MSDN discussion:
Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel ...
- 1,906
Only top scored, non community-wiki answers of a minimum length are eligible