SQL 2012 :: Select Permission Error On Insert Statement
Jul 28, 2015
Have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.
View 6 Replies
ADVERTISEMENT
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Mar 9, 2008
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View 9 Replies
View Related
Jul 17, 2001
Hi,
I'm try to execute the Bulk Insert statement from my script but i receive this particular message :
"Permission denied. Only members of the sysadmin role can use the BULK INSERT statement."
How can i set the permission to allow my script to run the bulk insert statement? Thanks in advance.
View 1 Replies
View Related
Nov 4, 2015
I have a VB.NET scheduled job as a task scheduler ( windows 2012) that calls a stored procedure that bulk inserts. I have added the user in the server role "bulkadmin" yet I get the "You do not have permission to use the bulk load statement error"
System.Data.SqlClient.SqlException (0x80131904): You do not have permission to use the bulk load statement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
[code]....
View 0 Replies
View Related
Mar 9, 2005
Hello
My hosting company created a database for me a few day ago.
To make queries into a database,I made aspx page with textarea for query text and two radio buttons for select/insert.
I can create a table but if i make a SELECT or INSERT statement I get permission denied error.In that error database and owner data are the same as my database and user ID I use in a connection string.
So why dont I have permissions?
I created an account in webmatrixhosting to test my application and there everything works just fine.
So I tried to use "sp_tables" query before I make any table.
On my hosting company I got nothing but in webmatrixhosting
I get 16 rows of SYSTEM_TABLES and 2 rows of VIEWS.
Did my hosting company correctly set up my database?
They say they did but I Im not so sure.
Please Help
Thank You
Greeting from Slovenia
Daniel
View 3 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Sep 27, 2007
Hi,
I granted all the rights on the database to the user (db_owner, public, db_datawriter, etc...) However, I didn't grant the "System Administrators" role. I also specifically granted the select on those tables (sysobjects and sysindexes) for the user and checked through ‘sp_helprotect sysobjects’ command whether there are no specific revokes for for that user.
However, the user is still getting the below error while trying to expand the "Tables" view in the Enterprise Manager.
ERROR 229: SELECT permission denied on object 'sysobjects', database 'My_test', owner 'dbo', SELECT permission denied on object 'sysindexes', database 'My_test', owner 'dbo'.
Also, the user claims that he can’t seem to do anything with the database, he can’t view any objects, and, when he goes to Query Analyzer, if he tries to run a SELECT query on a table (that he know that this table exists), he gets this error:
SELECT permission denied on object 'tblBillingTrans', database 'My_Test', owner 'dbo'.
Any help would be greatly appreciated!
Thanks,
Alla
View 7 Replies
View Related
Sep 19, 2007
SQL Server 2005 anomoly?
In SQL Server Management Studio I granted specific permissions to user "A" to do Select, Insert, Update, Delete on Table "B" -
When I logged on as User "A" and attempted the Insert imto table "B" I got the following error:
"Insert Permission Denied on Table B, Database C, Schema dbo"
Is this a problem with the dbo schema?
Then I went back and created a stored proccedure "D" with the exact same Insert statement inside the procedure. I granted User "A" execute permission on the stored procedure "D".
I then logged on as User A and executed Stored Procedure "D". No Problem - stored procedure executed fine with the Insert.
I attempted the Insert statement again - straight SQL - as User "A" and got the same error as above ("Insert Permission Denied.....")
Strange behavior - cannot do a SQL. Insert even though user has permissions but can execute a store procedure with the same Insert statement.
What gives?
View 2 Replies
View Related
Mar 13, 2003
I have following codes :
insert into table1 (lastname,firstname)
select ##Newrows.LAST, #Newrows.FIRST, from ##newrows
if @@error > 0
begin
select @ReturnError = 91001
end
Will this return @@error = 0 if insert fails but select is successful?
Thanks in advance.
-jfk
View 1 Replies
View Related
May 27, 2008
I have a select statemnet that I want to insert new rows if the data is not found from table 1 into table 2. How can I add onto this statement? What is the sql code neede?
Select *
From tbl_Data_OpenOrders
WHERE EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND
tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND
tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE)
Lisa Jefferson
View 7 Replies
View Related
Feb 22, 2007
Hi,I have a very simple issue: for simplicity lets say I have 2 tables, A andB.- Table A contains 5 fields. Amongst these there is a 'id'-field whichis but a reference to table B.- Table B contains 2 fields: 'id' and 'text'In order to post data to table A I thus (from a known text value that shouldmatch 1 value in B.text) have to get the value of B.text before performingthe UPDATE/INSERT statement.How is this possible?I would have thought something likeINSERT INTO A (val1, val2, val3, ID, val4)VALUES ('x1','x2','x3', SELECT id FROM B WHERE [SOME TEXT VALUE] = B.text,'x4')however this is not possible, so I'm lost - not experienced in the arts ofSQL:-)Hope someone can help.Best Regards,Daniel
View 6 Replies
View Related
Feb 24, 2007
Hello All,
INSERT INTO [SPIResConv5].[dbo].[Batch]
([BATCH_NO],
[OPENDT]
,[USERID]
,[MODULE]
,[USERBATCH]
,[RESORT_ID]
)
Select [BATCH_NO],
[OPENDT] = getdate(),
'Hwells' as [USER_ID],
'1 CASH RECEIPT' as [MODULE],
[USERBATCH],
Resort_ID = Case Resort_ID
when 2 then 'Ell'
when 3 then 'CSI'
when 12 then 'Ell2'
when 13 then 'ATR'
end
from TransactionTempToTransaction
where Resort_ID = 3 or Resort_ID = 2 or Resort_ID = 12 or Resort_ID = 13
needing help in modifing the statement above
The first would be to grap from the batch table the last [BATCH_NO] and add one number to it-and insert --just that number €“for all records imported to Batch Table. Example: last record was 50 then 51 is the batch for all recordes imported.
Second: [USERBATCH] is a varchar, but need to insert based on current date: Say for example -today 2/23/2007 but need to insert in format = '2/23 LBX'
Last and again thanks for the help-
Resort_ID-For the Batch table only need to import how many rows,
Based on how many different resorts or in the table TransactionTempToTransaction
For example-- if there are two diffrent resorts €“ only import two rows
With the last field Resort_id being the only difrrent field showing the resort_id. If six different resorts or in tabe TransactionTempToTransaction then six rows.
Thanks for your
JK
View 1 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Mar 19, 2008
Hi,
When I am trying to drop a user using following statement
--First remove access from all databases
Set @SQL =
'
USE [?];
if ''' + @login_name + ''' in (Select name from sysusers )
EXEC [?].dbo.sp_revokedbaccess @name_in_db = N''' + @login_name + ''';
'
Exec sp_msforeachdb @SQL
Print 'Access Removed.'
exec @ret_value=master.dbo.sp_droplogin @login_name
I am getting an error,
User has been dropped from current database.
User has been dropped from current database.
Access Removed.
No permission to access database 'model'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'sysjobs', database 'msdb', owner 'dbo'.
Login dropped.
The SQL Version I am using is
-------------------------------------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Please help me to solve this issue.
Mujeeb.
View 5 Replies
View Related
Apr 14, 2005
Hi,
I am trying to run the following insert statement, but am gettng an error. The table I want to insert to
(DimensionMonthTime_hold) has an
Identity column defined for its key.
The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by.
I've tried to specify the column names on the insert line, but haven't figure that out.
Any suggestions?
Thanks
Jim
----------------------------
Insert into DimensionMonthTime_hold
select distinct substring(period,1,4) + substring(period,6,2),
substring(period,1,4) ,
case substring(period,6,2)
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
else 1
end,
substring(period,6,2),
case substring(period,6,2)
when '01' then 'First Quarter'
when '02' then 'First Quarter'
when '03' then 'First Quarter'
when '04' then 'Second Quarter '
when '05' then 'Second Quarter'
when '06' then 'Second Quarter'
when '07' then 'Third Quarter '
when '08' then 'Third Quarter '
when '09' then 'Third Quarter '
when '10' then 'Fourth Quarter'
when '11' then 'Fourth Quarter'
when '12' then 'Fourth Quarter'
end,
case substring(period,6,2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April '
when '05' then 'May'
when '06' then 'June'
when '07' then 'July '
when '08' then 'August '
when '09' then 'September '
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end,
1,
getdate()
from transaction
where Account_type ='A'
------------------------
View 6 Replies
View Related
Jan 14, 2005
Hey,
I am not sure how to really explain this, but I'll give it a try.
I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement:
SELECT Column1, Column2, Column3
FROM #Temp1
The result set is:
Column1---Column2---Column3
99--------6756756---55555
44--------55---------NULL
Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this:
Column1---Column2---Column3
NULL------NULL------55555
99--------6756756---NULL
44--------55---------NULL
Another example:
The returned result now is:
Column1---Column2---Column3---Column4
99---------6756756---55555-----NULL
42---------55---------NULL------12345
So I need:
Column1---Column2----Column3----Column4
NULL-------NULL-------55555------NULL
NULL-------NULL-------NULL-------12345
99---------6756756----NULL-------NULL
44---------55----------NULL-------NULL
Does this make sense, and/or is it even possible?
I know it could be more of a presentation thing, but I would like to know how to do it in the code behind.
Thanks
View 2 Replies
View Related
Jun 9, 2007
is there anyway i can merge select statement and insert statement together?
what i want to do is select few attributes from a table and then directly insert the values to another table without another trigger.
for example, select * from product and with the values from product, insert into new_product (name, type, date) values (the values from select statment)
View 3 Replies
View Related
May 30, 2006
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View 1 Replies
View Related
Jul 20, 2005
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
View 1 Replies
View Related
Jan 2, 2008
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:
NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate
ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email
DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender
I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.
I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.
What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.
The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)
Any help would be appreciated,
Thank you!!
View 3 Replies
View Related
Aug 20, 2007
hello everybody
I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue:
We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems.
I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10:
select * from cweb.DBNAME.dbo.TableNAME
Or
insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c10.DBNAME.dbo.TableNAME
All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements.
So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails
C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this:
insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2
It fails with the following 2 error messages:
"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done
The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."
I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID.
I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid.
So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server
So in summary the problem is this:
1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange)
2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it
"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done
The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."
I have been searching thru google and forums but havent found any solutions yet.
Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_?
thanks a lot
Helkyn
View 1 Replies
View Related
Nov 15, 2007
Hello,
I have a trigger on a table named Store. The trigger updates the longitude and latitude on store based on the zip. Simple right? Well, I'm trying to import data and of course the trigger is not updating the data as triggers are not on a row by row basis with multi row inserts.
Here is the error message I'm receiving:
Msg 512, Level 16, State 1, Procedure SetLongLat, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How would one go about resolving this issue? I've includes my snippets below:
Trigger:
Code Block
ALTER TRIGGER [dbo].[SetLongLat]
ON [dbo].[Store]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Long float
DECLARE @Lat float
SELECT @Long = LONGITUDE, @Lat = LATITUDE
FROM Zipcode..ZipcodeLite
WHERE ZIP_CODE = (SELECT Zip FROM Inserted)
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
IF UPDATE(Zip)
BEGIN
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
END
END
Thanks for your help!
Nathan
View 5 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Dec 19, 2014
Is there a way to set a default value for a sp parameter using a select statement(see code bellow)
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event
View 9 Replies
View Related
Jan 26, 2015
Project has 2 tables process(parent) and processchild(child).
Project workflow recorddsany changes to these tables as a history.
I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).
Here is example.
Process table
PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...
ProcessChild table
PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..
I want to find out all the processes where processchildid=2 and processchild.status =1
View 3 Replies
View Related
Apr 6, 2015
Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1
I get this output---> "Apr 6 2015 4:07PM"
But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?
View 3 Replies
View Related
Aug 4, 2015
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...
View 5 Replies
View Related
Apr 20, 2015
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
View 1 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 6 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Aug 29, 2006
hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor
View 5 Replies
View Related
