I have made following code.my problem is the loop stops if the table doesn't exists,
Private Sub GetOrderNR()
Dim CompanyID(5) As String
CompanyID = Company.SelectedItem.ToString().Split("-")
SQL.ExecQuery(String.Format("SELECT ordernr as 'myOrdernr' FROM [myDB].Main.myOrders as tt where Companynr={0}", CompanyID(1).Trim()))
[code]...
I want the loop to continue instead either alert an msgbox
Jord writes "Hi,
Is there a way to retrieve or display the queries that I've run against a database? I didn't save them and now I need to know what I've done.
Thanks so much in advance.
Jord."
Hi, I need to build a query that just includes certain columns if they haven't NULL row values.For example:SELECT NAME_COLUMN IF not NULL, SELECT GENDER_COLUMN if not NULL, otherwise won't select these 2 columns i used as example.Any help, will be greatly appreciated,Best regards,Teixeira
View 3 Replies View RelatedHi,
I have application in which i am performing synchronization between SQL Server 2000 and SQL Server 2005 CE.
I have one table "ItemMaster" in my database.There is no relationship with this table,it is standalone.I am updating its values from Windows Mobile Device.
I am performing below operations for that.
Step : 1 Pull To Mobile
Code BlockmoSqlCeRemoteDataAccess.Pull("ItemMaster", "SELECT * FROM ItemMaster", lsConnectString,RdaTrackOption.TrackingOn);
Step : 2 Using one device form i am updating table "ItemMaster" table's values.
Step : 3 Push From Mobile
Code BlockmoSqlCeRemoteDataAccess.Push("ItemMaster", msConnectString);
So i am getting an error on 3rd step.
While i am trying to push it says,
"The Push method returned one or more error rows. See the specified error table. [ Error table name = ]".
I have tried it in different ways but still i am getting this error.
Note : Synchronization is working fine.There is not issue with my IIS,SQL CE & SQL Server 2k.
Can any one help me?I am trying for that since last 3 days.
'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
Hi all,
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
Hello,
the following alter table statement:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED
is answered with:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.
which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.
Note that:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL
DOES get executed, and
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY
produces the same error.
Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.
So - anyone an idea?
This is the error that I got. Is it a Resevered Table name or something
Invalid column name 'LastLoginDate'.Invalid column name 'LastLoginDate'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'LastLoginDate'.Invalid column name 'LastLoginDate'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I'm trying to send a Test Email to Operators defined in a SQL 7.0 server (currently on SP2). And get the following error message:
"Error 22022: SQLServerAgent Error: MAPI error: Table too big."
We've checked the email profiles (also performed test on SQL MAIL) and can stop/start SQL Mail successfully. Verified that the accounts are correctly identified with appropriate permissions. MSDB is 150MB with no table that looks abnormal in size.
Has anyone come across this situation? Please let me know. THANKS!!!
Hi,
I am new to SQL and i am trying to create a table from a table. below is my query but im getting an error
CREATE TABLE Production.TransactionHistoryArchive1 AS
(SELECT ProductID,SUM(Quantity) QuantitySum,SUM(LineItemTotalCost) TotalCostByID FROM Production.TransactionHistoryArchive
GROUP BY ProductID)
error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
Hi. An evaluation of Quest Log Reader threw up the following message on a particular database: 'Table Error: DBCC PAGE (1:111067) (object ID 0, index ID 0) is out of the range of this database'.
Running DBCC CHECKDB shows no errors however I did have a hardware problems on the server last week, now resolved. The question is do I actually have any corruption since DBCC CHECKDB doesn't report anything? If so how do I determine where it is?
Hope someone can point me in the right direction.
Regards,
YaHozna.
1 HIS_HTTP_LOG a partition table2 REL_HTTP_LOG not a partition table,the same structure of HIS_HTTP_LOGļ¼3 When HIS_HTTP_LOG doesn't exist any index the following executed succeed
ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03] ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997') ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3
4 However when I added the index in HIS_HTTP_LOG and execute the step 3,It made error: a) CREATE INDEX IDX_HIS_HTTP_LOG_001 ON HIS_HTTP_LOG(USERID)ON PS_HIS_HTTP_LOG (STARTIME) b) ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03] ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997') ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3
========================= Error messages================================================"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TMP_HTTP_LOG SWITCH ' for the index 'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' ."
When I added index in REL_HTTP_LOG ,it gave me the same error message
Could you tell me how can I solve the problem !
Hi: I have writtern the following procedure,
while running the following procedure
EMP_SP_EmployeeDetails_AdminDisplay 'A1','UserName','Asc ' .I have got the following table( same record is repeated).Can any one send me the correct procedure.
My Procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EMP_SP_EmployeeDetails_AdminDisplay]
(@colum as varchar(50),@order as varchar(50),@EmpID as varchar(25))
AS
BEGIN
declare @sqlexec varchar(1500)
set @sqlexec='SELECT EH.EmpID, EL.Username,EL.Password,EL.Confirmpwd, E.FirstName, E.LastName,DE.Designation, DE.DesignationID, D.DepartmentID,D.Department, E.Sex,
E.DOB, E.Age, E.Address, E.Email,
''ContactNo''=Case When E.phone is not null Then E.phone
When E.phone is null Then E.Mobile End,
E.DateOfjoining, EH.HobbyID
From EMP_Tbl_Employee E, EMP_Tbl_EmployeeLogin EL, EMP_Tbl_Designation DE, EMP_Tbl_Department D,EMP_Tbl_Employeehobbies EH, EMP_Tbl_Hobby H
where E.EmpID='''+@EmpID+''' and E.EmpID=EH.EmpID and E.DesignationID=DE.DesignationID and E.DepartmentID=D.DepartmentID and E.Sno=EL.Sno and EH.HobbyID=H.HobbyID order by '+@colum+' '+@order+''
print(@sqlexec)
END
Query Result:
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
A1 kumar kumar kumar kumar k SoftwareTrainee EITA1 EJT Testing Male 1982-05-05 00:00:00.000 26 Coimbatore kumar@gmail.com +256-236-5623 2008-03-01 00:00:00.000 H1
hi all when i try to insert a record to sql table i have this error
ncorrect syntax near 'japan'.
the code part iscmdInsert = New SqlCommand("insert into empbill values(" & Val(eno.Text) & "," & contry1.Text , db)
cmdInsert.ExecuteNonQuery()
the value i entered is 123.japan
i have 2 field in empbill (emp,cont)
what happend
Hi,
i have a table called fundperformance and i opened that table in design view and deleted a column and added another column to that table and when i try to save the changes i made to it i get the below error.
'Fund' table saved successfully'FundPerformance' table- Unable to create index 'PK_FundPerformance'. The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FundPerformance' and the index name 'PK_FundPerformance'. The duplicate key value is (1).Could not create constraint. See previous errors.The statement has been terminated.
what should i do to fix it.
Thanks
Karen
Hi - I am trying to do the following simple insert that fails...
insert into tbloutgoingobms_hold
select aOBMMessageID, nGlobalCaseID, nOBMailerID,'Food','05-JUN-2002'
from tbloutgoingobms
The table that I am inserting into is a duplicate of the table I am selecting from plus I added two new columns - thus I am inserting constant values into those two new columns: 'Food','05-JUN-2002' . This insert works in Oracle but when I run it on SQL*Server I get the following error: An explicit value for the identity column in table 'tbloutgoingobms_hold' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I used the command: SET IDENTITY_INSERT tbloutgoingobms_hold ON
The column that the 'Food' constant is going into is a varchar(10) and the date column is a datetime column.
Still no luck.... Help please - this cannot be such a difficult thing to do... :(
Thanks is advance,
Nancy
Hi Everybody,Can any one tell me how to solve the error 8908(Table Corrupt error). Iam getting this problem every 6 weeks or so. Chain linkage mismatch problem.Help in this regard is highly appreciated.Thanks in advance.
Monu.
Dear All,
I was trying to create a table with many columns. However, I got the following error. Have anyone seen this error before?
>>>>>
Warning: The table 'PDMS_USER' has been created but its maximum row size (9118) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
<<<<<
Thanks.
I'm creating the temporary table below...
CREATE TABLE #TempTest(P_ID int not null,
Type_1 varchar(1000) null default('N/A'),
Type_2 varchar(1000) default('N/A'),
Type_3 varchar(1000) default('N/A'),
Type_4 varchar(1000) default('N/A'),
Type_5 varchar(1000) default('N/A'),
Type_6 varchar(1000) default('N/A'),
Type_7 varchar(1000) default('N/A'),
Type_8 varchar(1000) default('N/A'),
Type_9 varchar(1000) default('N/A'),
Type_10 varchar(1000) default('N/A'),
Type_11 varchar(1000) default('N/A'),
Type_12 varchar(1000) default('N/A'),
Type_13 varchar(1000) default('N/A'))
When I attempt to dump data to it I get the followig error...
The total row size (13052) for table '#TempTest' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Is there any way to get around this as the field I'm taking the data from is varChar(1000) and I don't have any problems with that?
Thanks in Advance
We are running a application that uses SQL. No users use this database but the application. We have been getting an error stating that there was an error inserting into one of my tables. We have increased the LE Threshold to 10000 and have also increased my locks to 200000 but it still doesn't get rid of the problem. Any ideas as to why. I can't imagine that this application will hold that many locks but then again I'm not the developer. Any suggestions will help.
Ben
I'm creating a table variable, then executing it in an EXEC statement, like this:
DECLARE @temp_table TABLE (
column1 INT,
column2 INT
)
SET @strInsert = 'INSERT INTO @temp_table (column1,column2) VALUES ('+@intValue1+','+@intValue2+')'
EXEC (@strInsert)
I get a message during the EXEC statement that I must declare @temp_table. When I do this using an actual temporary table (i.e. #temp_table) instead, it works fine. Is there a way around this so I can use the variable instead of the table?
Hello All,
One of my clients is accessing SQL Enterprise Manager via
Citrix. He is getting an error message 'Unexpected error occured during this operation' while opening tables and displaying all rows in Enterprise Manager.
When I try the same thing through Enterprise Manager installed locally on my PC, I do not get any errors.
Has anybody faced a similar problem?
I am quite new to SQL Server. Please help.
Thanks in advance
Sumathy
I renamed a table, and now whenever I try to insert data into the table I get this error:
Invalid object name 'dbo.OldTableName'.
For the life of me I can't find anything that uses the old name. There is a trigger and a couple indexes, but as far as I can see they all use the new name?
Hi all,
I made a C# function to insert new row to MS SQL Server 2005. However, I kept getting this error when I executed it:
Code:
Line 1: Incorrect syntax near '0'.
Unclosed quotation mark before the character string '','{ 0 }','Sun, Mar 25, 2007 04:33:00 PM')'.
here is the sql insert statement
Code:
insert into Temip_tmp_3(Managed_object, Alarm_type, Perceived_Severity, Probable_Cause, Domain, Additional_Text, Specific_Problems, Original_Event_Time ) values ('','EquipmentAlarm','Major','EquipmentMalfunction','Domain tsel_ns:.dom.radio_ericsson','"EXTERNAL ALARM RECEIVER FAULT *** ALARM 121 A2/IO_DEV BTAK1 042/0700H U 070325 1633 EXTERNAL ALARM RECEIVER FAULT AP APNAME NODE NODENAME 1 AP_BTAK1_C A AP_BTAK1_A APNODE FCODE B FAULT CODE 23 **SpecificProblem:121 , AdditionalInfo:@@"','{ 0 }','Sun, Mar 25, 2007 04:33:00 PM')
I believe there is not a single quotation mark unclosed in my query. And, what's so funny is when I tried to execute the query on SQL Server Enterprise Manager, it worked.
I don't know, maybe I missed something.
Can you guys please help me figure this out?
Thank you very much in advance.
Shinta
Hi
I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:
DECLARE @ERRCODE INT
CREATE TABLE x
SET @ERRCODE = @@ERROR
This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.
Is there a way that I can capture the error using @@ERROR and still let the script run ?
Thanks in advance
Mickster
Hi all,
I have a table that looks something like this: -
county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby
I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -
County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES
I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'
SELECT @sql
exec sp_executesql @sql
SELECT * from ey_report_temp
--------------------------------------------
DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)
DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0
begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'
exec sp_executesql @sql
end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp
drop table ey_report_temp
This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation
(1 row(s) affected)
(0 row(s) affected)
update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
(0 row(s) affected)
One of my co-worker told me I can do this to capture errors and insert into error table but when I test it, it doesn't work. Here is what I try to accomplish. SQL 2012. In reality, I have more complicate queries than below.
1. Insert data FROM SourceEmployee INTO Employee table and capture emp_id and error msg insert into dbo.##temperror table
2. Continue on the process until no more record. Basically, skip the error records and do a while loop until end of record.
--DROP TABLE dbo.Employee;
CREATE TABLE [dbo].[Employee]
(
[emp_id] [int] NOT NULL,
[last_name] [varchar](20) NULL,
[first_name] [varchar](15) NOT NULL,
[code]....
I am trying to update one of the databases Jobs table Via an asp page because the amount of text in the field is too large to to be added via the DTS import wizard. I keep getting the following error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Jobs'.
The table Jobs definately exists in the database. I double checked the System DSN to make sure that it was pointing to this database. What do I need to look for to fix this?
oops, Forgot to show the sql statement this is using, I am using ADO to insert the data vs using an Update Query because of the number of apostrophe's etc in the variables.
SELECT description FROM Jobs WHERE JobID = 1
Miranda
OK I have tried just about every combination over the past 3 days to get this working and have gotten close but just can't seem to get past this.
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[DepartmentNumber],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data],
[UserName],
[DomainName]
)
Select
[DepartmentNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3),
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[Events].*
from [Events]
join EventstoLog ON dbo.Events.EventID = dbo.EventsToLog.EventID
quote:Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
The Select with the Join at the bottom work great by itself.
The whole script without the Join works.
But add the Join to the Insert Into script and it fails.
Can someone look at this and point me in the right direction??
Also, below is most of the table strustures just in case.
USE [EventLogs]
GO
/****** Object: Table [dbo].[Events] Script Date: 12/01/2006 18:15:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Events](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [EventLogs]
GO
/****** Object: Table [dbo].[SecurityEvents_Temp] Script Date: 12/01/2006 18:15:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SecurityEvents_Temp](
[DepartmentNumber] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [EventLogs]
GO
/****** Object: Table [dbo].[EventsToLog] Script Date: 12/01/2006 18:16:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventsToLog](
[EventID] [int] NULL,
[EventDescription] [char](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Thank You,
John Fuhrman
http://www.titangs.com
hi,
i want to create table using another table but i am getting the following error.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
my query is:
CREATE TABLE errorlog
AS SELECT * FROM log where 1=0
becos i a trying to copy the structure of the table log to table errorlog.please rectify my problem,please