Cannot Change Referenced UDF

Jun 17, 2008

I have a UDF in my database like so...

ALTER FUNCTION [dbo].[f_spn_GetCustomerNo]
(
-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
RETURN 12441
END

This function is used as the default for a Customer_No column in every table. Now I want to change the UDF to RETURN 11564 but when I attempt to Alter Function I get the error:

Cannot ALTER 'dbo.f_spn_GetCustomerNo' because it is being referenced by object 'DF__tm_spn_eq__Custo__5C6CB6D7'.

which is a table that is using the UDF as a default. Is there any way around this with the exception of removing the default from every table, changing my function, and then resetting the default?

TIA!

View 10 Replies


ADVERTISEMENT

Memory Referenced Cannot Be Read

Nov 8, 2000

Hello, this is an error I've gotten today when trying to expand system tables such as master and msdb in Enterprise Manger:
The instruction at "ox418561c4" rferenced memory at "00x00000034".
The memory could not be read.
I have no clue what it means. I did delete about 45 mg of .TRN files from a log directory on the server. Could it be the result? What's my next step?
thank you again

View 1 Replies View Related

Cannot Run Query--referenced Object

Apr 9, 1999

Msg 225, Level 16, State 1
Cannot run query--referenced object (name NAME NOT RECOVERABLE) dropped during query optimization.

can anyone tell me how to solve this problem
thanks

Ali

View 2 Replies View Related

Invalid Locator De-referenced

Oct 3, 2006

In our application we use three database(access, sql server and oracle) to store configurations. We use a program to copy the configuration from one database to another. We use ODBC and MFC recordsets to do the copy. We recently updated the application and changed the the table schema's. Before we had two tables which each had a schema of one long as key and one image field. Now we have one table with one long as key and three image fields. Now when we copy to sql server from access we get the error " Invalid locator de-refrenced" when we have large data (about 1 MB) in one image field. Before the change in schema's we did not have this problem with an image field with a large amout of data. We are using the sql server odbc driver.

View 4 Replies View Related

Invalid Locator De-referenced

Jan 25, 2007

I am trying to copy data from one SQL server to another. The data contains big image or texts. After SQLGetData, we used the SQLPrepare, SQLBindParameter, SQLParam and SQLPutData, and SQL_LEN_DATA_AT_EXEC to insert the data.

Our application is unicode based, but the data type in the SQL server is text, so we bind the paramter using SQL_C_WCHAR as C type and SQL_LONGVARCHAR as sql type. But we got the error:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Microsoft claims it has this problem when using the MDAC2.7, see http://support.microsoft.com/kb/325765, but with a different error message.

The SQL server ODBC driver we are using is 2000.85.1117.00, but I got "Invalid locator de-referenced", Not sure if it has been resolved completely or not?

Any possible workaround?

Thank

Strong

View 4 Replies View Related

Cannot ALTER 'dbo.fn_GetDate' Because It Is Being Referenced By Object

Jan 29, 2008

I am trying to modify a function used as default value by many tables but I get the following error:

Cannot ALTER 'dbo.fn_GetDate' because it is being referenced by object '...'.

First, can someone explain me the reason behind this behavior? Why can't the function be altered??
I did the following test:
I create a table with a default value of dbo.fn_GetDate_r().
fn_GetDate_r() simply returns the value of another function fn_GetDate().
Result: I can alter fn_GetDate() without problems...


Like I said, the function I want to modify is used by many tables. Is there a simple way to alter it? The tables don't appear in the dependencies and SQL Compare doesn't seem to be able to detect and drop/recreate the constraints...

Thanks
Frantz

View 11 Replies View Related

DACFx Not Deploying Referenced Database

Aug 26, 2013

Database project  “BaseDatabaseProject” has a single table called “BaseTable.”Database projected “DerivedDatabaseProject” has a single tabled called “DerivedTable” and has a database referenced to “BaseDatabaseProject” where the location is set to “Same database.”Using SSDT for Visual Studio 2012, I right click on DerivedDatabaseProject and select publish. I set the connection to my localhost database and give the database a new name that does not currently exist. If I click the generate script button, I see a script that first adds BaseTable and then adds DerivedTable. When I push databse, my local instance now contains a new database with both tables.

This is not the same result as I have experienced with SQLPackage.exe from SQL Server Data-Tier Application Framework (July 2013). I have been trying to deploy using the SQLPackage and have found that when I do so, it only adds the DerivedTable. Any elements from the BaseDatabaseProject are being omitted. This is evident if I generate a script (the two scripts are otherwise identical aside from the missing part from BaseDatabaseProject) or if I try to publish.The syntax I have been using for SQLPackage.exe is

SqlPackage.exe /a:Publish /sf:[mylocation]DerivedDatabaseProject.dacpac  /tdn:DerivedDatabaseProject /ssn:localhost
SqlPackage.exe /a:Script /sf:[mylocation]DerivedDatabaseProject.dacpac /of:true /tdn:DerivedDatabaseProject /tsn:localhost /op:[mylocation]DerivedScript.sql

Am I missing a needed parameter when I’m running SQLPackage.exe or is this an issue with DACFx?

View 2 Replies View Related

How Can I See What Object/resource Is Referenced To With PAG: 5:1:104 OR RID: 7:1:14567:39??

Jul 20, 2007

Hello,



Can someone tell me how to see what object/resource is referenced to with following statements:

DatabaseID:FileIDageID PAG: 5:1:104

OR RID: 7:1:14567:39

?

is there a statement too see what's on the mentioned pages??





Thanks in advance.

Greetz,



Patrick de Jong

View 1 Replies View Related

Some .Net Assemblies Cannot Be Referenced In Script Task

Dec 14, 2006

I'm creating a script task that needs to reference the Visual Studio Team Foundation Server assemblies, but I'm unable to setup a reference. (Note: the Team Foundation assemblies are part of the VSTS SDK installation.) When designing the script, if I select Project, Add Reference, the .net assemblies for the Microsoft.TeamFoundation namespace are not listed -- even though the assemblies do show up if do the same in a Visual Studio.Net project for C# or VB.

I've also tried to add a reference in a Custom Component Set -- View, Object Browser, Browse, Edit Custom Component Set, and selected a specific assembly. But I get the following error:


---------------------------
Microsoft Visual Studio for Applications
---------------------------
The following components could not be browsed:

C:Program FilesVisual Studio 2005 SDK2006.09VisualStudioIntegrationCommonAssembliesMicrosoft.TeamFoundation.dll


Why can that .dll be referenced in standard VB.Net or C#.Net apps, but not in an SSIS script task?



View 3 Replies View Related

SQL Server 2012 :: List Objects Referenced Outside Of DB

Feb 23, 2015

Need to find the list of objects that are referenced from the outside of current database. Example

Use TestDB1

Create Proc TestProc as begin

select * from TestDB1..Sysobjects

select * from sys.indexes

end

Need a query which able to return the following

LinkedServername, Databasename, TableName

null ,TestDB1 , Sysobjects

View 2 Replies View Related

SQL 2012 :: FK Value Not Exsit In Primary Table It Referenced To

Sep 5, 2015

I am migrating data. I found a strange thing in the existing table, there is a column named workshopCaseID in a TruancyCase table ,The datatype for workshopCaseID is an int (null). it is a FK, there are some records are 0 values, but the fk referenced primary table only have 1-8 values, how are the 0 values get inserted there?

View 6 Replies View Related

Foreign Key Points To Not Most Appropriate Index Of Referenced Table

Sep 23, 2015

The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column. Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).

