Invoking a SQL Server user-defined function via SQL passed to Informix

==========
Background
==========
My colleague and I are using MS Reporting Services to create reports based upon data extracted from an Informix database. Inseveral reports, we have the need to parse the contents of a variable length field in order to extract a variable lengthforeign key value.

Note that the key value is of variable length and embedded within a string that's also of variable length.

==========
Question
==========
Given the fact that we aren't allowed to add anything within the back-end Informix environment, we wrote a SQL Server userdefined function that can extract a variable length "key" value from a variable length string. My question is as follows:

Is it even possible to invoke a SQL Server user defined function in our SQL statement (passed to Informix) within the context of Reporting Services?

==========
Environment Notes
==========
- We are NOT allowed to add anything within the back-end Informix db environment.

- We are connecting via an Informix ODBC driver with a linked Informix server defined in our SQL Server environment.

- We've tried defining the user-defined function within the "master", "ReportServer" and "ReportServerTempDB" databases with the appropriate permissions set.

==========
Error Messages
==========
We've received the following error message after trying to invoke the SQL Server user defined function:

SELECT field1, field2, field3, dbo.udf_GetStringElement(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Identifier length exceeds the maximum allowed by this version of the server.

We receive the following error message after creating a new SQL Server user defined function using a shorter name:

SELECT field1, field2, field3, dbo.gse(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Routine (dbo.gse) can not be resolved.

Any feedback would be appreciated,

ndm

have you tried using a custom code function for it ?|||

Hey, thanks for the reply.

I'm not sure I understand what you mean by "custom code function". The user defined functions I mentioned, "udf_GetStringElement", "gse", are user defined functions that I wrote. It's not one of the "system" functions (e.g. "fn_isreplmergeagent") found in master.

I merely created the function WITHIN the context of the "master" db and eventually within "Report Server" and "ReportServerTempDB" in an attempt to invoke the function within a Report Services SQL statement.

|||By Custom Code function I mean the custom code vb.net functions you canwrite under Report -> Properties -> Code tab. You canreplicate the logic of your user defined function you have in sql intovb.net.
what xactly foes your user defined function do ?
|||

Ah, I understand what you're saying about simply replicating/writing the string parsing/extraction logic within the Report Services environment, but our issue isn't really a (post-data aggregation) formatting problem.

Our SQL Server user defined function parses a variable length string in order to extract a variable length "foreign key" value that we'll need to use for joining data from several Informix tables.

Unless there's another approach of which I'm unaware, I'm assuming that we need to parse this string DURING the SQL execution in order to perform the requisite join(s).

Appreciate the feedback,

ndm

|||I didnt understand the situation completely..you have the SELECT stmt from Informix db but the UDF is in SQL Server right ? does the udf do anything other than string parsing ? looks like its a little complicated and we could be hitting the limitations ( one of the many) of RS.|||

Correct; the SELECT statement (defined within shared Report Services dataset) is passed to Informix and our custom UDF is in SQL Server.

More specifically, I've tried placing our custom SQL Server UDF within several of the SQL Server databases ("master", "ReportServer" and "ReportServerTempDB"). When I attempt to invoke the UDF within the SQL statement passed to Informix, I receive the aforementioned error messages.

The key point is that we need to be able to parse DURING the SQL execution in order to perform some requisite join(s).

Within a pure SQL Server environment, I can access the UDF from master (or, for that matter, any other SQL Server db so long as the appropriate permissions are set). I'm not even sure if what we're trying to accomplish is possible since the SQL statement is being passed to Informix. I was wondering if there was some "Reporting Services" method for invoking our custom UDF defined WITHIN the SQL Server realm.

Again, I appreciate the reply.

ndm

|||

The SQL statement you have is executed on Informix DB engine and it wouldnt understand the SQL UDF..unless you make a specific connection to SQL for which it may not be possible in 1 SELECT stmt..you prbly might need to use a set of stmts..If the UDF is only doing some string parsing I would recommend moving it into RS custom code and rewriting it in vb.net. Cant think of anything else..

|||

I would create a DTS package that is scheduled to get the data from Informix and populate the Reporting Service database. Try the urls below for more info. Hope this helps.

http://www.sqldts.com

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx#EDAA

Kind regards,

Gift Peddie

|||

Yes, I'm thinking that we may have to take a less directextract+dump+cleanse route (Informix -> MS SQL Server -> MS Reporting Services) for this scenario. We were trying to avoid taking snapshots of the data and having to maintain a secondary (albeit temporary) data store.

Your recommendation merits further consideration.

Thanks for the feedback,

ndm

Invoking or sending SQL queries using dOS

Hello Folks,
I want to create a simple batch DOS script to query mi SQL Server 2000 database. How I could do this?
I just want to run a select for a table but I dont want people to interct with sql query analyzer or enterprise manager to avoid any issues.
Regards,Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.|||Originally posted by Paul Young
Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.

UID stands for? PWD I assume is the pasword, isnt.

How I can redirect the results to a txt file,

OSQL -S OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b >> query.txt

Am I right?

Thanks for your prompt reply and help|||UID = User ID

You could pipe the output to a text file but the -o parm would be more usefull.|||How I can get the instance name. I have tested in my environment and I got the next error:

[DBNETLIB] Sql Server does not exist or access denied
[DBNETLIB] ConnectionOpen <Connect(())

As per this result I checked the server name I used typing osql -L. I got the names and used them but with the same result.

When I open the Sql query analyzer I connect using windows authentication option to connect to my databases.

Please suggest..|||For Windows Security I think you need to use -E option instead of -U and -P options.

Tim Ssql

Invoice with multi pages

Hi,

I want know if is possible create a report, with this caracteristics.

Page 1:

Header:

Nome of Company Invoice no 1

Original

Detail:

line 1

line 2

line 3

Page 2:

Header:

Nome of Company Invoice no 1

Copy

Detail:

line 1

line 2

line 3

.

.

.

Many pages of parameter in my code in Visual Studio.

Tanks,

Camsoft77

what about with two table controls that are identical except the "Original"/"Copy" difference?|||

urbancenturion,

My question is how i can create two pages or more, with same information but change for example 1 parameter.

I need build a report to create Invoice. with 3 copies of invoice.

Att,

Camsoft77

|||

>>I need build a report to create Invoice. with 3 copies of invoice.

The best way to do this is with a cartesian join added to your "real" query.

For example, the following will create three rows for each real table row, with an extra column called "invoice_info", properly ordered so the "original" shows up first, in case that is important to you <s>:

Code Snippet

select sales_no, invoice_info from orderheader join

(select 'ORIGINAL' as invoice_info, 1 AS invoice_order

union

select 'COPY1',2

union

select 'COPY2',3

) xx

on 1 = 1

order by sales_no, invoice_order

.. now put a group on your invoice # (or sales_no, in my example) or whatever you want in your report...

HTH,

>L<

Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
--
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny

Invalid object name...

Hello all,
however, this is my first question to this news. I am working with RS SP1,
and have question. I have example procedure:
CREATE PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
CREATE TABLE #table (ID SQL_VARIANT)
INSERT INTO #table(ID) VALUES (@.ord)
SELECT * FROM #table
DROP TABLE #table
GO
When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID) I
get following error:
Could not generate a list of fields for the querry...
Invalid object name #table
Ofcourse, this procedure works good in Query Analyser. Anyone has idea, why
this is not working ?
--
Ing. Branislav GerzoI got it to work w/o a problem. However, I do see the same error if I enter
the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation dialog
box while attempting to define the dataset it uses. Try actually executing
the procedure with a parameter or passing in a static value from the Generic
Query Designer data window once you've defined the dataset. If you use
static value like (EXECUTE GEGE_test_a '11'), simply change it after to use
your query parm.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
> I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
> why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Use a table ariable instead of the temp table:
ALTER PROCEDURE GEGE_test_a
@.ord SQL_VARIANT AS
SET NOCOUNT ON
DECLARE @.table TABLE(ID SQL_VARIANT)
INSERT INTO @.table(ID) VALUES (@.ord)
SELECT * FROM @.table
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Ing. Branislav Gerzo" <IngBranislavGerzo@.discussions.microsoft.com> wrote
in message news:0B87A6E5-C4C2-452E-A601-5B76C6DA3D75@.microsoft.com...
> Hello all,
> however, this is my first question to this news. I am working with RS SP1,
> and have question. I have example procedure:
> CREATE PROCEDURE GEGE_test_a
> @.ord SQL_VARIANT AS
> SET NOCOUNT ON
> CREATE TABLE #table (ID SQL_VARIANT)
> INSERT INTO #table(ID) VALUES (@.ord)
> SELECT * FROM #table
> DROP TABLE #table
> GO
> When I want add DataSet with this procedure (EXECUTE GEGE_test_a @.OrderID)
I
> get following error:
> Could not generate a list of fields for the querry...
> Invalid object name #table
> Ofcourse, this procedure works good in Query Analyser. Anyone has idea,
why
> this is not working ?
> --
> Ing. Branislav Gerzo|||Dejan Sarka [DS], on Friday, October 29, 2004 at 17:17 (+0200)
contributed this to our collective wisdom:
DS> Use a table ariable instead of the temp table:
DS> ALTER PROCEDURE GEGE_test_a
DS> @.ord SQL_VARIANT AS
DS> SET NOCOUNT ON
DS> DECLARE @.table TABLE(ID SQL_VARIANT)
DS> INSERT INTO @.table(ID) VALUES (@.ord)
DS> SELECT * FROM @.table
DS> GO
thanks, I was afraid that someone will answer like this. Ofcourse,
this works, but my problem is, that in my situation I have to fill
@.table_var with result of another procedure. And I found this:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&
A3:
1. Tables variables cannot be used in a INSERT EXEC or SELECT INTO
statement.
2. You cannot use the EXEC statement or the sp_executesql stored
procedure to run a dynamic SQL Server query that refers a table
variable, if the table variable was created outside the EXEC statement
or the sp_executesql stored procedure. Because table variables can be
referenced in their local scope only, an EXEC statement and a
sp_executesql stored procedure would be outside the scope of the table
variable. However, you can create the table variable and perform all
processing inside the EXEC statement or the sp_executesql stored
procedure because then the table variables local scope is in the EXEC
statement or the sp_executesql stored procedure.
Ofcourse, i'd like to use table variables, they are fast, they are
cool. But, how to fill them with result of another procedure ?
I can't cheat them in any way, I have only one idea for that -
procedure which fill @.tabl_var using cursors. But I hope there is
better way do this.
Dejan, please help.
--
...m8s, cu l8r, Brano.
[Alright, who g r e a s e d the tagline?.]|||John H. Miller [JHM], on Friday, October 29, 2004 at 11:14 (-0400)
typed the following:
JHM> I got it to work w/o a problem. However, I do see the same error if I
enter
JHM> the (EXECUTE GEGE_test_a @.OrderID) statement in the dataset creation
dialog
JHM> box while attempting to define the dataset it uses.
anyone knows, why this error occurs ? I can't use temp tables in my
procedures ?
JHM> Try actually executing
JHM> the procedure with a parameter or passing in a static value from the
Generic
JHM> Query Designer data window once you've defined the dataset. If you use
JHM> static value like (EXECUTE GEGE_test_a '11'), simply change it after to
use
JHM> your query parm.
No, it also doesn't work, I get the same message back. (could not
generate a list...). I really don't know why, it is known bug, or
what?
Thanks a lot. My all work stops on this :(((
--
...m8s, cu l8r, Brano.
[Applaflammaphobia: A vacation fear that the house will bu]

Invalid object name!

Hi,
I'm trying to create a new table by merging two files together. They both
have exactly the same table structure. I.e. they are both got 1 field called
ref varchar(255).
The code I'm using is:
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
The error message I'm getting is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'U_T_XmasOnly'.
Can you please tell me what I'm doing wrong?
Thanks in advance
RobRobert
I think you missed INTO within the INSERT statement
It shoul be
INSERT INTO U_T_XmasOnly(REF)
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> Hi,
> I'm trying to create a new table by merging two files together. They both
> have exactly the same table structure. I.e. they are both got 1 field
called
> ref varchar(255).
> The code I'm using is:
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> The error message I'm getting is:
>
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'U_T_XmasOnly'.
> Can you please tell me what I'm doing wrong?
> Thanks in advance
> Rob|||The INTO is optional, so the INSERT statement is fine, as long as the table
already exists. Your object 'U_T_XmasOnly' musn't exist.
Looking at your statement "I'm trying to create a table" I think what you
want to do is this:
SELECT ref
INTO U_T_XmasOnly
FROM
(
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
) x
But what you should really do is create your table first, then use the
INSERT syntax above:
eg
CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
Basically the syntax for adding records to an _existing_ table is the
INSERT, creating a table on they fly and adding records at the same time is
the SELECT INTO, and really you should create the table first, then add the
records, using the CREATE TABLE and INSERT.
Let me know how get on.
Damien
"Uri Dimant" wrote:

> Robert
> I think you missed INTO within the INSERT statement
> It shoul be
> INSERT INTO U_T_XmasOnly(REF)
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> called
>
>|||Hehehe, right , I have never thought about it.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:45A4A9EE-A080-45AC-85E7-E3DC24EB2BD6@.microsoft.com...
> The INTO is optional, so the INSERT statement is fine, as long as the
table
> already exists. Your object 'U_T_XmasOnly' musn't exist.
> Looking at your statement "I'm trying to create a table" I think what you
> want to do is this:
> SELECT ref
> INTO U_T_XmasOnly
> FROM
> (
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> ) x
> But what you should really do is create your table first, then use the
> INSERT syntax above:
> eg
> CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> Basically the syntax for adding records to an _existing_ table is the
> INSERT, creating a table on they fly and adding records at the same time
is
> the SELECT INTO, and really you should create the table first, then add
the
> records, using the CREATE TABLE and INSERT.
> Let me know how get on.
>
> Damien
> "Uri Dimant" wrote:
>
both

Invalid Object Name Error

I'm trying to create a report model using a set of tables from two different servers. Creating the Data Sources and the Data Source View is no problem, however, while trying to create a Report model I run into an error that says,

An error occurred while executing a command.
Message: Invalid object name 'dbo.table_name.
Command:
SELECT COUNT(*) FROM [dbo].[table_name] t

I've checked the schemas for both these tables and they are correct. Why is this error occuring?

Any suggestions would be appreciated!

I'm not sure why the error occurs here but I have a found solution around the problem. You can create named queries that Union your tables from your different data sources into one query.sql

Invalid object name dbo.getname ?

someone please help me?

i've made this used-defined function and sql won't let me use it,

CREATE FUNCTION dbo.getname (@.sname varchar(255))
RETURNS TABLE
AS

RETURN
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName

using it,

select dbo.getname(c.EMPLOY_REF)
from c_contact as c

but everytime i try to use it i get,

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.getname'.

what's wrong with it?, i should have rights, i am set as sysadmingot it to work,

CREATE FUNCTION dbo.getname (@.sname nvarchar(255))
RETURNS nvarchar(255)
AS

BEGIN
DECLARE @.ss as nvarchar(255)
SET @.ss = (
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName
)
RETURN(@.ss)
END

Invalid Object Name

Ok I'm trying to connect to my easycgi.com MSSQL database.

I can connect OK.

My ID is in the db_owner group.

I can create and edit tables and data.I can open a table and see the data.I can view the SQL statement behind the open table (select * from Table) and execute it successfully.

But if I open a new query window and type "select * from [table]" (or any other query), no matter which table it is, I get an error:

Msg 208, Level 16, State 1, Line 1
Invalid object name '[table name]'.

I've searched the web and found this error plenty of times, usually associated with security or the schema. But all my objects are under dbo and I'm in db_owner... ?

Can you post the exact query you typed into the query analyzer?

|||

I'll go you one better:

|||

Try:

sp_help 'Messages'

or

SELECT*

FROMINFORMATION_SCHEMA.TABLES

and given what you've said, "SELECT USER" should return "dbo", correct?

|||

sp_help 'Messages' gives me this:

Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Messages' does not exist in database 'master' or is invalid for this operation.

SELECT *

FROM INFORMATION_SCHEMA.TABLES gives me:

master dbo spt_fallback_db BASE TABLE
master dbo spt_fallback_dev BASE TABLE
master dbo spt_fallback_usg BASE TABLE
master dbo spt_monitor BASE TABLE
master dbo spt_values BASE TABLE

SELECT user gives me "guest"... ? I'm logged on under my own ID.

|||

Ok I disconnected and reconnected, then went to options and told it to connect to my individual database and that seems to have done it... Never had to do that before but I guess querying interactively requires a direct connection to my DB. Strange.

Invalid Object Name

Can anyone help me out with an error that I get when
attempting to create a dataset that uses a stored
procedure? I wrote a moderately complex sp that utilizes
temporary tables. In fact, the result set returned is
that of a select statement against a temp table. However,
when I try to define a new dataset for a report that I am
writing, I get the following error message:
Could not generate a list of fields for the query
Check the query syntax or click refresh fields on the
query toolbar
Invalid object name '#Tmp'
Of course #Tmp is the name of the temporary table that I
am selecting from to return the result set.
Any help would be appreciated. I am using a fresh install
of reporting services without any service packs applied.
Thanks!
TimTry declaring a table variable instead.
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>|||Tim:
Your solution to the temp table is better than mine and I would like to learn more about the local variable of type Table. Would you PLEASE share it with me.
I don't understand what do you mean by local variable of type Table. May I PLEASE have the URL to this local variable.
Thanks!
Augusta
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>

Invalid file name trying to create catelog

I am trying to "Define full text indexing" on a table but
when it attempts to create the catelog, I get the
following error:
Execution of the full text operation failed. The
directory name is invalid.
Any ideas? I check to ensure the directory is correct....
Mike B
What is the path? Normally if the path is too long you get a very specific
error message stating that. Also are you trying to create your catalog on a
fixed disk?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mike B" <mbujak@.psi-hci.com> wrote in message
news:0a7701c46e71$f0b72230$a301280a@.phx.gbl...
> I am trying to "Define full text indexing" on a table but
> when it attempts to create the catelog, I get the
> following error:
> Execution of the full text operation failed. The
> directory name is invalid.
> Any ideas? I check to ensure the directory is correct....
> Mike B
|||
>--Original Message--
>What is the path? Normally if the path is too long you
get a very specific
>error message stating that. Also are you trying to
create your catalog on a[vbcol=seagreen]
>fixed disk?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mike B" <mbujak@.psi-hci.com> wrote in message
>news:0a7701c46e71$f0b72230$a301280a@.phx.gbl...
but[vbcol=seagreen]
correct....
>
>.
>
I was using the default path :
C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA
I also created a testing folder on C where the catelog
directory name would have been
C:\Tesging
Fixed disk, yes, right on the server c:
Mike B
|||Mike,
What is the <value> for the following registry key on the server where this
problem exists?
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<named_instance>\MSSQLServer
FullTextDefaultPath= <value>
Do you get the same error if you specify a specific path for your new FT
Catalog name <Cat_Address>?
There should be no invisible or un-printable characters in the above <value>
for FullTextDefaultPath. Could you use Regedit and delete the current string
and re-enter it? In the past I've had customer's who had un-printable
(un-viewable) characters in this Registry key value cause similar
problems...
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:0b1d01c46e7a$38535f70$a301280a@.phx.gbl...
> get a very specific
> create your catalog on a
> but
> correct....
> I was using the default path :
> C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA
> I also created a testing folder on C where the catelog
> directory name would have been
> C:\Tesging
> Fixed disk, yes, right on the server c:
> Mike B
>

Invalid destination path when trying to create distribution database

Hello. I am attempting to add a distribution database to an instance of SQL Server on my local PC. I am using SQL 2005 Developer Edition. I am able to set the instance up as a distributor using the following command:

exec sp_adddistributor @.distributor = N'computername\instancename'

I use the following command to create the distribution database:

exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data',

@.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data',

I get the following error when executing that command:

Msg 14430, Level 16, State 1, Procedure sp_adddistributiondb, Line 214

Invalid destination path C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data.

It's not due to permissions on that particular folder. I have tried using other folders, all with the same results. Also, I am an Administrator on the PC, so I have full permissions. I have tried running the SQL Server services as myself and Local System. Same results both times. Co-workers with apparently the same setup are able to create the distribution database without a problem.

Has anybody had this type of problem?

Thanks,

Evan

I executed the same thing above, but different path, and it worked for me. Proc sp_adddistributiondb is raising error 14430 because proc sp_MSget_file_existence cannot locate the path you specified. I'd run a profiler trace to see why this proc cannot locate your path.

Invalid cursor state @ Distribution Agent

Hi! When i create a Transactional Replication between two SQL Server 2000 (with no additional Service Pack or Update) with the Enterprise Manager, i get this error (also when i restart the agent):
Invalid cursor state
(Source: ODBC Driver Manager [ODBC]; Error number: 24000)
I think it the MDAC is not the problem, because i never updated the SQL Server 2000 and there the error is from the ODBC SQL Driver. But here it′s from the Driver Manager.
Please help me!! I really despair!
Thanks
Does this apply to your case:
http://support.microsoft.com/default.aspx?kbid=831997?
HTH,
Paul Ibison

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
DanSounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
DanSounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
--
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid Cursor State

After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan
Sounds like this KB article.
FIX: An invalid cursor state occurs after you apply Hotfix 8.00.0859 or
later in SQL Server 2000
http://support.microsoft.com/kb/831997
However, this MS PSS only fix looks like BUILD 876. BUILD 878 is public and
located here:
http://support.microsoft.com/?kbid=838166
Since these are cumulative, it would include the fix for the other.
Sincerely,
Anthony Thomas
"DLS" <DLS@.discussions.microsoft.com> wrote in message
news:2BB9D7A0-6BCE-426F-9375-7D205280F4DA@.microsoft.com...
After I create a table, if I attempt to modify or insert a column using the
wizard in EM, I get the following error:
-Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid Cursor State
I have SP3a installed, Analysis Services and SQL Reporting. Any suggestions?
Thanks.
Dan

Invalid column name using BCP

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Quote:

Originally Posted by sarah21

Hi,

I am having trouble creating the data files. I received the error that there is an invalid column name 'Name' when using the BCP to create the files. How can I find out where this error is coming from?

Thanks

Sarah,

Name is a funny word in SQL Server as it is also a programming command and that is why you may be experiencing problems, try putting the column name in [] or change column name to be something more meaingfull like FullName.

Hope that helps

Invalid Column Name

Hi the following SP that causes an error.

CREATE PROCEDURE GetInfo
(
@.MinPriceint=0,
@.MaxPriceint=9999999999,
@.TypeHomenvarchar(50)=NULL,
@.Locationnvarchar(100)=NULL
)
AS
Declare @.strSql nvarchar(255)
Set @.strSql="Select * from table WHERE "
Set @.strSql=@.strSql + 'Price BETWEEN ' + CONVERT(nvarchar(20),@.MinPrice) + ' and ' + CONVERT(nvarchar(20),@.MaxPrice )
If @.TypeHome != "No Preference"
 Set @.strSql=@.strSql + ' and Type = ''' + @.TypeHome+ ''''
If @.Location != "No Preference"
 Set @.strSql=@.strSql + ' and City = ''' + @.Location+ ''''
Set @.strSql=@.strSql + ' and IDX = ''Y'' ORDER BY Price'
Exec(@.strSql)
GO

The Error I get is:
"Error 207: Invalide Column Name 'Select * from table WHERE'
Invalid Column Name 'No Preference'
Invalid Column Name 'No Preference'

I have checked the table and the columns do exist, spelled correctly and caps are all the same. Also, this same SP in another table works just fine.

What is causing this error?

Thanks in advance!change your double quotes to single quotes when you assign a string to a variable.
sample : you should say set @.var = 'value' and not set @.var= "value "

hth

Invalid Class String (Database diagram)

I am getting an error trying to create a new database diagram. I assume that
some underlying COM component is not registered properly, but I am not sure
which one, as there is no Event Log entry, only the error in SQL Management
Studio.
===================================
Invalid class string
(MS Visual Database Tools)
Program Location:
at
System.Runtime.InteropServices.Marshal.ThrowExcept ionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VirtualProject.Microsoft.SqlServer.Managemen t.UI.VSIntegration.Editors.ISqlVirtualProject.Crea teDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.ISqlVirtualProject.CreateDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.CreateDesignerWindow(IMan agedConnection
mc, DocumentOptions options)
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)
************************************************
Think outside the box!
************************************************
Solved by uninstalling and reinstalling the tools.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)
************************************************
Think outside the box!
************************************************
"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@.comcast.netNoSpamM> wrote in
message news:%23BR6sXg2HHA.5884@.TK2MSFTNGP02.phx.gbl...
>I am getting an error trying to create a new database diagram. I assume
>that some underlying COM component is not registered properly, but I am not
>sure which one, as there is no Event Log entry, only the error in SQL
>Management Studio.
>
> ===================================
> Invalid class string
> (MS Visual Database Tools)
> --
> Program Location:
> at
> System.Runtime.InteropServices.Marshal.ThrowExcept ionForHRInternal(Int32
> errorCode, IntPtr errorInfo)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VirtualProject.Microsoft.SqlServer.Managemen t.UI.VSIntegration.Editors.ISqlVirtualProject.Crea teDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions,
> IManagedConnection con)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.ISqlVirtualProject.CreateDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions,
> IManagedConnection con)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.CreateDesignerWindow(IMan agedConnection
> mc, DocumentOptions options)
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> http://gregorybeamer.spaces.live.com
> Co-author: Microsoft Expression Web Bible (upcoming)
> ************************************************
> Think outside the box!
> ************************************************
>

Invalid @owner_login_name and subscriptions

I've been trying to create subscriptions on a new server and have been
receiving errors on the login name. After researching it, I see that can be
as a result of a server name change, and indeed, the new server was renamed
after SQL installation. However, I've done the drop servername, add
servername thing, restarted and even rebooted. The new @.@.Servername value is
correct. But I still get the same error (even after updating sysjobs
originating server). Anybody have any suggestions? If I have to reinstall, I
can, but would that be SQL or SQL RS or both?
--
Thanks,
CGWAh, just had to reconfigure... RSConfig
--
Thanks,
CGW
"CGW" wrote:
> I've been trying to create subscriptions on a new server and have been
> receiving errors on the login name. After researching it, I see that can be
> as a result of a server name change, and indeed, the new server was renamed
> after SQL installation. However, I've done the drop servername, add
> servername thing, restarted and even rebooted. The new @.@.Servername value is
> correct. But I still get the same error (even after updating sysjobs
> originating server). Anybody have any suggestions? If I have to reinstall, I
> can, but would that be SQL or SQL RS or both?
> --
> Thanks,
> CGW