SELECT DISTINCT Unit.[Unit Name] AS Unit_Name, Cadet.[Cadet ID] AS Cadet_ID, Cadet.[Cadet First Name] AS Cadet_First_Name, Cadet.[Cadet Last Name] AS Cadet_Last_Name, Cadet.[Cadet Middle Initial] AS Cadet_Middle_Initial, Cadet.[MSL Level] AS MSL_Level, Cadet.GPA, Cadet.[How found out about SMP] AS How_found_out_about_SMP, Cadet.[BCT Date] AS BCT_Date, Cadet.[Planned to Contract] AS Planned_to_Contract, Cadet.[NG or AR] AS NG_or_AR, Cadet.[Cadet Email Address] AS Cadet_Email_Address, Cadet.AIT FROM (Unit INNER JOIN Cadet ON Unit.[Unit Name] = Cadet.[Unit Name])
I have this connected with a database.
There is a dropdown list associated with Unit.Unit Name. My problem is....
When you click the drop down to view contents there are a number of items listed the same about of times listed in the Cadet Table. For instance... 101 Airborne is listed 6 times. I only want it listed once.
Any one help me on this? I can give more info if needed.
Does anyone know the column name for the system row identifier. For example Informix uses rowid, for Ingres its tid, in MS SQL server its _______ (fill in the blank).
I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.
So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.
I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.
So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?
Msg 103, Level 15, State 4, Line 3 The identifier that starts with ' "C:Program FilesMicrosoft SQL Server90COM ablediff" -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sour' is too long. Maximum length is 128.
When i split the Identifier as
SET QUOTED_IDENTIFIER ON DECLARE @sFull varchar(1000) DECLARE @s1 varchar(1000) DECLARE @s2 varchar(1000) DECLARE @s3 varchar(1000) DECLARE @s4 varchar(1000) SET @s1= '""C:Program FilesMicrosoft SQL Server90COM ablediff""' SET @s2= ' -sourceserver "VSNET1" -sourceuser "sa" -sourcepassword "sysadm" -sourcedatabase "PMSTEMP" ' SET @s3=' -sourcetable "DEMAT" -destinationserver "VSNET1" -destinationuser "sa" -destinationpassword ' SET @s4=' "sysadm" -destinationdatabase "PMSRESTORE" -destinationtable "DEMAT" -f "c:Diff"' SET @sFull = @s1 +@s2 + @s3 + @s4 EXEC XP_CMDSHELL @sFull
this executes fine but the optput is as
The input line is too long. NULL
What is this? how could i execute such long commad using CMDSHELL?
Is there a field in the SQLException class that identifies what specific exception was thrown. I tried Number and ErrorCode but those seem to change randomly, they are not consistent. Thanks
I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int Or uniqueIdentifier? Or is it all the same??
Hi there,I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.Any help would be greatCheersStu
Hello!I work with oracle, mySQL but im completely new to SQLserver.How can i set a auto-increment unique variable for userID ?After deleted user, the userID should be never used again on a new user.Thank you,
use projectserver2003 selectr.RES_NAME, p.PROJ_NAME, a.TASK_NAME, w.WWORK_START, w.WWORK_FINISH, w.WWORK_VALUE from MSP_WEB_RESOURCES r, MSP_WEB_ASSIGNMENTS a, MSP_WEB_PROJECTS p, MSP_WEB_WORK w join MSP_VIEW_PROJ_TASKS_ENT TE on r.WPROJ_ID=TE.WPROJ_ID join MSP_VIEW_PROJ_RES_ENT RE on r.WPROJ_ID=RE.WPROJ_ID join MSP_VIEW_PROJ_PROJECTS_ENT PE on r.WPROJ_ID=PE.WPROJ_ID wherew.WWORK_TYPE = 1 -- actual work andw.WASSN_ID = a.WASSN_ID anda.WPROJ_ID = p.WPROJ_ID anda.WRES_ID = r.WRES_ID
This statement is returning the following errors:
Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "r.WPROJ_ID" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "r.WPROJ_ID" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "r.WPROJ_ID" could not be bound.
I have all tables identified; however unclear as to why it cannot be bound.
if exists (select 'x' from obj where new_obj.key1 = obj.key1 and new_obj.class = obj.class) BEGIN set @temp_old_ref = (select obj.rowid from obj where new_obj.key1 = obj.key1 and new_obj.class = obj.class) SET IDENTITY_INSERT new_obj ON insert into new_obj(rid, key1, class, is_searchable, is_deleted, is_loaded, old_rid, old_ref) select rid, key1, class, is_searchable, is_deleted, is_loaded, old_rid, @temp_old_reffrom new_obj delete from new_obj where old_ref != @temp_old_ref END
while running it i m getting this error
Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 35 The multi-part identifier "new_obj.key1" could not be bound. Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 35 The multi-part identifier "new_obj.class" could not be bound. Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 37 The multi-part identifier "new_obj.key1" could not be bound. Msg 4104, Level 16, State 1, Procedure mergeChanges, Line 37 The multi-part identifier "new_obj.class" could not be bound.
I've found lot of other people experiencing the same problem I'm having, but I can't get any of their solutions to work for me. I have two tables with the exact same structure Today and Yesterday. I'm trying to compare a price column from Yesterday's column against Today's and I'm getting the 'The multi-part identifier '' could not be bound' error. Here's the code I'm using:
DECLARE @ProgramName varchar(100) SET @ProgramName = 'AffiliateName'
SELECT CJ_RawImport_Today.* FROM CJ_RawImport_Today WHERE (CJ_RawImport_Today.ProgramName = @ProgramName) AND (CJ_RawImport_Today.RetailPrice < CJ_RawImport_Yesterday.Price)
Does anyone know if there is a read-only unique identifier of a database in a given server instance? E.g. a value stored somewhere in the database meta data that is generated during CREATE DATABASE...
I need to develop a strong licensing solution based on the database accessed... Currently our solution is easily hackable because the the license information is kept in the database of your choice and is not dependant on some static information, the current encryption key is static and kept in the software so it can be hacked easily. What i want to do to change this is simple in nature but i don't know how to get that one particular info i need. I want to get some kind of unique identifier for a database (catalog) that sql server could generate. This info must be static and must not be movable. If for example, someone would backup and restore, this information would not be transfered with the backup. Thus, a user that backups his database and restores it on another database server or another database catalog even on the same server would corrupt his license since the Unique ID returned by the SQL Server would be different; the hashing code would change and thus the decryption would fail. Is there any such info i can query using SQL that will not require admin rights?
HI Gurus, Trying to alter an existing Trigger on an insert event on one of our tables. The following sql generates: "Msg 4104, Level 16, State 1, Procedure insxECSIBHEADER, Line 4 The multi-part identifier "INSERTED.PROJECT" could not be bound." Here's the SQL itself: set QUOTED_IDENTIFIER ONgoALTER TRIGGER [insxECSIBHEADER] ON [dbo].[xECSIBHeader] AFTER INSERT AS BEGIN IF SUBSTRING(INSERTED.[PROJECT],4,2) = 'DR' BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + 'D' + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END ELSE BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END END
I am trying to execute a query in SQL SERVER 2005 for calculating days difference for each. I created a function because there are a lot of calculations. In SPeriods table I have 4 fields that I want to pass as parameters in the table-value function TFC_date_diff, but I receive the error "The multi-part identifier "CC.start_period_no" could not be bound. Is there a solution for this ? SELECT CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year, TFC_date_diff.dates_differnceFROM dbo.[SPeriods] AS CC CROSS JOIN dbo.TFC_date_diff(CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year) AS TFC_date_diff_1 Thanks a lot in advance!
Howdie y'all, I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one.... CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO I actually would like to get value of the UserId column for the newly added record. Can anyone of you folks help me with this? Cheers, Wes
I have the following statement with multi-part identifier error :-
SELECT #t.vno,transact.vdesc,transact.acctno,transact.camt,transact.damt,transact.ccamt,transact.cdamt into #main FROM transact,( SELECT VNO,VTYPE,TDATE,SUM(CAMT) AS SCAMT,SUM(DAMT) AS SDAMT FROM TRANSACT WHERE YEAR = 2007 and batchno = 5 GROUP BY VNO,VTYPE,TDATE having sum(camt) <> sum(damt) )as #t WHERE (transact.YEAR = 2007)
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
Hello,I am in the midst of converting an Access back end to SQL Server Express.The front end program (converted to Access 2003) uses DAO throughout. InAccess, when I use recordset.AddNew I can retrieve the autonum value for thenew record. This doesn't occur with SQL Server, which of course causes anerror (or at least in this code it does since there's an unhandled NULLvalue). Is there any way to retrieve this value when I add a new recordfrom SQL server or will I have to do it programmatically in VB?Any direction would be great.Thanks!
Hi guysI'm having a nasty problem with bulk copying into a table that hasunique identifier column. I'm coding on C++, using ODBC driver.I'm coping from a file containing UID description like this:{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}(I also tried a non-bracket version)and in the DB table I get:4233347B-4235-4433-452D-353238302D34which seems random at first sight, but it is:[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.and my question is: what the hell?my code look like this:if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)ret = -1;if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,SQLUNIQUEID, colNo) == FAIL){ret = -1;}(I also tried a VARLEN version:)if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){ret = -1;}and then stuff like sendrow ans save:if (bcp_sendrow(m_hDbproc) == FAIL)return -1;if (bcp_batch (m_hDbproc) == -1)return -1;I also tried specyfiling the column type in the m_hDbproc handle asSQLUNIQUEID, but either I'm doing something wrong, or this just isn'tthe way of a bulk copy samurai:INT * pValue=new INT;INT *pLen=new INT;*pValue=0x24;bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);So like, PLEASE help me on this. I need to get this working by lastmonday :]Thanx, M.
HiI currently have a problem with registering a new server in my EnterpriseManager on a new PC.I need to connect to a MSSQL2K server on a windows 2003.My current PC is on the same LAN as the server and has no problemregistering the server in Enterprise manager. This PC uses NT (about time Ichange :-)The new PC runs WIN2K. But this PC is on another LAN and has to access theserver through a firewall. Port 1433 is open in the firewall.I use a Firewall-1 authentication agent to access the server.I use SQL authentification to logon (sa account), I have checked that thenew clients network libraries are the same version as the servers.In the client network Utility I have set up an alias with the server I wantto access.The registration seems to access the SQL server, but comes out with an errorstating: "invalid attribute/option identifier"When I search KB with this message, I get nothing. Did anyone in hereexperience anything like this? Any pointers to help me move on would begreatly appreciated.best regardsRené Pedersen
Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.
I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".
   INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname)    SELECT KontaktID, AnredeID, Titel, Nachname    FROM [Susi].MSCMS.Core.tKontakte AS Client    WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);
I have the following code and I am getting the error Multi-Part identifier not found. I know its a problem with the code highlighted in green. Can anyone help?
Select a.Code 'Product Code', a.Description 'Product Description', PackSize, NetWeight, GrossWeight, CubicVolume, SupplierOwnCode, a.SupplierCode, b.[Name] 'Supplier Name', e.Description 'Product Type Description', LeadTime, os.description, sum(pl.QuantityOrder) 'Order With Supplier', 0 'On Order By Customer', sum(pl.QuantityOrder) - 0 'Total On Order' from Product a inner join Supplier b on a.SupplierCode = b.code inner join ProductType e on a.TypeID = e.ID inner join PurchaseOrderLines pl on po.code = pl.PurchaseOrderCode LEFT join PurchaseOrders po on a.code = pl.productcode LEFT join orderstatus os on po.orderstatus = os.id AND os.id = 2 where a.code = @ProductCode group by a.Code , a.Description , PackSize, NetWeight, GrossWeight, CubicVolume, SupplierOwnCode, a.SupplierCode, b.[Name], e.Description , LeadTime, os.description