You may need to create a proxy account & have the SSIS run under that account. The proxy account would need permission to access the particular folders / files.

Here's a basic idea that works with the sample data that was supplied...

CREATE FUNCTION dbo.fnOrders

(

@oid int

)

RETURNS nvarchar(4000)

AS BEGIN

DECLARE @orders_out nvarchar(4000)

SELECT @orders_out = ISNULL(@orders_out,'') + ISNULL(CAST(itemid AS nvarchar),'') + ';' FROM...

If you are using an ODBC connection make sure the provider is the "SQL Native Client" version 9.x for 2005, and not "SQL Server" which is the old 2000 version....

Try this:

Val(Right([Note], Len([Note]) - InStr(1, [Note], "NIBP=") - 4))

returns the answer as a number.

Here are a couple of ideas:

1. Instead of importing the data, create a linked table from Access to Excel.

2. Import the data into a staging table that has all the...

We did a conversion about 2 years ago, the original Access file with data + forms etc. was 140 MB, the resulting SQL db was around 280. Now two years...

I had a similar problem exporting a CSV file from SSRS, where Excel would open the file as a single column. Turned out that the default SSRS export encoding for...

You might need to swap if you have a pair of columns such as...

CREATE TABLE #temp

(

[name] nvarchar(128),

[rows] char(11),

...

You make a good point. If you use a DSN and/or Windows authentication, that provides an easy way for the user to get in.

We have the Access 2003 runtime installed...

In my world it is the job of the developer to provide the DBA with a single script file. The developer prefixes their script files with 01_, 02_ etc. to...

Ozzy,

You need to create & set three startup properties for the Access database:

StartupShowDBWindow = False

AllowSpecialKeys = False

AllowBypassKey = False

this will prevent the user from accessing the database window & editing...

SELECT IDENT_CURRENT('my_table')

Francis,

Your sample has 8 lines of text with line-breaks.

There are 51 occurrences of "ZB5|"

Do you want the output to be 51 lines?

The same logic should work for either one:

Function find_identity_or_autonumber(my_table As String) As String

Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field

Set...