tag:blogger.com,1999:blog-102217352024-02-08T11:25:27.250-07:00Mitchell Data BaseA forum created 01/17/2005 to facilitate communication between database developers and users.Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.comBlogger92125tag:blogger.com,1999:blog-10221735.post-22692082708085910742018-09-07T13:00:00.001-07:002018-09-07T13:00:32.935-07:00Replacement for SendKeys in VBAWe have an MS-Access 2016 application that runs on a terminal server which we are upgrading from 2008 R2 to 2012. The prelim testing was done on Windows 10 which seemed equivalent, but not so much.
One feature of the app is an auto-test button, which uses VBA to open forms, click buttons, and make selections from drop-down lists.
The portion of the code for the drop-down lists uses the built-inBill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com1tag:blogger.com,1999:blog-10221735.post-76693469313249877032017-07-07T13:56:00.000-07:002017-07-07T13:56:46.049-07:00Alter Table Drop ColumnIf you've worked with SQL Server for a while, you're familiar with the syntax:
ALTER TABLE table_name
DROP COLUMN column_name
Easy enough, right? But if you have a default constraint on that column, you need to drop the constraint before you can drop the column.
If you originally created the constraint & specified the name, something like this:
ALTER TABLE table_name
ADD column_name bit NOT Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-86886419452513509192015-11-14T10:38:00.000-07:002015-11-14T10:44:21.727-07:00SSIS imports Excel with some blank columnsI am working with SSIS to import an Excel file into SQL Server. Not being an SSIS expert, I usually use the import wizard in SSMS which lets me create the destination table, load the data, and save the package to the file system. Then I will review the wizard-created table, modify any data types as needed, and then generate a proper CREATE TABLE statement.
In some cases however, the wizard does Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-85165931615047606492015-10-30T13:08:00.002-07:002015-10-30T13:08:56.862-07:00SSMS Utility ExplorerOne of the features of SSMS 2008R2 is the Utility Explorer window, which "connects to Database Engine instances to provide a tree view of all the objects in the SQL Server Utility." (https://msdn.microsoft.com/en-us/library/ee210556%28v=sql.105%29.aspx)
This feature is available in the Enterprise & Developer Editions.
Out of curiosity, I opened that window to check it out, and then closed the Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-25630824135400874252015-10-21T17:49:00.002-07:002015-10-21T17:49:55.855-07:00TortoiseSVN and SSMS icon overlaysTortoise SVN is one of the popular source-control systems, which we use at our office. If you've used this product, when you commit you'll sometimes see a message that a new version is available; I had 1.8.x installed, so this time I did the update but - got bit by version 1.9.2 ...
This is using a recent Windows 7 x64 machine, by the way.
After the update, the Windows folders all had the Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-73269555668324957722015-01-09T15:33:00.002-07:002015-01-09T15:58:20.406-07:00sp_who2 with sql textSQL Server has an undocumented stored procedure sp_who2 that provides information about the connections to the database.
Recently I have been using sp_who2 to track down connections that are "suspended" which in my case means that our MS-Access application is not processing all of the requested data in a timely manner. This can interfere with other users, even though there is no actual blocking Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-25573800143478116862014-08-17T07:20:00.000-07:002014-08-17T07:20:44.158-07:00T-SQL Select SchemaLet's say that you have the same table that exists in two different schemas -
A.my_table
B.my_table
...and you want to write a query with a parameter so you can select from one schema or the other. Without resorting to dynamic SQL.
One possible solution is to write a UNION query that will deliver the data from one schema or the other, like this:
DECLARE @schema sysname
SET @schema = 'A'
Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-47998775468897230832012-10-20T18:20:00.000-07:002012-12-06T05:08:15.783-07:00Programming Tab ControlsMS-Access tab controls offer a method to present multiple groups of similar data, making it easy for user to locate specifics within an intuitive & user-friendly layout - very much like an Excel workbook. The main control is of course called a Tab Control, and the tabs within it are called Pages.
For example, you might have a main form where you select a customer, and then you have a tab controlBill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-60647142808783568382012-09-09T10:00:00.000-07:002012-10-20T18:26:10.924-07:00SQL - list columns in a table, updated, againThe code has been updated again, this time instead of using sp_columns we are using the system tables directly. This provides access to additional data, such as whether the column is computed. Plus, in the event that Microsoft changes sp_columns - that would not break our code.
One significant change since the previous January 2012 version is that option 4 now provides a skeleton for an insert Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-82024681076582105232012-01-12T20:56:00.008-07:002012-01-12T22:52:07.720-07:00SQL - list columns in a table, updatedWay back in April 2009, I had published the original version of my stored procedure wm_columns which can be used instead of sp_help or sp_columns to view a list of column names for a table or view.The new version has a number of enhancements, and you can view the complete code here.The first parameter is required, which is the name of a table or view, such as:wm_columns 'my_table'...and the Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com2tag:blogger.com,1999:blog-10221735.post-79124510212537767162011-06-30T05:26:00.014-07:002011-07-01T21:48:16.878-07:00SQL Timestamp and AccessThe timestamp field in a SQL Server table holds a unique value that is updated every time a record is updated. In a recent post in a SqlServerCentral forum, there was a suggestion that when you are using MS-Access as the front-end, you should add a timestamp column to every linked table - and thus prevent write-conflict messages.If you use the SQL Server Migration Assistant (SSMA) to convert yourBill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com1tag:blogger.com,1999:blog-10221735.post-67464490875823667182011-05-15T07:59:00.019-07:002011-05-15T08:45:33.413-07:00T-SQL Computed Columns, part 3My last two posts described how to create a computed column, and how to control the data type and size.In the first example, the computed column would be "A" if there is an approval date, otherwise it would be "".The general rule is that the computed column can only refer to other columns within the same table. However, it is possible to use a scalar function to perform a lookup from another Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-76958708458864268212011-05-07T07:49:00.012-07:002011-05-15T07:53:26.131-07:00T-SQL Computed Columns, part 2My previous post presented the basics of using computed columns in SQL Server. Here we have a slightly more complex example that also deals with data types and sizes.I have a table of locations in the city - each location has a direction, street, and cross street, such asNB Austin Blvd @ Cermak RdSB Austin Blvd @ Cermak RdEB Cermak Rd @ Austin BlvdWB Cermak Rd @ Austin BlvdIf you examine the listBill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-39103525378268990842011-04-23T06:53:00.008-07:002011-04-23T07:39:07.305-07:00T-SQL Computed ColumnsA Computed Column in SQL Server allows you to create a read-only calculated field that performs some logic but occupies no space in the database.We have an Access front-end with a SQL Server database & I was looking for ways to improve the user experience. I noted that the query behind one of the forms opened "OK" but scrolling thru the records was a little sluggish. The form itself is a Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-3403135544046608652011-01-28T22:19:00.012-07:002011-01-28T23:04:04.039-07:00sp_executesqlThe system stored procedure sp_executesql can be used to run dynamic sql. It also provides a way to use input and/or output parameters. However, the BOL examples seemed to be mighty complicated so I put this together for a quick reference.For the example, we have a simple table "Cities" like this:City nvarchar(50),ST nvarchar(2),County nvarchar(50)If all you want to do is to look up the County Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-38624844095372392062010-12-18T23:24:00.003-07:002010-12-19T00:03:04.258-07:00SSRS and CSV formatUsing SQL Server 2005 Reporting Services, I created a timed subscription to save a report as a CSV file to a Windows file share. However, the user was unable to import the file into their salesforce.com application.Turns out that the default export format for CSV uses Unicode encoding, so if you open the file with Excel, each row of the data ends up in the first column.Luckily SSRS has a file Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-43211356062548439352010-08-28T10:06:00.009-07:002010-08-29T11:44:03.136-07:00Resize query columnsMy MS-Access application has several buttons that display a query in datasheet view, so users can sort, filter or export the results. I'm frequently disappointed in the appearance when the columns are either too narrow or wide; of course you can easily resize columns with a double-click, but Access doesn't work the same as Excel, i.e. Access considers only the visible rows when it resizes.I Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com5tag:blogger.com,1999:blog-10221735.post-44149818681816429562010-08-19T04:56:00.005-07:002010-08-19T05:27:21.092-07:00Stored procedure textSQL Server has a system view sys.syscomments that holds the sql text for all of your stored procedures, views, triggers, UDF's, default constraints and computed columns.I was considering using BULK INSERT to import a file but hadn't used it for a couple years, so I wanted to search some old scripts to get the basic syntax. I Google'd "SQL Server stored procedure text" and found a short article byBill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-32596184330941142972010-08-19T04:33:00.002-07:002010-08-19T04:50:29.103-07:00Drop temp tableBy definition, when a batch completes in SQL Server, any local temp tables created are automatically dropped. So then, if your stored procedure creates a local temp table such as #temp it will be dropped when the sp completes, and you don't need to include the statement DROP TABLE #temp to release resources in tempdb.However, when you are developing a stored procedure, and the batch fails due to Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-20084038056978137702010-06-02T01:06:00.022-07:002010-06-02T02:43:22.228-07:00ODBC without a DSNFor years, I have been using MS-Access to connect to SQL Server databases using DSN's, and the process has always been painful & frustrating: do you create a User, System, or File DSN? Each has its advantages, but User / System requires a visit to each PC to config the DSN; In some cases having to log in with Admin credentials to make that happen. And a File DSN sits out there somewhere on Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-80470019690828293472010-04-04T06:38:00.004-07:002010-04-04T06:51:31.063-07:00Office 2010, part 3In the Access 2010 Beta, if you open a file from an Explorer window or a shortcut, you get the dreadedThe command or action '' isn't available now.But the app works perfectly after dismissing that message. This one had me stumped; tried decompile, compile & save, compact & repair; nothing fixed it. Luckily a few minutes of Googling revealed the answer: if you open that same file from Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-17139161101463719542010-04-04T06:03:00.004-07:002010-04-04T06:22:03.225-07:00Office 2010, part 2Today I decided to resolve the Access calendar issue, which is: I have a popup form that uses the Calendar 2.0 control, but Access 2010 does not support ActiveX controls. Yes, I know that starting with version 2007, Access provides a date picker icon that displays wherever you have a text box that has a date format (you can turn that off), but navigating is more cumbersome compared to the old Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-56186115856748680812010-02-06T07:51:00.011-07:002010-02-06T08:30:29.264-07:00Scripted Mass UpdatesThis article describes a method to generate scripts to perform mass-updates of data. The purpose is to document the updates that are done, for future reference.Let's say you have a table of customers, and each is assigned to a region based on the state being east or west of the Mississippi River. Therefore, Illinois is assigned to the East. However, in order to balance the workload, the users Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-53600405873843595322010-01-25T04:58:00.002-07:002010-01-25T05:36:41.147-07:00Office 2010, part 1I have been working with the Office 2010 Beta for about a week now. I downloaded the Professional Plus version on January 17 and installed on my test PC which is an old Pentium III with 512 RAM running Windows Server 2003. The install was quite similar to the Office 2007 install.The program launches faster than 2007. Gone is the Office Button, in favor of a File tab on the ribbon which brings up Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0tag:blogger.com,1999:blog-10221735.post-42844810718530078982009-11-09T05:19:00.003-07:002009-11-09T06:16:19.634-07:00SQL Data ScriptsA standard practice in SQL Server development is to make any changes to objects or data using a script and apply it to the DEV system; once verified, it is applied to QA; once verified, it is finally applied to production.You can take this one step further, and write your scripts to be deterministic - i.e. if the script is run multiple times, the outcome will be the same.For example, let's say Bill Mitchellhttp://www.blogger.com/profile/04373828339706140353noreply@blogger.com0