I am having a SQL Server 2005 problem with my Insert statement. I am
sending a command via my website to my database. It comes up with an
error I'll put below. The code is here:
"INSERT INTO tblUsers (userName) VALUES ( userNameTest)"
This is the error I get:
The name "userNameTest" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
Now, userName is a varchar field in the database. What is wrong?
To all, I have previously read from and saved to Access databases, but now decided to attempt to learn more about using SQL databases. I took an ASP.NET page I had previously used to save data to an access database and attempted to repurpose it to be used with a SQL database with the same content. I get an error that I have spent the past 3 hours researching on the internet. I am unable to find a solution that fits my code formatting. Any help that could be given would be greatly appreciated. When I run my ASP.NET page I get the following error:"The name 'newsID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." The code I am using is shown below:---------------------------------------------------------------------------------------------------------------------Dim objCommand As OleDbCommandDim strSQLQuery As String strSQLQuery = "INSERT INTO news " _& "(ID, newsDate, newsComp, newsInfo) " _& "VALUES (newsID, newsDate, newsComp, newsInfo)" objCommand = New OleDbCommand(strSQLQuery, objConnection) objCommand.Parameters.Add(New OleDbParameter("newsID", OleDbType.Integer))objCommand.Parameters.Add(New OleDbParameter("newsDate", OleDbType.DBTimeStamp))objCommand.Parameters.Add(New OleDbParameter("newsComp", OleDbType.VarChar))objCommand.Parameters.Add(New OleDbParameter("newsInfo", OleDbType.LongVarChar)) objCommand.Parameters("newsID").Value = "1"objCommand.Parameters("newsDate").Value = "11/06/2006"objCommand.Parameters("newsComp").Value = "Company Name"objCommand.Parameters("newsInfo").Value = "A description of the news items here" objConnection.Open()objCommand.ExecuteNonQuery()objConnection.Close()---------------------------------------------------------------------------------------------------------------------
I'm pulling data from one database in which the headerID is the primary key which automatically increases by one on each insert. Modifying all of the other fields from one data type to another and then inserting them into another database after the convertion is done. I keep getting this error.
The name 'HeaderID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Hi Everyone, I've run into a problem I have not seen before, and a Google search did not turn up any useful info. So, I'm back to ask for help here! I have created an INSERT statement and several columns are boolean. When I try to execute the INSERT, I get the following error: The name "False" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. I've found that this is Error MSG 128. Also, I'm executing the command as a transaction. The first command is a DELETE followed by an INSERT. The DELETE is working fine, but the INSERT is causing this error. Here is the code for assembling the 2 SQL statements. string[] SQLCommands = new string[RecordCount+1]; int SQLCount = 0; StringBuilder stb = new StringBuilder();
stb.Append("DELETE FROM UserRights WHERE UserIDPtr = '"); stb.Append(TargetUserID);// Guid value stb.Append("';"); SQLCommands[SQLCount++] = stb.ToString(); stb.Remove(0, stb.Length);
foreach (MyApp.UserRightsRow row in TargetUserRights) { stb.Append("INSERT INTO [UserRights] "); stb.Append("([UserIDPtr], [AllCustomersFlag], [Customer], [CustomerFlag], [Region], [RegionFlag], [Plant], [PlantFlag], "); stb.Append("[Building], [BuildingFlag], [Area], [AreaFlag], [Active], [UpdatedBy], [Updated]) "); stb.Append("VALUES ('"); stb.Append(row.UserIDPtr);// Guid value stb.Append("', "); stb.Append(row.AllCustomersFlag.ToString());// bit stb.Append(", "); stb.Append(row.Customer.ToString());// int stb.Append(", "); stb.Append(row.CustomerFlag.ToString());// bit stb.Append(", "); stb.Append(row.Region.ToString());// int stb.Append(", "); stb.Append(row.RegionFlag.ToString());// bit stb.Append(", "); stb.Append(row.Plant.ToString());// int stb.Append(", "); stb.Append(row.PlantFlag.ToString());// bit stb.Append(", "); stb.Append(row.Building.ToString());// int stb.Append(", "); stb.Append(row.BuildingFlag.ToString());// bit stb.Append(", "); stb.Append(row.Area.ToString());// int stb.Append(", "); stb.Append(row.AreaFlag.ToString());// bit stb.Append(", "); stb.Append(row.Active.ToString());// bit stb.Append(", '"); stb.Append(row.UpdatedBy);// Guid value stb.Append("', "); stb.Append(row.Updated);// DateTime value stb.Append("); ");
SQLCommands[SQLCount++] = stb.ToString(); stb.Remove(0, stb.Length); } The method that performs the SQL Transaction is: public bool DoTransaction(string[] SQLStatements) { int i; int SQLStatementCount = SQLStatements.Length;
using (SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("cnMyApp"))) { using (SqlCommand command = MyConnection.CreateCommand()) { SqlTransaction transaction = null;
try { MyConnection.Open();
transaction = MyConnection.BeginTransaction();
command.Transaction = transaction;
for (i = 0; i < SQLStatementCount; i++) { command.CommandText = SQLStatements[i].ToString(); command.ExecuteNonQuery(); }
transaction.Commit(); return true; } catch { transaction.Rollback(); throw; } finally { MyConnection.Close(); } } } } Any help with this would be greatly appreciated!! Thanks!
Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows
=,<,>.................................Below is my query declare @WorkOrder as nvarchar(10) Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')
SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date', M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal, isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +
I've done some programming before, but i'm pretty much a novice in C#. I recently installed Visual Studio 2008 Express Edition. For some reason, when i came to a point to make a connection with an SQL Sample database "NORTHWIND.SDF".... An error message show up saying "access to database file is not permitted". What causes it.???
"Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."
However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...
but when I change the selected index of the listbox, then this error comes up:
System.Data.SqlClient.SqlException was unhandled Message="An error occured while setting up a connection with the server. At making a connection with SQL server 2005, this jamming is possibly caused by the fact that at the standardinstitutions of SQL server no external connections have been permitted (provider: Provider of named pipes, error: 40 - no connection with SQL could open server)"
If the error sounds a bit weird, sry I tried to translate as most of my errors come in my language
someone knows what the problem is and a solution to it?
(btw sry if this is in the wrong part of the sql forum, but there are so much parts, I didn't know which to choose lol )
I have a table that I am an dbo owner and when I try to move a column from the last to the first position in Design table mode and click to save, I am getting the following warning:"Saving changes is not permitted. The changes you have made require the following tables to be dropped or re-created.  You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to the re-created"..how to deal with this error so that I can move my column in a table Design mode. I am on SS 2008 R2.
Hi.. I tried to setup MSSQL2000 trans replicatiom using push subscription method. My Publisher is also my distributor. But whenever I started the dstribution agent, I got the following error. It has problem connecting to subscriber's DB with the following error. I had tried that the login is 100% fine. But why this error appear ? Any help?
The name ' ' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. (Source: Subsriber (Data source); Error number: 128)
I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.
=================
Executing the query "BACKUP DATABASE [XXXX] TO [XXXX] WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The backup of the file or filegroup "sysft_XXXX_FT" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I would like to configure the permitted hosts setting for the Report Server. According to MSDN, the Syntax is:
 <PermittedHosts>          <HostName>Adventure-Works.com</HostName>          <HostName>hotmail.com</HostName>     </PermittedHosts>
In case of having only one host, is the <HostName> tag mandatory? I was not able to find more information regarding the xsd on which the configuration file is based. To be more specific, is it possible to define the PermittedHost like this or will this result in an error?
 <PermittedHosts>Adventure-Works.com</PermittedHosts>
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
Hi, I need to build up dynamically cursor and fetch variables in a script. I use exec(@sql) to declare the cursor and my hope was I could use the same method for doing the fetch. Unfortunately I run into a
SET @sql ='DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET(' + @apo+@int_provider_name+@apo+ ',' + @apo+@int_provider_string+@apo + ',' + @apo+@int_query_string+@apo + ') FOR READ ONLY' PRINT @sql EXEC(@SQL)
OPEN c1
set @fetch = 'FETCH c1 INTO '+@int_fetch print @fetch EXEC (@fetch)
... snippet
Output: DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:work2007schenker.xls','SELECT * FROM [Datei$]') FOR READ ONLY
FETCH c1 INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24
Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@c1".
How can I declare the dynamically built 'fetch into' variables (@c1 etc.) so they are in the context of dynamic sql ?
I have a web application that I need to search based on what the user entered in the input box. e.g when the user enters in the box something like "Brain Boom"
I need to search the column in the DB table where there is anything word like Brain or has Boom or all the above. How will I accomplish this?
There are some concept about context switch block me .
---executor: dbo create user u1 without login
go
create table t
(
col int
)
go
create proc proc1
with execute as 'u1' as
insert into t values(1)
go
exec proc1
I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'
To my surprise, the "exec p1" command could run successfully..
I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.
I was trying out the Building an End-to-End Application (VB.Net) exercises on Web Matrix Guided Tour and encountered the mentioned errors. Please help. Thanks.
' TODO: Update the ConnectionString and CommandText values for your application Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
Dim CommandText As String = "select OrderID, OrderDate, CustomerName from Orders"
Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet() myCommand.Fill(ds)
MasterGrid.DataSource = ds MasterGrid.DataBind()
End Sub
Sub BindDetailGrid()
' get the filter value from the master Grid's DataKeys collection If MasterGrid.SelectedIndex <> -1 Then
' TODO: update the ConnectionString value for your application Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
' TODO: update the CommandText value for your application Dim filterValue As String = CStr(MasterGrid.DataKeys(MasterGrid.SelectedIndex)).Replace("'", "''") Dim CommandText As String = "select OrderDetailID, ProductName, Quantity, UnitPrice from OrderDetails where OrderID = '" & filterValue & "'"
Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlCommand(CommandText, myConnection)
Cannot generate SSPI context. 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.
I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:
However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).
I am writing a trigger to audit changes to certain columns. In the trigger I would like to record to the "Audit" table as much information about the current execution context as possible (current user, login, nt user, inputbuffer, etc.). I couldn`t find the way to find out what is the currently executed* stored procedure. @@PROCID returns object id for the trigger itself. Any help would be highly appreciated as this is an urgent production environment issue.
We have a sqlserver 7 on windows nt 4. We'd just change a startup account for sqlserver agent to a not domain admin for security reason. The startup account for sqlserver service is still domain admin. We are now getting the following error every 5 second in our application event log.
"SQLServeragent security context does not have server-autorestart privileges"
Is there a way to get rid of this error without putting a domain admin as a startup account for sqlserver agent?
All of a sudden I cannot connect to sql server from Visual Studio. Nor I can't conenct to Sql server through IIS and it gives me the same error saying "Login failed: Cannot Generate SSPI Context". Lots of my development projects got stuck because of the SSPI Issue. I know there was a network upgrade from Windows NT (users) to Active directory. I can only access the sql server from the host machine.if someone can guide or give me a quick run down on things to look for that would be wonderful.
Inserting info into a table, where one of the columns meets a certain criteria.
insert into employee (/*emp_id,*/ fname, lname, start_date, dept_id, title, assigned_branch_id) values (/*null,*/ 'Michael', 'Smith', '2001-06-22', (select dept_id from department where name = 'Administration'), 'President', (select branch_id from branch where name = 'Headquarters'));
But I'm getting this error:
Msg 1046, Level 15, State 1, Line 5 Subqueries are not allowed in this context. Only scalar expressions are allowed.
I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.
The package needs to be executed on a regular basis.
I have given RO access for a regular user on production DB, he is executing the package from his client desktop.
I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.
I understand from the above experience that there is an “execution context� for SSIS execution. Can someone tell me how can I define the execution context for SSIS?
------------------------ I think, therefore I am - Rene Descartes
HiMy company uses a Windows 2000 server with MS SQL Server 2000.We have many clients with administrative software that use the MS SQLserver, the problem is that we experimented randomly the error"Cannot Generate SSPI Context" and the only way to work with DB is logout the client.I'm looking the MS Technical sheet but there's no resolving hints thatcan help.Anyone has a similar problem ?Thanks.
Win Server 2003 SQL Server 2000 SP4 SharePoint Server
Win Server 2000 (Domain Controller / Active Drectory)
Clients: Win XP / Access XP
I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.
The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified: 1) date and time 2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2)) 3) Not using cached credentials
we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.
Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?
I am trying to loop through databases to gather information.
However if i loop though datbases in a cursor, by using a select from sysdatabases, once i am in the cursor and SET @Sql = 'use ' + DBName and EXEC(SQL), the database context never changes from the one the cursor is executing in.
Is there a way to loop through database contexts and execute sql ?
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true"); SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.
If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.
The event log shows me:
The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
Running a SQL Profiler trace shows me:
Error: 3910, Severity: 16, State: 2 Transaction context in use by another session.
This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150
I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