This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well. Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.

View 4 Replies View Related

Using Correlation Names - Same Table Referenced 4X In Select

May 18, 2015

I have a "case" table describing what type of social worker case and who performed work on the case. Four case workers are referenced within each case and all workers belong to the "worker" table. I am trying to present the case worker name on a detailsView rather then the much simpler task of showing the worker's index stored within the case. I can't seem to get the correlation names working.

SelectCommand="SELECT [ReportID], [RecCreated], tblVCaseType.Description AS cType, [IntakeDate], tblVCounty.Description AS County, [Abuse], [Neglect], [Dependency], [AfterHours],
[Screenout], tblVScreenOutcome.Description AS Outcome, [Closed], [ClosedDate], iWorker.[Worker Name], cWorker.[Worker Name] ,
tblVAssessmentType.Description AS aType, tblVTimeframe.Description AS tFrame, tblVDisposition.Description AS Dispo , tblVProgramXfer.Description AS Xfer,

[Code] ...

Getting only one worker name is easy. But, I need all four. I have tried many versions without success. The error from this version is repeated 4X:

The multi-part identifier "tblVWorker.ID" could not be bound.

View 5 Replies View Related

Package Path Referenced An Object That Cannot Be Found

Nov 17, 2005

I am running Final Relase of 2005 version 9.00.1399.  I built an Integration Services package saved it closed up, came in the next day opened the project and I get 46 Warnings and the message on all of them is similar:

View 4 Replies View Related

Report Against Parent-child Referenced Dimension

Feb 20, 2008

Hello,

I want to create a report in SSRS against a cube in SSAS 2005.

I have a Sales fact table, a Products dimension which is related directly to the fact table, and a Categories dimension which is related to the fact through Products (referenced dimension).

Fact --> Products --> Categories

In the Categories dimension i have also implemented a parent-child hierarchy. Only leaf category members are joined to Products.

Is it possible to create a report, drilling down from parent categories to child categories to products?

I 've already implememted the categories parent-child report but when i add the Products dimension in the flattened rowset, the Products seem to be joined to non-leaf category members.

Thanks.

View 4 Replies View Related

How To Find Dependent/Referenced Objects Of A Stored Procedure?

Jan 31, 2007

Hi Frens,

Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?

Thanks a lot for your time.
Regards,
-Ram.

View 11 Replies View Related

What's The Scope Of The 'global Dts Object' Referenced From A Script Task?

Feb 27, 2006

Is it at the package scope, AppDomain, Process, or OS/Machine scope?

We've started using SSIS from C# in a multi-threaded app and are coming up against some problems that prompted this question.

One application process, one application domain, multiple threads. Each thread is given a package to execute under the control of a manager thread. The thread instantiates a package instance, (loading the package from a file), sets the value of some of the user variables and executes it. Different threads may be given the same package name to execute, but with different values for the variables.

The problem we see is that two different package instances, when running seemingly separate instances of the same package, seem to be sharing the same Dts.Variables collection instance in a Script Task - overwriting the values of ReadWriteVariables set by the other executing package. Sometimes it will fail completely being unable to access a variable.

I'd have expected two package instances to be completely separate w.r.t. their variables - am I missing something?

View 4 Replies View Related

AppSettings From Web.config Cannot Be Referenced In Emailed Report Subscription

May 2, 2007

Server Software:

SQL Server 2000 (all sp's up to date)

SSRS 2000 (all sp's up to date)





We have an error when a report subscription is sent via email.



Our report definitions reference a key in the web.config file so that we can have a single location to reference our department's name in all reports. Our department name changes frequently as our company is bought and sold and it is difficult to manually update 700+ reports.



In report designer we have a text field with the following value expression:

=System.Configuration.ConfigurationSettings.appSettings("Department").ToString()



Our web.config looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

....extra settings truncated for brevity...

<appSettings>

<add key="Department" value="Report created by: Corporate Reporting and Analysis" />

</appSettings>

</configuration>



The reports work perfectly in every aspect accept when they are schedules to be emailed. When the email recipient receives the email (not matter what format, mhtl, pdf, etc...) the department string value is replaced with: #Error



Is this a bug with the report schedule and deilvery component? ..or is there a permission problem when accessing: System.Configuration.ConfigurationSettings.appSettings("Department").ToString() ?



Any help would be greatly appreciated.

Thank you

View 3 Replies View Related

How To Synthesize A Single Row Result From A Cross-referenced Table?

Jan 13, 2008

Hello, I have an SQL problem that is hard to describe so here's an example scenario:

a) company has a personnel database, identifying each person in the company and their assigned role(s).

b) tblPersonnel has a row per person, key is 'PersonnelID'
c) tblRoles is a list of all the roles in the company (administrator, clerk, shipper, truck-driver, etc.), key is 'RoleID'

d) tblPersonnelRoles is a cross-reference of all the roles assigned to each person - in simple format of:

PersonnelID | RoleID


Given all the above, I need to have a select statement that produces one row per person, and list all the fields from tblPersonnel AS WELL AS identifies each role assigned to the person.

I'm no SQL guru .. is this even possible?

I know there are alternative solutions (e.g., to write code that for each Personnel does a lookup in Roles) but for one particular situation I need a single SQL statement, or even Stored Procedure.

Thanks in advance,
Rick Piovesan
Detaya Corp
Aurora, Ontario, Canada

View 1 Replies View Related

Data Access :: Cannot Truncate Table Because It Is Being Referenced By FOREIGN KEY Constraint

Nov 11, 2015

Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-

Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.

View 7 Replies View Related

How To Reset The Identity Counter On A Table Referenced By A FOREIGN KEY Constraint?

Apr 4, 2006

I know that TRUNCATE TABLE can be used to reset the identity counter, but it can't be used on a table referenced by a foreign key. Anybody knows how to do that? Thanks.

View 4 Replies View Related

Reporting Services :: Can The Report Parameters Be Passed To Query Of Referenced Dataset

Sep 29, 2015

So I have a report that uses an MDX query to fetch the main data for the tablix.  Then I have a custom row added which pulls data in via a Lookup function and references a different dataset.  The report has 3 parameters.  Each dataset uses those 3 parameters in its underlying query.  However, the dataset referenced in the lookup function doesnt seem to be updating when change the parameters and re-run the report.

View 2 Replies View Related

SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding

Apr 1, 2015

when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way

View 1 Replies View Related

Http Endpoints For SOAP In SQL Server 2005 - Can The Stored Proc Referenced Do More Than Select?

Feb 17, 2008

I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.

Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?

If so I could develop this entire solution from the database side and not involve our .net programmer!

Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.

I look forward to any response. Thanks!

View 5 Replies View Related

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type

Dec 14, 2007

I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View 1 Replies View Related

Cannot Truncate Table 'Database.dbo.Table' Because It Is Being Referenced By A FOREIGN KEY Constraint..

Aug 23, 2006

Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.

It will run and update with a primary key until I add a foreign key to another database.

Here is my error when running the package when table 2 has a foreign key.

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions

May 12, 2015

Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data

View 1 Replies View Related

DB Engine :: Collation Change And DDL Change In Same Script

Nov 18, 2015

We ran into weird/interesting issue with below details.

Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):

We are using SQLCMD to run DDL script on our product database in below order. That script has below content.

step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)

When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.

View 7 Replies View Related

Can You Change Dbo

Apr 24, 2001

can you change the dbo after you've already made the database?

View 1 Replies View Related

Change PC Name

Jun 20, 2002

Hi:

Actually i'm working with a pc named "my_pc", later i'll need to change
its name to "my_server". Obviously MsSQLServer is afected due to it depends
in the name of the pc, also the master database has all its configurations
and aditional information related to the name "my_pc".

My question is: How can i do the change in MsSQLServer without
reconstruncting the master database?


Thanks in advance.

View 1 Replies View Related

Can You Change It

Oct 15, 2004

Hi

This code is for Access db



strSQL = "SELECT TOP 20 tblThread.Topic_ID, Subject, Username, tblThread.Thread_ID, Message, Message_date "
strSQL = strSQL & "from tblTopic, tblThread, tblAuthor "
strSQL = strSQL & "where tblThread.Topic_ID = tblTopic.Topic_ID "
strSQL = strSQL & "and tblThread.Author_ID = tblAuthor.Author_ID "
strSQL = strSQL & "order by Message_date DESC"




can you rewrite it for SQL Db

with this



strSQL = EXECUTE



thanks

View 3 Replies View Related

How To Change 200 To 2.00

Jan 18, 2005

Hi,

Can anyone help me on this problem please?
When i use sql statement to pull out data, one of the fields will pull out data like 200 or 300 or sumthing like that.
But now i need to pull out the data as 2.00 instead of 200.
How do i do that?
Thanx

View 2 Replies View Related

CHANGE THE SA Name

Sep 24, 2007

HI Everyboody,


How can i change SYSTEM ADMINISTORS NAME plz explain me

cureent my system admin name is 'bindu' it will rename 'RENU' How plz explain me


Thx

Meti BEST OF THE BEST

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved