I have a table like the following
Table=Items
Type / Name
3 / Active Item
3 / Active Item
4 / New Item
4 / New Item
5 / Old Item
5 / Old Item
How do I run a querry to update the Type Colum entrys from 5's to 3's? I have tried the following:
UPDATE Items SET Type = 3 WHERE Type = 5
The above querry passes SQL Querry analyzer, but the querry itself provides the following error: Unable to parse query text.
I think the error is due to the fact that I am using a circular reference to update the same Column that I am testing on.
MS-SQL 2005 SP-2
I have a hypothetical question. Suppose you have three database tables. One represents the moneyin a cash register. This money can be composed of Dollars or any amount of change. At any particular time you might have no dollars in the register but only change, or both or vice versa. To represent this I created three tables. One for Currency, Dollars and Change. My database isset up so that there is a one to many relationship between the table CashRegister and Dollars. And also a one to many relationship between CashRegister and change. Is this alright? Or is this somehow a circular reference?
CashRegister-----------CurrencyID(PK) Dollars --> this table represents dollar amounts-------DollarsId(PK)typeOfCurrnecyAmount Change --> this table represents coins------ChangeIdAmounttypeOfCurrnecy MoneyType --> table Indicates the type of currency, change or dollars---------typeOfCurrnecy(PK)
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)
UPDATE MyDB1.dbo.Users SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID FROM MyDB2 INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email
I don't want to have to create a linked server. It's on the same box.
Hey! I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files. To achieve this ive created te following tables (truncated for clearity): #################### Sites ##################### ID [Int] ## ... #################### #################### Folders ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... #################### #################### Files ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... #################### Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both! Then i set up the foreign constraints as follows:Folders.FolderD -> Folders.IDFolders.SiteID -> Sites.IDFiles.FolderID -> Folders.IDFiles.SiteID -> Sites.ID All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible) Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?
I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).
The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.
However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.
I hope that I have explained this well enough.
I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution??
I am getting desperate, so any suggestions will be welcome
I have a problem that looks like it has not been discussed before inthese groups.I have a simple SQLAgent job that runs sp_who (could be anything, butlet's just say sp_who for this example). I have set the jobstep towrite to an output file "T:out.txt". If the job is owned by anadmin, it runs fine and writes the output file. If it is owned by anon-admin user, it gets the following error msg:Warning: cannot write logfile t:out.txt. Error 1059 : Circularservice dependency was specified. The step failed.I know about setting up the SQLAgent CMDExec proxy account, and havedone that. In fact, both SQLAgent and the SQLAgent cmdexec proxy usethe same domain account, which is in the administrator group of thelocal server. So, I know that security is not the issue.When a simple job runs and writes to an output file, what service orservice group could it be trying to start or modify? I looked throughthe list of Services, and could not find any circular dependencies.Is there a utility to detect this? Why would running under onecontext (as an admin) be ok while the other context (non-admin on SQL,but using the same admin domain service account) fails?Thanks in advance for any info you might have.
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
DECLARE Loan_cursor CURSOR FOR SELECT Loan_No,store FROM loan WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch. FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN Declare @LoanNo varchar(12) ** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Loan_cursor END
CLOSE Loan_cursor DEALLOCATE Loan_cursor
when l run the cursor l get the error Server: Msg 207, Level 16, State 3, Line 15 Invalid column name 'Loan_No'. . If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15 The column prefix 'loan' does not match with a table name or alias name used in the query. All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main as begin set nocount on
--First sp exec Data_transfer_sp
--Second sp Exec Clearance_sp
set nocount off end
In data_transfer_sp,i have commented the select statement but still iam use to the select result while execution the main sp. Note:I have compiled the data_transfer_sp after making comment .Txs in advance
Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code? It happens on this line: MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields. Dim MyAdapter As SqlDataAdapter Dim MyCommandBuilder As SqlCommandBuilder Dim DetailsDS As DataSet Dim Row As DataRow Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _ ClientIDSent Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr) MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) MyCommandBuilder = New SqlCommandBuilder(MyAdapter) MyAdapter.Fill(DetailsDS, "tblClient")
I am access SQL 7 via ADO in some ASP pages. My database objects were created under a user name aliased to the dbo. As a result, when I have my client logon to make a connection, I have to preface all my object references with the name of the owner, i.e., Select * from mydboname.people . Is there a way to avoid having to prefix all my transact sql statements with the owner?
I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
As I posted earlier, I am relatively new to TSQL. I have been given a few books: O'Reilly SQL on SQL Server 2005 O'Reilly SQL in a Nutshell Mastering SQL Server 2005 - Sybex Beginning SQL Server 2005 Programming - Wrox
I have a copy of SQL2005 Developers Edition on a development server. So I think I have some decent tools at my disposal.
I was wondering if there are any other sites or forums that might be suggested for learning SQL programming? Right now, I am just making stuff up and following examples in books, etc. I would like to find a site where there are beginner level projects for no other purpose than educational. Something where I can get more hands on of the design and programming aspects. Just to practice.
CREATE TABLE s (sno VARCHAR(5) NOT NULL PRIMARY KEY, name VARCHAR(16), city VARCHAR(16) )
CREATE TABLE p (pno VARCHAR(5) NOT NULL PRIMARY KEY, descr VARCHAR(16), color VARCHAR(8) )
CREATE TABLE sp (sno VARCHAR(5) NOT NULL REFERENCES s, pno VARCHAR(5) NOT NULL REFERENCES p, qty INT CHECK (qty>=0), PRIMARY KEY (sno, pno) )
when i run this query i get an error saying:
Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24 43 37
if i want to know the chain for item no 3 then it shall give following 37
if i want to know the chain for item no 1 then it shall give following 12 24 43 37
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello. I'm getting an error - "Object reference not set to an instance of an object" when running an SSRS report with graphs. It only happens when the returned dataset is empty (based on parameters set by the user, it is possible to return no rows). I've tried using the NoRows property (I entered "No Data" in the property box), but I still get the same error. Is there another way to account for an empty dataset in charts?
I would like to use a custom build class library written in C# inside of the vb script. Does anyone know how to add the reference to the scriptComponent project once you open script through design script button?
Given a UDT, is there any way to get a reference to the table where the specific instance is running ?
IE: Let's suppose we have defined a UDT named UDTPoint; now we define two tables: ATable & BTable, wich both have one column that is defined as UDTPoint.
When an insert/update/delete operation on ATable or BTable occurs, the UDTPoint class needs to verify in which context it is running (ATable or BTable) before doing operations on data.
Hi, I wanted to add a reference to an assembly in my report (not a DLL, but an EXE). As I see there is no option to select an assembly which is an EXE. Firstly, is it possible to add a reference to an EXE (I guess not)? Secondly, If no, why is it not possible?
