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:
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?
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.
Hi i am creating a web page which will return the results of products, the store name and the distance of the store from the postcode which the user has put into a text box. My first question is how would my database structure look, below is what it looks like at the moment; Company; companyID, companyName, companyAddressLine1, companyAddressLine2, companyPostcode. and Products; productID, productName, companyID, price. In order to achieve the proximity search how would i alter the "Company" table, as in what columns will need to be added or removed, thank you.
Hi i am creating a web page which will return the results of products, the store name and the distance of the store from the postcode which the user has put into a text box. My first question is how would my database structure look, below is what it looks like at the moment;
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?
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
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...
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
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?
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?
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.
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.
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:
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.
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?
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?
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:
<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() ?
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
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.
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.
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.
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
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 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?
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.
HI, We upgraded to SQL Server 2005 Standard Edition for our ASP.NET 2.0 website. We were using SQL Server Express 2005. That worked fine. Now we are unable to connect to the database. I have googled, but I just cann't figure out what is going on. Any help is appreciated. Here is the error. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) In our firewall sqlbrowser.exe and sqlservr.exe are allowed. Thanks Matt
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 1Invalid 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... ???
When trying to connect to a remote SQL 2005 Express Server, I get this error message: [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection. I can remotely connect to the server with the same username and password using osql in command and I can also connect to the server remotely with SQL Server Management Studio installed on this machine. Here is my connection string: Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=uid;Initial Catalog=EBM;Data Source=xxx.xxx.xxx.xxxSQLEXPRESS;Use Encryption for Data=False The same connection string works if connecting locally, via changing the ip address to the machine name: Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=uid;Initial Catalog=EBM;Data Source=machinenameSQLEXPRESS;Use Encryption for Data=False
I get a Invalid Column Name ' '. with this procedure. Can anyone see what migh be wrong?
Thanks,
SELECT A.CompanyName,C.FirstName,C.LastName,C.Client_ID, CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName] ELSE A.[CompanyName] END AS DRName, C.Client_ID FROM tblClients C INNER JOIN tblClientAddresses A ON C.Client_ID = A.Client_ID WHERE (C.Client_ID = 15057) AND (A.MailTo=1) AND Convert(varchar(5), GETDATE(), 10) BETWEEN Convert(varchar(5), A.Startdate, 10) AND Convert(varchar(5), A.Enddate, 10) OR (A.Startdate Is Null) AND (A.EndDate Is Null) GO
