I have a table which have several fields. I have a field that is made required by mistake. I would like to change this field to not require. Can someone help with the syntax?
i am running sql server 2000 on windows 2000. i have a need to export a view and delimit the columns with double quotes as it has imbedded commas in the columns, how do i do this??
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' tableĀ in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
I've got a SQL Reporting Server 2000 SP2 report that takes 3 parameters. FromDT, ToDT, and LocationCD. The first two parameters are free form text fields that expect a date. The last one is a drop down box. For some reason, when I'm viewing the report through the standard reports folder on the report server I have to click the "View Report" button two times to get the report to render. Clicking it just once, doesn't seem to do anything. The report is a line graph.
There are default values in the FromDT and ToDT parameter fields.
Anyone have any ideas what would be causing the need for the second click?
We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated
I'm using BCP for some command line SQL statement. The file I'm generating is comma delimited. Some of my records contain comma's in them and require them to be in quotation marks. What is the parameter for BCP to put those entries that have commas in them to have those records be in quotation marks?
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily. this section shows the problem, i am loading xml from a file and trying to insert this into the sql database XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close(); pls help
I have problem with sql query with aspnet. In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark => ' . Any suggestions? Thanks.
I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website. The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.
Hi,I've created a table in SQL Server 2000 and I'm now trying to searchthrough the data and return specific rows. I'm using this command:select * from Export where libelle_court='Recherche d'investisseurs'The problem is this: The search fails whenever there is a curlysingle quotation mark within the table field ( ' as opposed to ' ).For example, if the field entry in my table is this:Recherche d'investisseursthen both of the following commands retun no fields:select * from Export where libelle_court='Recherche d'investisseurs'select * from Export where libelle_court='Recherche d''investisseurs'However, if the field entry in my table is this:Recherche d'investisseursthen both of the commands quoted above succeed.How can I get SQL Server to treat the curly quotation mark correctlyand return the right results? I've tried changing the collation butwith no success.Thanks,Rob
Hi i have to pass Query to the SqlCommand with " ' " single quote when i try this i got the error msg Unclosed quotation mark before the character string ')'. insert into service(problem) values('Receipt Printer Can't Work') where service_id = 112; help me Very Urgent
I try to get values from database table name GoldPriceRecord , and display them in a simple labels text. However, i receive the following error: Error: Unclosed quotation mark before the character string ''.
Public LondonDateTime As Date Public CommodityPrice As String Public SysDate As DatePublic CommodityPrice_Check As String Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Dim strsql1 As String Dim strsql2 As String strsql1 = "SELECT TOP 1 Bid, SystemDate FROM GOLDBEST.DBO.GoldPriceRecord ORDER BY SystemDate DESC'" SetGoldPriceRecord(strsql1) fdsa1.Text = LondonDateTime fdsa2.Text = CommodityPrice fdsa3.Text = SysDate fdsa4.Text = CommodityPrice_Check End Sub Public Function SetGoldPriceRecord(ByVal strsql As String) Dim connString As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection As New SqlConnection(connString)Dim myCommand As New SqlDataAdapter(strsql, myConnection) Dim DS As New DataSetmyCommand.Fill(DS, "GoldPriceRecord") <---- Error CommodityPrice = CType(DS.Tables(0).Rows(0)("Bid"), String)CommodityPrice_Check = CType(DS.Tables(0).Rows(0)("Bid"), String) SysDate = CType(DS.Tables(0).Rows(0)("SystemDate"), Date)LondonDateTime = CType(DS.Tables(0).Rows(0)("SystemDate"), Date) myConnection.Close() End UsingReturn LondonDateTime Return CommodityPriceReturn SysDate Return CommodityPrice_Check End Function
Hi, My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer. Here is my problem query: SELECT * FROM OPENROWSET('MSDASQL', 'DSN=SHADOW', 'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'
I am getting this error: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '2007'.
I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
I have an app that I created and I am trying to upload the MS SQL DB to my web host. I downloaded MS SQL Server Database Publishing Wizard and then open up Visual Studio 2005. I right click my database, and then try publishing it. I convert to a MS 2000 DB (was originally 2005). I save the .sql statement and try copying into the host's (Godaddy) query analyzer. (It's only about 400k). Well, I tried cutting and pasting the doc so I could see exactly where I get the error. I get a good part of it successful, but then I try the pasting the code below (not modified at all) and get the error about "unclosed quotation..." I skip this and go to the next process, and is successful, but often, I am getting this error.... WHY? Please help. Code copied directly from generated script from MS Publishing wizard:
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int OUTPUT AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 DECLARE @ErrorCode int DECLARE @RowCount int SET @ErrorCode = 0 SET @RowCount = 0 SELECT @UserId = u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE u.LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName IF (@UserId IS NULL) BEGIN GOTO Cleanup END -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set IF ((@TablesToDeleteFrom & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V'')))) BEGIN DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set IF ((@TablesToDeleteFrom & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set IF ((@TablesToDeleteFrom & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set IF ((@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0 Cleanup: SET @NumTablesDeletedFrom = 0 IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END' END GO ***************************ERROR:****************************
Unclosed quotation mark before the character string 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranS... /****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int ASBEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSESET @TranStarted = 0 DECLARE @ErrorCode int DECLARE @RowCount int SET @ErrorCode = 0 SET @RowCount = 0 SELECT @UserId = u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE u.LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName IF (@UserId IS NULL) BEGIN GOTO Cleanup END -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set IF ((@TablesToDeleteFrom & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V'')))) BEGIN DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set IF ((@TablesToDeleteFrom & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set IF ((@TablesToDeleteFrom & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set IF ((@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0Cleanup: SET @NumTablesDeletedFrom = 0 IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCodeEND' ENDGO
Hello, Can anybody help me with this error , System.Data.SqlClient.SqlException. "Line 1: Incorrect Syntax near :'m' ."
I have a textbox named DESCRIPTION and am trying to insert the values entered by user in this textbox.
It works well if a user types " I am unable to " but it throws this error when a user types " I'm unable to "...because of I'm .. How do i fix this?
My Query:
strSQL = "Insert into ABC(DESCRIPTION) values('" & strDescription & "')"
When i try below query in query analyser , am getting this error .... Error 1: [Line 1: Incorrect syntax near 'm'.] Error 2: [ Unclosed quotation mark before the character string '.]
update ABC set DESCRIPTION = 'I'm unable' WHERE ABC_ID = '142'
<WebMethod()> Public Function getCertificateInformation( _ ByVal cerNumber As String, _ ByVal StudentID As String) As DataSet Dim cnn As New SqlConnection( _ "user id=sa;password=;" & _ "initial catalog=uni2;data source=(local)") Dim strSQL As String strSQL = "SELECT cerNumber, name," _ & " address, stId, year FROM Msc" _ & " WHERE cerNumber='" & cerNumber _ & "' AND stId='" & StudentID when running this i am getting an error "System.Data.SqlClient.SqlException: Unclosed quotation mark before the character string '12'". isn't the sql statement correct? need help plz! ** 12 one of the input
right im programming a database program and i need some code. (the database is in sql/msde) This is what i am attempting to do: I have a page with textboxes on it with buttons at the bottom i need to program the database with the add function
this is the code that i have already Imports system.data.sqlclient
'You need two objects
Dim conn as new sqlconnection dim comm as new sqlcommand
conn.connectionstring = "Your UDL String here" comm.text = "insert into books (isbn, title, author, publisher, description, price,) 'books is the name of the databse values ( '1234' , 'Title', 'Author', 'Publisher' ) conn.open comm.connection = conn comm.executenonquery comm.close conn.close
now i think i need to insert this at the top of the page but im not 100% sure (btw i have the udl string)
so what do i need to add to this code to make the page submit data that has been enterd into textboxes into a database table(sql/msde) on the click of the button?
1) I am facing the problem of database suspect how do I solve the problem.
2) When I tried to cofigure my SQL mail I am not getting the option or Icon of SQL MAIL in the enterprise manager. I think it probably got deleted , how do I retrieve it .
For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK. I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList. I am wondering how can I add an identity PK to my view? TIA,Jeffrey
I have a SQL table that consists of columns A, B and C. I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column. This computed column has the following logic: If B is blank or null then NewColumn = A + ' - ' C else NewColumn = A + ' - ' + B I am just wondering how the SQL statement should look like....
Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it. Ne help pls !!! Any yes the data is saved because when I use the "select" command I can print the data.
*first issue how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1 as select tab.col1,tab.col2 from from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
I'm using SQL Server 2008 to solve the following exercise.
I have three Tables: Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID Table Shipper with columns:ShipperID,Company,Phone
I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders