I have two tables - Projects and ProjectStruct
Table Projects contains master records of the projects, ProjectStruct
allows to define a project herarchie and contains the fields
PrjStructId, ProjectId, PrjStructName, ..., ParentId
PrjStructParent contains a reference to the parent or to itselves if
record is top-level-record for a project.
I try to create a trigger on table Projects (INSERT) which
automatically creates the top-level-entry in ProjectStruct but I
didn't succed.
Tried to use (several variations similar to)
INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)
SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))
FROM INSERTED
but this inserts a reference to the last inserted record. Why this
happens is pretty clear to me, but I found no way to get the reference
to the identity column of the record currently inserted.
Test #1 Returns 20971 rows * 25 = 524,725 Test #2 Returns 14169 rows * 37 = 524,253 Test #3 Returns 6808 rows * 77 = 524,216 Test #4 Returns 5140 rows * 102 = 524,280
With the similarity of the total byte count returned, I would assume that a buffer or something is being overrun, is this a configuration parameter possibly.
If I perform the select against the linked table as such
Select * (for fields list) From LURCH_PARADB.S102D4LM.PARADB.BLHDR
I get all rows returned. The issue only exist when accessing the views that are created against the linked server.
need help please on update only if . i need to update only the field "val_holiday " (in table B from table A) and olso to check on table B the "ID" + "new_date" only if exist and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1 only the row that update in table B
Code Snippet select from table A update table B WHERE ..........................HOW ?
----------------------------------------------------------------- table A ID fname new_date val_holiday field_check_update_if _ok ---------------------------------------------------------------------------------------------------
----------------------------------------------------------------- table B ID fname new_date val_holiday ----------------------------------------------------
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
UPDATE t1 SET t1.name = t2.name, t1.age = t2.age FROM DB1..Person t1 INNER JOIN DB2..PersonToo t2 ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation. 1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters... It all sounds very messy, and I still don't know how Any ideas?
Hi All, Can anyone tell me how to create a reference for composite key. For ex, I have created tblEmp table successfully. create tblEmp( empId varchar(100), RegId varchar(100), empname varchar(100),constraint pk_addprimary key(empId, RegId) ) And now, I am going to create another table which references the composite key.create table tblAccount( acctId varchar(100) primary key, empId varchar(100) references tblEmp(empId), RegId varchar(100) references tblEmp(RegId) ) But it gives error like Server: Msg 1776, Level 16, State 1, Line 1There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors. Could anyone please let me know how to create reference for composite key. Thanks in advance,Arun.
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins ( AdminID INT NOT NULL PRIMARY KEY, SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID) )
CREATE TABLE SecurityGroups ( SecurityGroupID INT NOT NULL PRIMARY KEY, CreatedBy INT NOT NULL REFERENCES Admins (AdminID) )
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.
E.g. present query:
Insert Prices select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C where M.asset = 'money' and C.zDate not in (select zdate from price where sid >=15)
So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)
Hi, Everyone. i am wondering if there is some way to hide a table row depending on the page number. I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. These are the values i need to update
old value new value 7007 4007 7008 4008 4008 7 7009 4009 7011 4011 4011 ' ' 7010 4010 4010 1 7016 4016 4016 1 4506 4006 4512 4012
how do I write one query that will accomplish this?
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say Location1 : C:LoadFilesImport Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3, Second Set: VD_1, VD_2, VD_3, Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1 AP_2 goes to Table2 AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201 If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION]( [ID] [int] IDENTITY(1,1) NOT NULL, [USERID] [int] NOT NULL, [STARTTIME] [smalldatetime] NOT NULL, [ENDTIME] [smalldatetime] NOT NULL, [REASON] [nvarchar](250) NULL, [PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this; command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime); insert PERMISSION output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object. Here are some clipits of what you need. Imports SQLDMOPrivate myServer As New SQLServer Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString() Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString() Dim storedProcedure As New StoredProcedure Dim qresults As SQLDMO.QueryResults Example Stored Procedure Try myServer.Connect(serverName, userName, password) storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) Label1.Text = "Creation of stored procedure successful"Catch ex As Exception Label1.Text = "Creation of stored procedure failed" Finally myServer.DisConnect()End Try Run Stored Procedure and Obtain Results myServer.Connect(serverName, userName, password) qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows MemberRowNo = qresults.GetColumnString(num, 1) Next myServer.DisConnect()
CREATE ASSEMBLY asmRANDRTCalendaringGateway FROM 'E:sqlRANDRTCalendaringGateway.dll' WITH PERMISSION_SET = UNSAFE
I get this error ...
Msg 10301, Level 16, State 1, Line 1 Assembly 'RANDRTCalendaringGateway' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Even though I do not explicity refer to system.web in the DLL's code ...
using Microsoft.Win32; using System; using System.Data.Sql; using System.Data.SqlClient; using System.Collections.Generic; using System.Configuration; using System.Text; using log4net; using ADODB; using MAPI; using CDO; using System.Runtime.InteropServices;
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion. As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ? CREATE TABLE [dbo].[LoginsExpiry]( [LoginName] [varchar](50) NULL, [ExpiryDate] [date] NULL, [Roles] [varchar](500) ) ON [PRIMARY] GO
I was hoping to instantiate a channelfactory from a clr stored proc in order to send messages to a wcf endpoint but VS 2005 wont allow refs to anything other than what it calls "SQL Server" targets. Is calling a wcf endpoint from clr sp's possible? I've alreay read the article on serialization under ss2005 but dont believe it has the answer to this particular piece of the puzzle.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
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?
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.
The table above is my users table. It allows for a user to be at multiple sites or multiple locations within a single site or multiple sites. Would it be wise to use a auto incrementing primary key instead of the 3 column composite key? The reason I ask is because if I am referencing this SU table (which I will be a lot), a lot more data would be replicated to the tables which have the foreign key to this table, right? But if I used a single incrementing column as the primary key, only a small integer would be used as the foreign key, saving space?Does this make sense?
I have 2 databases in sql server. let us say database1 = db1 and database2 = db2 Now both the databases have a same table called table1 with the same fields.
IF data in db1.table1 is updated then data in db2.table2 should be updated automatically.
There are many ways we can do this. one way is to create a INSERT trigger on db1.table1.
But i would like to avoid trigger Is there something in SQL server where I can just link table1 of db1 to db2 and delete the table1 in db2. That means db2 is using the same table that of db1.
I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).
I would like to know if it's possible in MS SQL to redirect a table into another DB instance? I have no access into the source code and I have been wondering if it is possible to make a redirection/hard link in MS SQL side.
I would have a table in my base DB and when a query is made into this table, the MS SQL would redirect it to another DB or table. Is this possible in MS SQL ? Something along the lines of a hard-link in linux. Thank you very much.
Just installed VS 2005 & SQLServer 2005 clients on my workstation. When trying to create a new Integration Services Project and start work in the designer receive the MICROSOFT VISUAL STUDIO 'Object reference not set to an instance of an object.' dialog box with message "Creating project 'Integration Services project1'...project creation failed."
Previously I had SQLServer 2000 client with the little VS tool that came with it installed. Uninstalled these prior to installing the 2005 tools (VS and SQLServer).
I'm not finding any information on corrective action for this error.
Hi All, The problem is about cross reference. 1. I have a third party cross reference store procedure SimpleXTab CREATE PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */exec sp_executesql @sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor open xcursor Fetch next from xcursor into @Col While @@Fetch_Status = 0Begin set @Sqlstr = @Sqlstr + ", " set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col + "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'') set @Sqlstr = @tempsql Fetch next from xcursor into @Col End /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */ set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable + @XWhereString + ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql Close xcursor Deallocate xcursor set @tempsql = N'Drop Table ##temptbl_Cursor' exec sp_executesql @tempsqlprint @tempsql /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr exec sp_executesql @Sqlstr if @@rowcount = 0 select 'No Records found'GO 2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function. how can i modify SimpleXtab to avoid using math function but still can generate cross reference. exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
I need to write a query that requires respective fields referencing from multiple tables. For example, here are the tables: Main Table: InfoID Team1 Player1 Team1 Table: Player_ref Player Team_Player_ref Player1 John doh Table: Team_ref Team Team_Player_ref Team1 My Team
Ideal result Table from query: InfoID Count John Doh 1 My Team 2
Any suggestion to creat the Ideal Results table from query? Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance. --daydreamstuck at the current problem
I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.
Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...
DECLARE check_contact_fields CURSOR FOR SELECT field_id, column_name FROM contacts_fields OPEN check_contact_fields FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name WHILE (@@FETCH_STATUS = 0) BEGIN set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted' exec sp_executesql @SQL set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted' exec sp_executesql @SQL if (select * from ##DeletedData) <> (select * from ##InsertedData) select * from ##InsertedData FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name END CLOSE check_contact_fields DEALLOCATE check_contact_fields
drop table ##DeletedData drop table ##InsertedData
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'deleted'.
