My 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 table. This might have a dramatic effect on performance, depending on table size, row count etc. but it is an option according to the BOL.
So then, what if that approval date actually resides in a different table?
Assuming that my_table has a primary key PK int, and other_table has the related column ID int, we could write a lookup function, like this:
CREATE FUNCTION dbo.fnLookup
(
@PK int
)
RETURNS varchar(1)
AS
BEGIN
DECLARE @Lookup varchar(1)
SELECT @Lookup =
CASE
WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
FROM other_table
WHERE ID = @PK
RETURN @Lookup
END
...and then use that function in our computed column, like this:
ALTER TABLE my_table
ADD A_display
AS
dbo.fnLookup(PK)
Again, this could have an impact on the performance - any query run against my_table will execute the function for every row - but this is one more option for computed columns.
A better solution might be to create a column in my_table to hold the A_display data, and then create a trigger on other_table that would keep A_display in sync. We'll explore that in a future post.
Sunday, May 15, 2011
Saturday, May 07, 2011
T-SQL Computed Columns, part 2
My 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 as
NB Austin Blvd @ Cermak Rd
SB Austin Blvd @ Cermak Rd
EB Cermak Rd @ Austin Blvd
WB Cermak Rd @ Austin Blvd
If you examine the list, you see that all four locations are actually the same street intersection - Austin and Cermak - and in the application (MS-Access) we need the ability to sort by the intersection, displayed in a format like street @ cross_street.
The original app used an expression in the query to place the streets in alphabetical order, like this:
IIf([Street] > [Cross_Street],
[Cross_Street] & " @ " & [Street],
[Street] & " @ " & [Cross_Street])
...and the result for all four locations would be:
Austin Blvd @ Cermak Rd
which provides the right answer & works fine, but takes a lot of typing, so it was eventually converted to a VBA function.
Now that the data has been moved into SQL Server, we can avoid using IIf() or VBA functions, which use resources in the GUI, and instead take advantage of the power of T-SQL.
We can start by creating a computed column like this:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
...which gives the correct result; however, Street and Cross_Street are both nvarchar(255) so our calculated column comes out as nvarchar(513) - why? it is the sum of the sizes of the two columns, plus the ' @ ' expression - and MS-Access regards that as a Memo field, so we cannot sort by that.
(Street and Cross_Street were originally defined as Text(255) in MS-Access, but the data is never that long - 100 would have been a better choice; but that's a topic for another day.)
We could force the computed column to nvarchar(255) to accommodate the limitations of MS-Access, like this:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
AS nvarchar(255)
)
...but now we have a potential bug - we don't expect this will ever happen - but - if the concatenation is ever more than 255 characters, the CAST will fail. The solution is to shorten the street names to 126 characters, so that 126 + 3 + 126 = 255 to ensure the computed column will never fail:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN LEFT(Cross_Street, 126) + ' @ ' + LEFT(Street, 126)
ELSE LEFT(Street, 126) + ' @ ' + LEFT(Cross_Street, 126)
END
AS nvarchar(255)
)
One final note - when you work with computed columns, it is not possible to ALTER a computed column; in order to change it you must DROP the column & then ADD the new column definition. As a result, the column to move to the last position in the table.
I have a table of locations in the city - each location has a direction, street, and cross street, such as
NB Austin Blvd @ Cermak Rd
SB Austin Blvd @ Cermak Rd
EB Cermak Rd @ Austin Blvd
WB Cermak Rd @ Austin Blvd
If you examine the list, you see that all four locations are actually the same street intersection - Austin and Cermak - and in the application (MS-Access) we need the ability to sort by the intersection, displayed in a format like street @ cross_street.
The original app used an expression in the query to place the streets in alphabetical order, like this:
IIf([Street] > [Cross_Street],
[Cross_Street] & " @ " & [Street],
[Street] & " @ " & [Cross_Street])
...and the result for all four locations would be:
Austin Blvd @ Cermak Rd
which provides the right answer & works fine, but takes a lot of typing, so it was eventually converted to a VBA function.
Now that the data has been moved into SQL Server, we can avoid using IIf() or VBA functions, which use resources in the GUI, and instead take advantage of the power of T-SQL.
We can start by creating a computed column like this:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
...which gives the correct result; however, Street and Cross_Street are both nvarchar(255) so our calculated column comes out as nvarchar(513) - why? it is the sum of the sizes of the two columns, plus the ' @ ' expression - and MS-Access regards that as a Memo field, so we cannot sort by that.
(Street and Cross_Street were originally defined as Text(255) in MS-Access, but the data is never that long - 100 would have been a better choice; but that's a topic for another day.)
We could force the computed column to nvarchar(255) to accommodate the limitations of MS-Access, like this:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
AS nvarchar(255)
)
...but now we have a potential bug - we don't expect this will ever happen - but - if the concatenation is ever more than 255 characters, the CAST will fail. The solution is to shorten the street names to 126 characters, so that 126 + 3 + 126 = 255 to ensure the computed column will never fail:
ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN LEFT(Cross_Street, 126) + ' @ ' + LEFT(Street, 126)
ELSE LEFT(Street, 126) + ' @ ' + LEFT(Cross_Street, 126)
END
AS nvarchar(255)
)
One final note - when you work with computed columns, it is not possible to ALTER a computed column; in order to change it you must DROP the column & then ADD the new column definition. As a result, the column to move to the last position in the table.
Saturday, April 23, 2011
T-SQL Computed Columns
A 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 continuous form. I suspected that an IIf() statement was the culprit.
The IIf() statement drives a control that displays "A" if the record has an approval date, like this:
=IIf(IsNull([approval_date]),"","A")
I created a computed column in the SQL table using this statement:
ALTER TABLE my_table
ADD A_display
AS
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
...and then in the application, I replaced that IIf() statement with the new column A_display; thus duplicating the Access statement. This did speed up the query quite a bit.
Now, looking back at the modified table, I can see that A_display shows as
varchar(1) NOT NULL
...which is fine for my purpose. But you can modify that behavior by adding to the statement. For example, we might want the output to be nullable - so if you set the control's background property to Transparent (instead of Normal) then you can see through it, if there's no approval date.
To make the column nullable, we could just change the original logic like this:
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE NULL
END
...but if your statement is more complex, you might use NULLIF:
NULLIF(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
,'')
You can also control the data type and size by using CAST:
CAST(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE
NULL
END AS nvarchar(1)
)
which gives you: nvarchar(1) NULL
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 continuous form. I suspected that an IIf() statement was the culprit.
The IIf() statement drives a control that displays "A" if the record has an approval date, like this:
=IIf(IsNull([approval_date]),"","A")
I created a computed column in the SQL table using this statement:
ALTER TABLE my_table
ADD A_display
AS
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
...and then in the application, I replaced that IIf() statement with the new column A_display; thus duplicating the Access statement. This did speed up the query quite a bit.
Now, looking back at the modified table, I can see that A_display shows as
varchar(1) NOT NULL
...which is fine for my purpose. But you can modify that behavior by adding to the statement. For example, we might want the output to be nullable - so if you set the control's background property to Transparent (instead of Normal) then you can see through it, if there's no approval date.
To make the column nullable, we could just change the original logic like this:
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE NULL
END
...but if your statement is more complex, you might use NULLIF:
NULLIF(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
,'')
You can also control the data type and size by using CAST:
CAST(
CASE WHEN approval_date IS NOT NULL
THEN 'A'
ELSE
NULL
END AS nvarchar(1)
)
which gives you: nvarchar(1) NULL
Friday, January 28, 2011
sp_executesql
The 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 for Phoenix AZ you could just write
SELECT County
FROM Cities
WHERE City = 'Phoenix'
AND ST = 'AZ'
To make this more versatile, you could write a stored procedure like this
CREATE PROC Get_County
(
@City nvarchar(50),
@ST nvarchar(2)
)
AS
SELECT County
FROM Cities
WHERE City = @City
AND ST = @ST
GO
However, pulling data from from a stored procedure might require that you create a temp table and then use the INSERT...EXEC syntax, which does work, but nesting of sp's is limited.
If we turn this into dynamic sql, we can feed in different parameters. It does seem a bit complicated but it is flexible once you get it set up...
DECLARE
@County nvarchar(50),
@County_out nvarchar(50),
@City nvarchar(50),
@ST nvarchar(2),
@sql nvarchar(4000)
SET @sql = 'SELECT @County_out = County FROM Cities WHERE City = @City AND ST = @ST'
EXEC sp_executesql
@sql,
N'@City nvarchar(50), @ST nvarchar(2), @County_out nvarchar(50) OUTPUT',
@City = 'Phoenix',
@ST = 'AZ',
@County_out = @County OUTPUT
SELECT @County AS County
So the steps are:
+ declare all of your input and output variables
+ declare a variable that's used locally for the lookup value
+ 1st argument = the sql string
+ 2nd argument = comma-separated list to declare of all of the in/out parameters
+ next argument(s) = input parameter(s)
+ last argument = output parameter
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 for Phoenix AZ you could just write
SELECT County
FROM Cities
WHERE City = 'Phoenix'
AND ST = 'AZ'
To make this more versatile, you could write a stored procedure like this
CREATE PROC Get_County
(
@City nvarchar(50),
@ST nvarchar(2)
)
AS
SELECT County
FROM Cities
WHERE City = @City
AND ST = @ST
GO
However, pulling data from from a stored procedure might require that you create a temp table and then use the INSERT...EXEC syntax, which does work, but nesting of sp's is limited.
If we turn this into dynamic sql, we can feed in different parameters. It does seem a bit complicated but it is flexible once you get it set up...
DECLARE
@County nvarchar(50),
@County_out nvarchar(50),
@City nvarchar(50),
@ST nvarchar(2),
@sql nvarchar(4000)
SET @sql = 'SELECT @County_out = County FROM Cities WHERE City = @City AND ST = @ST'
EXEC sp_executesql
@sql,
N'@City nvarchar(50), @ST nvarchar(2), @County_out nvarchar(50) OUTPUT',
@City = 'Phoenix',
@ST = 'AZ',
@County_out = @County OUTPUT
SELECT @County AS County
So the steps are:
+ declare all of your input and output variables
+ declare a variable that's used locally for the lookup value
+ 1st argument = the sql string
+ 2nd argument = comma-separated list to declare of all of the in/out parameters
+ next argument(s) = input parameter(s)
+ last argument = output parameter
Saturday, December 18, 2010
SSRS and CSV format
Using 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 rsreportserver.config where you can add or modify the export formats. The config file is an xml file which in my case was located at
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
(note: the wysiwyg on blogger has issues with angle brackets, so I am showing square brackets below)
Locate the section [Render] and you will find entries for all the formats; find the line that begins with [Extension Name = "CSV" and add the Configuration, Device Info, and Encoding:
[Render]
[Extension Name="CSV" Type=" ..."]
[Configuration]
[DeviceInfo]
[Encoding]ASCII[/Encoding]
[/DeviceInfo]
[/Configuration]
[/Extension]
[/Render]
Note: for the "Extension Name" entry, the default config file has a closing slash "/" that has to be removed for this to work.
After making this change, just to be safe I stopped and re-started IIS. After which it worked just fine, delivering an ASCII-encoded file that could be opening with Excel 2003 and imported into Salesforce.
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 rsreportserver.config where you can add or modify the export formats. The config file is an xml file which in my case was located at
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
(note: the wysiwyg on blogger has issues with angle brackets, so I am showing square brackets below)
Locate the section [Render] and you will find entries for all the formats; find the line that begins with [Extension Name = "CSV" and add the Configuration, Device Info, and Encoding:
[Render]
[Extension Name="CSV" Type=" ..."]
[Configuration]
[DeviceInfo]
[Encoding]ASCII[/Encoding]
[/DeviceInfo]
[/Configuration]
[/Extension]
[/Render]
Note: for the "Extension Name" entry, the default config file has a closing slash "/" that has to be removed for this to work.
After making this change, just to be safe I stopped and re-started IIS. After which it worked just fine, delivering an ASCII-encoded file that could be opening with Excel 2003 and imported into Salesforce.
Saturday, August 28, 2010
Resize query columns
My 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 Google'd for a solution and found the article:
You Can Do That with Datasheets?
and the same code is presented here:
Resizing column to best fit vba
...and the answer relies on two important concepts:
The code was a little wordy so I distilled it into the following:
Sub resize_query(query_name As String)
Dim frm As Form, ctl As Control
'
DoCmd.OpenQuery query_name, acViewNormal
Set frm = Screen.ActiveDatasheet
For Each ctl In frm.Controls
ctl.ColumnWidth = -2
Next ctl
DoCmd.Close acQuery, query_name, acSaveYes
End Sub
Now, if you have a Forms parameter in the query, you will need to have the corresponding form open when you run this code. Otherwise it will prompt you for the parameter(s).
EDIT on August 29:
I have been testing this code on a variety of queries and found that it does not work 100% of the time. Thinking that I might have left out something important, I returned to the original article mentioned above, and it also does not work 100% of the time. Will continue to test this out & hopefully post the final solution.
I Google'd for a solution and found the article:
You Can Do That with Datasheets?
and the same code is presented here:
Resizing column to best fit vba
...and the answer relies on two important concepts:
- When you open a form in datasheet view, if you set ColumnWidth = -2 then Access will resize the columns to their proper widths.
- You can open a table or query in datasheet view, and then Dim and Set a Form object to it.
The code was a little wordy so I distilled it into the following:
Sub resize_query(query_name As String)
Dim frm As Form, ctl As Control
'
DoCmd.OpenQuery query_name, acViewNormal
Set frm = Screen.ActiveDatasheet
For Each ctl In frm.Controls
ctl.ColumnWidth = -2
Next ctl
DoCmd.Close acQuery, query_name, acSaveYes
End Sub
Now, if you have a Forms parameter in the query, you will need to have the corresponding form open when you run this code. Otherwise it will prompt you for the parameter(s).
EDIT on August 29:
I have been testing this code on a variety of queries and found that it does not work 100% of the time. Thinking that I might have left out something important, I returned to the original article mentioned above, and it also does not work 100% of the time. Will continue to test this out & hopefully post the final solution.
Thursday, August 19, 2010
Stored procedure text
SQL 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 by Pinal Dave SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object which mentions sp_helptext, but you need to know the name of the sp in order to find it; catch-22.
So I opened up sp_helptext and found it was using sys.syscomments to get the sql text; from there it was easy to write:
SELECT OBJECT_NAME(id), [text]
FROM sys.syscomments
WHERE [text] LIKE '%BULK INSERT%'
...which in my case returned 7 rows. Eureka!
It is important to note that [text] is nvarchar(4000) so in the event that your sql text is larger than that, there will be multiple rows for that item, and the order is found in the column colid. So then, to get the complete text for an sp named "my_sp" you would use this:
SELECT object_name(id), colid, [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'my_sp'
ORDER BY 2
Now, sys.syscomments view does not have an identifier for the type of object, so if you normally use prefixes when naming your objects e.g. viw for views, trg for triggers etc. that will make it much easier to locate the desired sql text.
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 by Pinal Dave SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object which mentions sp_helptext, but you need to know the name of the sp in order to find it; catch-22.
So I opened up sp_helptext and found it was using sys.syscomments to get the sql text; from there it was easy to write:
SELECT OBJECT_NAME(id), [text]
FROM sys.syscomments
WHERE [text] LIKE '%BULK INSERT%'
...which in my case returned 7 rows. Eureka!
It is important to note that [text] is nvarchar(4000) so in the event that your sql text is larger than that, there will be multiple rows for that item, and the order is found in the column colid. So then, to get the complete text for an sp named "my_sp" you would use this:
SELECT object_name(id), colid, [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = 'my_sp'
ORDER BY 2
Now, sys.syscomments view does not have an identifier for the type of object, so if you normally use prefixes when naming your objects e.g. viw for views, trg for triggers etc. that will make it much easier to locate the desired sql text.
Drop temp table
By 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 some type of error, the temp table will remain; if you re-run the batch you will get a message that #temp already exists, and you need to drop the temp table before you can run it again.
You can include a statement like this at the beginning of your batch, to prevent that from happening:
IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #temp
However, when you are developing a stored procedure, and the batch fails due to some type of error, the temp table will remain; if you re-run the batch you will get a message that #temp already exists, and you need to drop the temp table before you can run it again.
You can include a statement like this at the beginning of your batch, to prevent that from happening:
IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL
DROP TABLE #temp
Wednesday, June 02, 2010
ODBC without a DSN
For 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 the network which makes me nervous.
I had read about "DSN-less" connections but never explored that option until I used the SQL Server Data Migration Assistant (SSMA) to migrate the data from an Access database into SQL Server. After reviewing the wizard output. this approach is definitely easy to implement using a connection string.
Now, connection strings can also be scary; writing one from scratch can be a challenge because of so many options. But if we narrow the focus to MS-Access 2000-2003 connecting to SQL 2005, after you have one actually working you can easily modify it.
The basic connection string looks like the following. I am using a Global Constant My_Connection, and I've broken out each portion for clarity, but the actual string should be all on one line:
Global Const My_Connection =
"ODBC;
DRIVER=SQL Native Client;
SERVER=my_server;
UID=my_username;
PWD=my_password;
APP=my_appname;
WSID=my_workstation;
DATABASE=my_database;"
There is some code, not shown here, which refers to the My_Connection string & then loops thru the tables to update the connections.
The approach works just fine if you are using SQL Server Authentication. We decided that this would be the easiest to manage since we don't have to maintain all the various windows ID's; plus, when we grant permissions we can simply say "GRANT SELECT ON dbo.my_tablename TO my_username". The application calls a Windows API to get the network ID and this controls who gets to do what within the app itself.
Now, if you have multiple SQL instances installed, e.g. if the development instance is called DEV you only need to change SERVER = my_server\DEV and it works fine; just remember to also change the my_username and my_password since those are usually different for the production servers.
One more little trick - if you are working remotely using a VPN connection, because Windows uses nslookup to find the IP address for the server name, this can cause a timeout & prevent you from connecting. The solution here is to replace the my_server_name with the actual IP address, e.g. if your server name is my_server and the IP adress is 192.168.1.2 then you can substitute that IP address for my_server. For a named instance it would be 192.168.1.2\DEV.
In our case, each of the possible connection strings to the various production, QA, DEV, and local testing database are written in plain text in a standard module; so after we run the "relink code" we delete all those strings and add a line Global Const My_Connection = "" so all the sensitive link data is removed, being replaced by a do-nothing statement that only exists to prevent compile errors.
I had read about "DSN-less" connections but never explored that option until I used the SQL Server Data Migration Assistant (SSMA) to migrate the data from an Access database into SQL Server. After reviewing the wizard output. this approach is definitely easy to implement using a connection string.
Now, connection strings can also be scary; writing one from scratch can be a challenge because of so many options. But if we narrow the focus to MS-Access 2000-2003 connecting to SQL 2005, after you have one actually working you can easily modify it.
The basic connection string looks like the following. I am using a Global Constant My_Connection, and I've broken out each portion for clarity, but the actual string should be all on one line:
Global Const My_Connection =
"ODBC;
DRIVER=SQL Native Client;
SERVER=my_server;
UID=my_username;
PWD=my_password;
APP=my_appname;
WSID=my_workstation;
DATABASE=my_database;"
There is some code, not shown here, which refers to the My_Connection string & then loops thru the tables to update the connections.
The approach works just fine if you are using SQL Server Authentication. We decided that this would be the easiest to manage since we don't have to maintain all the various windows ID's; plus, when we grant permissions we can simply say "GRANT SELECT ON dbo.my_tablename TO my_username". The application calls a Windows API to get the network ID and this controls who gets to do what within the app itself.
Now, if you have multiple SQL instances installed, e.g. if the development instance is called DEV you only need to change SERVER = my_server\DEV and it works fine; just remember to also change the my_username and my_password since those are usually different for the production servers.
One more little trick - if you are working remotely using a VPN connection, because Windows uses nslookup to find the IP address for the server name, this can cause a timeout & prevent you from connecting. The solution here is to replace the my_server_name with the actual IP address, e.g. if your server name is my_server and the IP adress is 192.168.1.2 then you can substitute that IP address for my_server. For a named instance it would be 192.168.1.2\DEV.
In our case, each of the possible connection strings to the various production, QA, DEV, and local testing database are written in plain text in a standard module; so after we run the "relink code" we delete all those strings and add a line Global Const My_Connection = "" so all the sensitive link data is removed, being replaced by a do-nothing statement that only exists to prevent compile errors.
Sunday, April 04, 2010
Office 2010, part 3
In the Access 2010 Beta, if you open a file from an Explorer window or a shortcut, you get the dreaded
The 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 within Access you don't get that message.
So then, to prevent that message all you have to do is create a shortcut that launches Access and opens the file, like this (watch for line breaks):
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" C:\mydatabase.mdb
The 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 within Access you don't get that message.
So then, to prevent that message all you have to do is create a shortcut that launches Access and opens the file, like this (watch for line breaks):
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" C:\mydatabase.mdb
Subscribe to:
Posts (Atom)