Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

sql

Cannot use a CONTAINS or FREETEXT...

Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.

I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:

"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."

I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.

The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).

I do not have replication running and I don't know where to look next.

Server:
SQL 2005 sp1
Windows 2003 Server

Any ideas would be greatly appreciated.

RussYou can create a FulltextCatalog and Index with Templates inside the Management studio.
At first, after you selected a Template, select the Database, in wich you will Fulltext enable, in the TopLeft Dropdown. Let the Tamplates as is. When you aktivate a template from Template Explorer, there is a little Button in the Buttonbar with an "A" and a "B" on it. Hit this Button and you get a Dialog to change the "confusing things" between the <brackets>. In this Dialog you need only change the DefaultEntry in the textbox with a Name for youre FulltextCatalog. After press OK in the DialogBox, press the red Exclamtionmark in the Toolbar. Now you've created youre FulltextCatalog.|||I understand. I don't think you read my post or maybe I don't understand. I have the fulltext running properly 99.9% of the time. Our website gets about 1000 hits on the search page a day, and the stored procedures run with out error. However, once every couple of weeks, the stored procedure that is running properly fails with the above error. I have checked that:

a) I am running incremental population late at night, not when the errors occur

b) I run a full db backup each night at midnight and then transactionals every hour, but the error does not occur during those time (last one was at 12:45pm)

cannot upload reports: pls help

Hi,
I just installed reporting service on a new box and it contains SQL server
2000 also. But, when I get into the manager screen, the options such as New
folder, Upload files etc are all disabled. The hyperlink appears but nothing
happens when i click on them..
Any suggestions?
Thanks in advance for your help.
-sDid you ever figure this out? I too have the same problem.
Thanks.
"San" wrote:
> Hi,
> I just installed reporting service on a new box and it contains SQL server
> 2000 also. But, when I get into the manager screen, the options such as New
> folder, Upload files etc are all disabled. The hyperlink appears but nothing
> happens when i click on them..
> Any suggestions?
> Thanks in advance for your help.
> -s
>
>

Cannot set a Variable from a select statement that contains a variable? Help Please

I am trying to set a vaiable from a select statement

DECLARE @.VALUE_KEEP NVARCHAR(120),

@.COLUMN_NAME NVARCHAR(120)

SET @.COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @.VALUE_KEEP = (SELECT @.COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @.VALUE_KEEP

PRINT @.COLUMN_NAME

RESULTS

-

FirstName <--@.VALUE_KEEP

FirstName <--@.COLUMN_NAME

SELECT @.COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @.COLUMN_NAME variable?

Any help greatly appreciated!

Your second SET statement is just applying the value of @.COLUMN_NAME to @.VALUE_KEEP. Based on the hardcoding in the first set statement I'm not sure exactly what you would be achieving with the variable use. If you are just trying to select a column dynamically from a given table you would need to use dynamic sql. Syntax something like...

DECLARE @.myVariable varchar(50), @.sql nvarchar(max) --or 4000 if using SQL 2000

SET @.myVariable = 'FirstName'

SET @.sql = 'SELECT ' + @.myVariable + ' FROM dbo.myTable WHERE myColumn = myColumn'

EXEC sp_executesql @.sql

That said, I would be very wary of using this approach in an application as there are security and maintainability issues with dynamic sql.

|||

I am passing this to coalesce()

my goal is to dynamically loop through the columns using coalesce() that is part of my stored procedure for de-duplicating a database. Since the columns can change I wanted to pull the columns and table dynamically.

I compare the records of the duplicates to update any null fields and want to do something like

coalesce(@.Record_Being_Kept, @.Record_Being_Replaced)

so two things...

I want the values in the coalesce ('Brent', 'Brent) when it is comparing the firstnames "Obviously this is much more applicable with the phone and email info" to Merge the Dupes.

I don't know if there is a way to pass EXEC sp_executesql @.sql to coalesce() ?

Cannot schema bind function 'fn_xxxx' because it contains an EXECUTE statement

Does anyone have any pointers on this error message?
Funny thing is that no where in the SQL documentation does it say that an
EXEC is not allowed in a function with SCHEMABINDING option set.Dynamic SQL is not allowed within a function at all, schemabinding or not...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Clark" <steve3264@.hotmail.com> wrote in message
news:e8o#nMX4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> Does anyone have any pointers on this error message?
>
> Funny thing is that no where in the SQL documentation does it say that an
> EXEC is not allowed in a function with SCHEMABINDING option set.
>|||The rules for the allowable constructs in functions are not well documented.
This one is almost there though. EXEC is only permitted if it references an
extended proc (or another function - but that's pretty much redundant).
Extended procs reside in Master but schema binding requires that "All
objects referenced by the function must be in the same database as the
function". So as documented, you could only ever create functions containing
EXEC as schemabound if they were in Master... but in reality that doesn't
seem to work either - not that it would be very useful anyway... :)
As Aaron says, dynamic SQL isn't permitted in functions ever. Nor are calls
to regular SPs.
--
David Portas
SQL Server MVP
--

Cannot schema bind function 'fn_xxxx' because it contains an EXECUTE statement

Does anyone have any pointers on this error message?
Funny thing is that no where in the SQL documentation does it say that an
EXEC is not allowed in a function with SCHEMABINDING option set.
Dynamic SQL is not allowed within a function at all, schemabinding or not...
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Clark" <steve3264@.hotmail.com> wrote in message
news:e8o#nMX4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> Does anyone have any pointers on this error message?
>
> Funny thing is that no where in the SQL documentation does it say that an
> EXEC is not allowed in a function with SCHEMABINDING option set.
>
|||The rules for the allowable constructs in functions are not well documented.
This one is almost there though. EXEC is only permitted if it references an
extended proc (or another function - but that's pretty much redundant).
Extended procs reside in Master but schema binding requires that "All
objects referenced by the function must be in the same database as the
function". So as documented, you could only ever create functions containing
EXEC as schemabound if they were in Master... but in reality that doesn't
seem to work either - not that it would be very useful anyway...
As Aaron says, dynamic SQL isn't permitted in functions ever. Nor are calls
to regular SPs.
David Portas
SQL Server MVP

Cannot run SQL2005 stored procedure from excel 2003

Usually have no problems pulling data into excel via SQL stored procedures until now.

Created an sp that contains a

declare @.tbl table(

Customerno varchar(6)

,InvoiceDate varchar(25)

,CustomerType varchar(10)

,CustomerRegion varchar(50)

,CustomerTypeName varchar(50)

)

Followed by a insert then a select statement on this @.tbl variable.

whenever i try to call this sp i get an error message saying "The query did not run, or the database table could not be opened"

This is what i'm using to connect:

With Sheets(1).QueryTables.Add(connection:="OLEDB;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=UnityBI;" & _
"Data Source=SQLdatabase;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Workstation ID=ACER-DAVEW;Use Encryption for Data=False;" & _
"Tag with column collation when possible=False", Destination:=Sheets(1).Range("A1"))
.CommandType = xlCmdSql
.CommandText = "usp_gordonsnodrops "
'.Name = "proclarity UnityBI_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

The stored procedure runs fine from other applications, just excell seems to be the problem.

Any help would be greatly appreciated.

Ok, after a very frustating week with this problem the answer turns out to be very easy.

Kinda deflating really after the amount of hair i've pulled out.

The stored procedure needed the statement "Set NoCount On" adding.

Works like a charm now

eg

ALTERPROCEDURE [dbo].[usp_GordonsNoDrops]

@.MonthYear asvarchar(50)='march06/07'

--@.End as varchar(10)

as

begin

setnocounton

sql statements etc....

end

Cannot resolve the collation conflict between

Hi
Your report's query contains JOIN on varchar/nvarchar columns which have
different collation
Try
select * from table join anothertable on table.col =anothertable.column
COLLATE danish_norwegian_ci_as
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>
Hi
I have no idea. I don't know Crystal Report
"H.Gjerde" <hg@.norspace.no> wrote in message
news:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl...
> Thank you
> It help, in my plain sql script
> But I stil have the problem, because I not realy sure where to put it in
> my CrystalReport. The report is predefined.
> ...? :|
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
>
|||can you create SP and call it in Crystal Report
Regards
Amish shah
http://shahamishm.tripod.com
On Jan 24, 3:31 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> I have no idea. I don't know Crystal Report
> "H.Gjerde" <h...@.norspace.no> wrote in messagenews:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl. ..
>
>
>
>