Linking a number with a month name

Hi,
I have a table with a field called Period whcih has numbers from 1 - 12. Now
I want to say that if the value of the field is 1 , then the report should
display january, if its 2 , Feburary and so on... Is there a way this can be
done without having to create a table ?
--
pmudYou can change your query to add column for month name
Oracle =Monthname(Extract Month from tempdate)
SQL server=DayPart("m",tempdate)
hope it helps.
"pmud" wrote:
> Hi,
> I have a table with a field called Period whcih has numbers from 1 - 12. Now
> I want to say that if the value of the field is 1 , then the report should
> display january, if its 2 , Feburary and so on... Is there a way this can be
> done without having to create a table ?
> --
> pmud|||Go to the Code tab of the Reports Properties dialog and add the following:
function MonthValue(byval MonthInt as integer) as string
select case MonthInt
case 1
return "Jan"
case 2
return "Feb"
'...
case else
return "Bad Month"
End select
end function
Then in the text box where you want the month to display place this
expression:
=Code.MonthValue( Fields!Period.Value)
You can read more about adding code by searching the help for 'Writing
Custom Code'
"pmud" wrote:
> Hi,
> I have a table with a field called Period whcih has numbers from 1 - 12. Now
> I want to say that if the value of the field is 1 , then the report should
> display january, if its 2 , Feburary and so on... Is there a way this can be
> done without having to create a table ?
> --
> pmud|||Hi Harolds,
That was very helpful.
Thanks
--
pmud
"Harolds" wrote:
> Go to the Code tab of the Reports Properties dialog and add the following:
> function MonthValue(byval MonthInt as integer) as string
> select case MonthInt
> case 1
> return "Jan"
> case 2
> return "Feb"
> '...
> case else
> return "Bad Month"
> End select
> end function
> Then in the text box where you want the month to display place this
> expression:
> =Code.MonthValue( Fields!Period.Value)
> You can read more about adding code by searching the help for 'Writing
> Custom Code'
> "pmud" wrote:
> > Hi,
> >
> > I have a table with a field called Period whcih has numbers from 1 - 12. Now
> > I want to say that if the value of the field is 1 , then the report should
> > display january, if its 2 , Feburary and so on... Is there a way this can be
> > done without having to create a table ?
> > --
> > pmud|||Thanks Guys, this helped me out as well
"pmud" wrote:
> Hi Harolds,
> That was very helpful.
> Thanks
> --
> pmud
>
> "Harolds" wrote:
> > Go to the Code tab of the Reports Properties dialog and add the following:
> >
> > function MonthValue(byval MonthInt as integer) as string
> > select case MonthInt
> > case 1
> > return "Jan"
> > case 2
> > return "Feb"
> > '...
> > case else
> > return "Bad Month"
> > End select
> > end function
> >
> > Then in the text box where you want the month to display place this
> > expression:
> >
> > =Code.MonthValue( Fields!Period.Value)
> >
> > You can read more about adding code by searching the help for 'Writing
> > Custom Code'
> >
> > "pmud" wrote:
> >
> > > Hi,
> > >
> > > I have a table with a field called Period whcih has numbers from 1 - 12. Now
> > > I want to say that if the value of the field is 1 , then the report should
> > > display january, if its 2 , Feburary and so on... Is there a way this can be
> > > done without having to create a table ?
> > > --
> > > pmud|||There is an easier way in the cell itself. There is a VB.net function
MonthName(), so
=MonthName(Month(Fields!MyDate.Value), True)
will give "Jan", "Feb" etc. Change the True to False and you'll get
full month names. I also think it is language aware, so will return the
month in the language the client system is set to.
--
Regards
Chris
Stephen L wrote:
> Thanks Guys, this helped me out as well
> "pmud" wrote:
> > Hi Harolds,
> >
> > That was very helpful.
> >
> > Thanks
> > --
> > pmud
> >
> >
> > "Harolds" wrote:
> >
> > > Go to the Code tab of the Reports Properties dialog and add the
> > > following:
> > >
> > > function MonthValue(byval MonthInt as integer) as string
> > > select case MonthInt
> > > case 1
> > > return "Jan"
> > > case 2
> > > return "Feb"
> > > '...
> > > case else
> > > return "Bad Month"
> > > End select
> > > end function
> > >
> > > Then in the text box where you want the month to display place
> > > this expression:
> > >
> > > =Code.MonthValue( Fields!Period.Value)
> > >
> > > You can read more about adding code by searching the help for
> > > 'Writing Custom Code'
> > >
> > > "pmud" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a table with a field called Period whcih has numbers
> > > > from 1 - 12. Now I want to say that if the value of the field
> > > > is 1 , then the report should display january, if its 2 ,
> > > > Feburary and so on... Is there a way this can be done without
> > > > having to create a table ?
> > > > --
> > > > pmud

linking 2 tables

dear experts,
i have two tables, tblcustomer and tblform. in tblcustomer i have cutomerid PK and i have also a field that link these two tables customername. in tblforms i have formname. customername and formname are not PK and customername has a lot of null values.
my question is that i want to link these two tables in customerid not the name and also to get the forms used by this customer. the thing is that i have to write a join query.
any suggestion to do that?

thanks my friendsplease show sample rows from each table|||in tblcustomer:
customerid PK
customername
address
frname
...
general information

in tblforms:
formid PK
formname
JS
CSS
...

now i want to link frname to formname so i can get the forms used by the user.
thanks|||sample rows? you know, so we can see the data?|||in tblcustomer:
customerid PK int
customername varchar
address varchar
frname varchar
...

in tblforms:
formid PK int
formname varchar
JS varchar
CSS varchar
...

i hope this is what you meant.
is there a way to link the table with the formid not with the formname
thanks|||no, that's not what i meant

here are sample rows --

customers
42, Fred Jones, 123 Sesame Street
43, Tom Smith, 456 Main Street

forms
1035, arwbtyms, n, y
1036, uwnctrsj, y, y

i still have absolutely no idea what you are trying to join|||customers
42, Fred Jones, 123 Sesame Street, tio

forms
1035, tio , n, y

this is the sample rows in my case.
now i want to get the the forms name user by a customer form tio in this example, it will be also nice if i can join these two tables with formid instead of formname.
i hope i make my self clear.
let me know if you have any question and thanks for spending time reading my posts.
thanks|||what i did is that i added a field in thbcustomer called formid but how can i force the relation between these two tables so i can join them on formid?
thanks|||alter table tblcustomer
add constraint this_is_the_link foreign key (formid) references tblform (formid)

by the way, you do not need to force a relation in order to be able to join tables

you can join tables on any columns you wish, whether or not they have a relationship|||ok thanks for the hint, but can you show me the query that will link these two tables by formid so i will get the forms used by a customer?
thanks|||the forms used by a customer? are you suggesting that a customer can have multiple forms? if that is true, then adding formid to tblcustomer is wrong, this would limit each customer to one form|||yes i have too many forms and this is in case i want to see the forms used by a customer, that is why i need to link the tables with formid because a customer can have many forms.

to sum up i need a query that will allow me to see all the forms user by a customer.
how can i do that?
thanks man|||is it a one-to-many relationship or a many-to-many relationship?

can the same form be used by more than one customer?|||it is a one to many relationship because a customer can use just one form but the form can be used by many customers, i think my last phrase was wrong, actually i need to see all the clients using the form. the only firled i have in tblcustomer is frname and the field i have in tblforms is formname.
thanks|||do me a favour, please script out your tables|||CREATE TABLE [tblcustomer] (
[customerID] [int] NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DomainName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prmform] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

) ON [PRIMARY]
GO

CREATE TABLE [tblforms] (
[formid] [int] IDENTITY (1, 1) NOT NULL ,
[formName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrameSet] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JavaScript] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

here is my script may be now it is clear.
thanks|||alter table tblforms
add constraint unique_name unique (formName)

alter table tblcustomer
add constraint form_link foreign key (Prmform) references tblforms (formName)
you might need to make them the same size VARCHAR|||thanks my friend for the help. but is there a way to link them by formId not formName?|||yes, you would have to add the formid column to the customers table, then make sure it contains appropriate values from the formid column in the forms table|||thanks my friend|||wonder if form is gettign confused for order?
A customer can have many orders, comprisded of many items, which should have one invoice for one order.|||in my case it is like a profile so no items there.
but i'm wondering, when i create the column in customer table how should i filled it with the right information because i already have some form names, i want to select the formid of this form name and insert it into formid in customer table.
any suggestions?
thanks|||I just wandered in from an adjacent universe/time continuum, but unless I missed something, you will need to simply find out which form is associated with each customer and then insert the appropriate formid into the new column in the customer table. At least for all your customer rows that don't have a NULL formname. For those, I guess you'll need to get on the phone and ask them which form they use...or just populate the associated formid with nulls (since you have no idea what formname OR formid they should have).

For the others it would be a simple join, eh? Something along the lines of UPDATE C
SET FormID = F.FormID
FROM dbo.tblcustomer C,
dbo.tblforms F
WHERE C.Prmform IS NOT NULL
AND C.Prmform = F.formNamesql

linked view in access don't use index

Hello,
I have a table with two field (name of the table : table_with_two_field)
field1, field2
when i create a simple view with no clause where:
create view dbo.view_on_field WITH VIEW_METADATA
as
select field1 from dbo.table_with_two_field
and i linked this wiew on access 2000 and i join with a local table on
field1 i have not response (field1 is a primary key on local and field1
have an index on the sql server table table_with_two_field)
in the trace i can see sql server send all the data : "select field1 from
dbo.view_one_field"
but when i do the same with local table and attached table i have a reponse
directly
can you help me
I'm not sure what precisely you are trying to do, but any time you
fetch data from the server and perform a join to a Access/Jet table,
ALL of the data is fetched from the server, and the join performed
locally by Jet. I am assuming that since you are performing a
heterogeneous join that you are not intending to update the data. What
I would suggest is to select from the SQLS table or view using a WHERE
clause to limit the result set, and dump it into a local Access/Jet
table, which should be very fast since you have fetched the data
locally and eliminated the heterogeneous join. The same local table
can be reused multiple times by deleting all of the rows before
fetching new ones.
--Mary
On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:

> Hello,
> I have a table with two field (name of the table : table_with_two_field)
> field1, field2
> when i create a simple view with no clause where:
>create view dbo.view_on_field WITH VIEW_METADATA
>as
> select field1 from dbo.table_with_two_field
>and i linked this wiew on access 2000 and i join with a local table on
>field1 i have not response (field1 is a primary key on local and field1
>have an index on the sql server table table_with_two_field)
>in the trace i can see sql server send all the data : "select field1 from
>dbo.view_one_field"
>but when i do the same with local table and attached table i have a reponse
>directly
>can you help me
|||Hello Mary,
I try to explain my problem, sorry for my english because i'm spain
when i use directly a linked table with a local table, i have data
immediatly, when I
see the trace, Access send every value field (use in join) from data from
the local table to the sqlserver.
The profiler trace send :
declare @.P1 int
set @.P1=2
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
select @.P1
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
Local table have 3 records
when i do a simple view like this
create view dbo.view as select field1 from dbo.table1
when i use directly this linked view with a local table,
the trace send :
SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft? Access etc..
and slqserver send millions records to access ...
Why the linked view and the linked table not have the same reaction ?
I can't do a view with more filter because every user have his owner local
table.
Thank you very much for your help.
I can
Rega1
"Mary Chipman [MSFT]" wrote:

> I'm not sure what precisely you are trying to do, but any time you
> fetch data from the server and perform a join to a Access/Jet table,
> ALL of the data is fetched from the server, and the join performed
> locally by Jet. I am assuming that since you are performing a
> heterogeneous join that you are not intending to update the data. What
> I would suggest is to select from the SQLS table or view using a WHERE
> clause to limit the result set, and dump it into a local Access/Jet
> table, which should be very fast since you have fetched the data
> locally and eliminated the heterogeneous join. The same local table
> can be reused multiple times by deleting all of the rows before
> fetching new ones.
> --Mary
> On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
> <toni@.discussions.microsoft.com> wrote:
>
|||I'm hazarding a guess here, but I suspect the reason why is because
Access cannot discover the schema for the view. Access caches schema
information for linked tables locally, so it can create the
parameterized prepared statement you are seeing in the first query.
Based on the Profiler output you posted, it *may* not do that for
views, so Access/Jet possibly has no idea of what the schema for the
view's underlying table is. Therefore it fetchs all of the data. You
might want to post in one of the msaccess forums in case the experts
there have a more definitive response.
--Mary
On Tue, 4 Oct 2005 05:35:02 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Hello Mary,
> I try to explain my problem, sorry for my english because i'm spain
> when i use directly a linked table with a local table, i have data
>immediatly, when I
> see the trace, Access send every value field (use in join) from data from
>the local table to the sqlserver.
>The profiler trace send :
>declare @.P1 int
>set @.P1=2
>exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
>"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
>select @.P1
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>Local table have 3 records
>when i do a simple view like this
>create view dbo.view as select field1 from dbo.table1
> when i use directly this linked view with a local table,
>the trace send :
>SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft Access etc..
>and slqserver send millions records to access ...
>Why the linked view and the linked table not have the same reaction ?
>I can't do a view with more filter because every user have his owner local
>table.
>Thank you very much for your help.
>I can
>Rega1
>
>
>
>"Mary Chipman [MSFT]" wrote:
sql

Linked to Excel - data changed on export

Hi Group:
Hope this is the right forum...
Occasionally, when I write data to a linked Excel file from a varchar(50)
field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
seem to have the tick in SQL when I view in QA. Any ideas why this would be,
or how I can avoid that?
Thanks!
eric db
Eric,
The apostrophe has to do with the leading zeros in you data, in Excel (and I
believe in 2003 version, it's no longer happening), Excel treats the values
in order not to lose them, thus puts the apostrophe in front. You want to
make sure that the Excel column is formatted for the data type you wanted to
be formatted before pouring the data in.
Hopefully, it helped.
"eric db" wrote:

> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would be,
> or how I can avoid that?
> Thanks!
> eric db
|||Excel does this to indicate the cell value should be treated as text -
but what you've passed in is probably convertible to an int or
something, so Excel explicitly states it's text by adding the (')
Simon Worth
eric db wrote:
> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would be,
> or how I can avoid that?
> Thanks!
> eric db

Linked to Excel - data changed on export

Hi Group:
Hope this is the right forum...
Occasionally, when I write data to a linked Excel file from a varchar(50)
field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
seem to have the tick in SQL when I view in QA. Any ideas why this would be,
or how I can avoid that?
Thanks!
eric dbEric,
The apostrophe has to do with the leading zeros in you data, in Excel (and I
believe in 2003 version, it's no longer happening), Excel treats the values
in order not to lose them, thus puts the apostrophe in front. You want to
make sure that the Excel column is formatted for the data type you wanted to
be formatted before pouring the data in.
Hopefully, it helped.
"eric db" wrote:

> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would b
e,
> or how I can avoid that?
> Thanks!
> eric db|||Excel does this to indicate the cell value should be treated as text -
but what you've passed in is probably convertible to an int or
something, so Excel explicitly states it's text by adding the (')
Simon Worth
eric db wrote:
> Hi Group:
> Hope this is the right forum...
> Occasionally, when I write data to a linked Excel file from a varchar(50)
> field, it arrives with a preceding single apostrophe (') in Excel. Doesn't
> seem to have the tick in SQL when I view in QA. Any ideas why this would b
e,
> or how I can avoid that?
> Thanks!
> eric dbsql

linked table 'HOST' field, Access->SQLserver

when you create a table link from Access to SQL server, the connection
string identifies the host as the computer at the time the link was
made, regardless of which computer is running Access. Can these table
links be updated at run time so that the SQL Enterprise Manager will see
the login as originating from the computer of origin instead of from the
computer that made the link?
Thanks,
MarkIf you are talking about linking SQL Server tables to an Access .mdb
front end, then the best way to do that is to write VBA/DAO code that
creates the links at runtime when the application starts up, and then
when the application shuts down, code runs that deletes all the links.
For good measure, the code that runs when the application starts up
should also delete any existing links. That is the only way to
reliably clear out any security information which may have been cached
locally in the TableDef objects in the mdb.
--Mary
On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:

>when you create a table link from Access to SQL server, the connection
>string identifies the host as the computer at the time the link was
>made, regardless of which computer is running Access. Can these table
>links be updated at run time so that the SQL Enterprise Manager will see
>the login as originating from the computer of origin instead of from the
>computer that made the link?
>Thanks,
>Mark|||Now that's clever, didn't know you could do that!. I don't program in Access
,
but like the approach you suggest. Care to offer a code fragment for
create/delete example? I never liked the form/data object model in Access,
and having forms linked to missing DAO objects seems, well, interesting!
thanks,
mark
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> If you are talking about linking SQL Server tables to an Access .mdb
> front end, then the best way to do that is to write VBA/DAO code that
> creates the links at runtime when the application starts up, and then
> when the application shuts down, code runs that deletes all the links.
> For good measure, the code that runs when the application starts up
> should also delete any existing links. That is the only way to
> reliably clear out any security information which may have been cached
> locally in the TableDef objects in the mdb.
> --Mary
> On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>|||Here ya go:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
--Mary
On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:
[vbcol=seagreen]
>Now that's clever, didn't know you could do that!. I don't program in Acces
s,
>but like the approach you suggest. Care to offer a code fragment for
>create/delete example? I never liked the form/data object model in Access,
>and having forms linked to missing DAO objects seems, well, interesting!
>thanks,
>mark
>"Mary Chipman [MSFT]" wrote:
>|||Thank you; that works rather nicely.
mark/
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> Here ya go:
> Public Sub LinkODBConnectionString()
> Dim strConnection As String
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Set db = CurrentDb
> ' Specify the driver, the server, and the connection
> strConnection = "ODBC;Driver={SQL Server};" & _
> " Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
> ' Specifying a SQLS user/password instead of integrated security
> ' strConnection = "ODBC;Driver={SQL Server};" & _
> ' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
> ' Create Linked Table. The LinkedTableName and the
> ' ServerTableName can be the same.
> Set tdf = db.CreateTableDef("LinkedTableName")
> tdf.Connect = strConnection
> tdf.SourceTableName = "ServerTableName"
> db.TableDefs.Append tdf
> Set tdf = Nothing
> End Sub
> --Mary
> On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>

Linked table blues

Thanks in advance!
I have created a database that uses Access for the front end and SQL Server for backend. In one of the tables, I have a field of type nvarchar and length of 500. When I originally created the table this field was of type char and 300 charters long. When I have a record of more that 255 characters, Access is only pulling in the last (right end of string) part of the record

(i.e. for this post to this point you would only see something like this

"in the last (right end of string) part of the record (i.e. for this post to this point").

In an attempt to try changing the data type back to char, I used Data Transformation to create a duplicate table so that I could experiment on it. When I link to the duplicate table I get several messages stating that data is larger than field length and this is before I made the changes.

Could someone please give me some direction on how to correct the problem?

Thanks,
Leenvarchar(500) can only store 255 bytes of data. To store 500 bytes of data you would need nvarchar(1000) etc. The nvarchar & nchar data types can store max 4000 bytes not 8000 like varchar & char.

server w/ text field gets Connection Broken error

We have a stored proc on Server B called:

my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL

this proc calls:

my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL

This second stored procedure just selects back an ID that is passed to it (to keep things simple).

If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.

It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.

Any ideas?If I change the linked server from SQL Server to ODBC I get this error message:

Server: Msg 0, Level 19, State 1, Line 15
SqlDumpExceptionHandler: Process 244 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.|||Here is the DML:

-- Run on DATABASE_1
CREATE PROCEDURE my_sp_server_a
@.the_id int, @.the_text TEXT = NULL
AS

SELECT @.the_id

GO

-- Run on DATABASE_2
CREATE PROC my_sp_server_b @.my_text TEXT = NULL
AS

EXEC MY_LINKED_SERVER.DATABASE_1.DBO.my_sp_server_a @.the_id = 1, @.the_text = @.my_text

GO