Sql server 2005 How do I edit a text field (or ntext) in SQL Server 2000 or 2005 using the GUI?

There doesn't seem to be a good way to edit longer text columns in the SQL Server Managers for SQL Server 2000 or 2005. While SQL Server Manager is really not for editing data in your db, what other tool does Microsoft provide that would normally allow you to do this? Every other field is pretty easy to edit, except long text fields. In Access, you could hit shift-f2 and it would pop up a nice dialog to edit your text in. alt text http://techinterview.org/edit.png

Sql server 2005 SQL Identity Column out of step

We have a set of databases that have a table defined with an Identity column as the primary key. As a sub-set of these are replicated to other servers, a seed system was created so that they could never clash. That system was by using a starting seed with an increment of 50. In this way the table on DB1 would generate 30001, 30051 etc, where Database2 would generate 30002, 30052 and so on. I am looking at adding another database into this system (it is split for scaling/loading purposes) and h

Sql server 2005 SQL 2005 Maintenance Cleanup Task .bak files not being deleted

I have a SQL 2005 Maintenance plan that runs a backup to create a .bak file, and, on successful completion is supposed to run a Maintenance Clean Up task to delete any .bak files in the folder the back up task saves to that are older than 5 days. My backups are being created, but my old files aren't being deleted. I don't see any errors in the log. I think that perhaps I have a permissions issue -- that the account the job is run under needs access to the folder the .bak files are to be delete

Sql server 2005 Is there a way to force a jdbc connection to use TCP?

In a normal SQL Server 2005 connection string, it's possible to specify the desired protocol in the following format: Data Source=tcp:myServerAddress; Initial Catalog=myDataBase; Integrated Security=SSPI; Is there a way to do something similar in a JDBC connection string to SQL Server? Edit: My JDBC Connection String looks like this: JdbcDrivers=com.microsoft.sqlserver.jdbc.SQLServerDriver; Provider=Mondrian;Jdbc= 'jdbc:sqlserver://myServerAddress; instanceName=SQLEXPRESS; data

Sql server 2005 login failed for user: The user is not associated with a trusted sql server connection?

We have an access front-end tied to a sql server 2005 back end. We have no trouble with any users accessing the interfaces with the exception of one. When trying to interact with the interfaces we get a SQL Server Connection Error telling us that the user is not associated with the trusted sql server connection. We are use mixed mode authentication so that is not the problem. We also thought it may be the computer itself, but the user gets the same error on multiple computers. Any suggestions

Sql server 2005 performance tuning with procedure having temp tables

i have many queries which needs to be tunned and i was relaying on DTA(datbase engine tuning adviser which comes with sql 2005) to give me some recommendation on indexes. but looks like DTA fails to understand the queries which uses temp tables . is there any way we could get the index recommendation. Thanks for your suggestions Regards DEE

Sql server 2005 can't insert xml dml expression as a string

Here is the code below that would explain you the problem... I create a table below with an xml column and declare a variable, initialize it and Insert the Value into the xml column, create table CustomerInfo (XmlConfigInfo xml) declare @StrTemp nvarchar(2000) set @StrTemp = '<Test></Test>' insert into [CustomerInfo](XmlConfigInfo) values (@StrTemp) Then comes the part of the question,, if I write this... update [CustomerInfo] set XmlConfigInfo.modify('insert <Info><

Sql server 2005 Obtain Update Table Lock at start of Stored Procedure in SQL Server

I'm writing a SQL Server stored procedure in which I want to lock a table for update before executing the body of the stored procedure. I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table. Here is my first attempt: CREATE PROCEDURE someProcedure BEGIN SET TRANSACTION ISOLATION LEVEL READ COMITTED BEGIN TRANSANCTION SELECT COUNT(*) FROM TheTable WITH (UPDLOCK, TABLOCK) -- Pause procedure so that we can vie

Sql server 2005 Check file existence from sql 2005 without needing SA permissions

I have been trying to get xp_fileexist in sql 2005 to run under a sql account without having to grant sysadmin permissions. The issue is that I am unable to get it to run otherwise - the value it returns is 0 despite knowing the file exists. Points to keep in mind: The share that the images/files are stored in is not local to the sql instance. I have tried to make this work and have been successful by creating local user accounts and granting permissions as required, but we need to have a sql

Sql server 2005 "Distinct" column in SQL query

SELECT id, EmpNo FROM EmployeesTable EmpNo can be the same for 1 or more records in the results of the above query. I now want to add another column derived from EmpNo(lets call it EmpNo2) but only returning distinct values of EmpNo. For example if the above query returns 100 records but there are 69 distinct EmpNo values and i modify the query to SELECT id, EmpNo, Distinct EmpNo2 FROM EmployeesTable EmpNo , i want all the 100 rows to be returned but the last column EmpNo2 should r

Sql server 2005 Can't connect to database, error 80040e21. Impossible to debug

I have a simple file that tries to connect to a database- <% Set RSDiscounts = Server.CreateObject("ADODB.Recordset") RSDiscounts.ActiveConnection = "Data Source=serverName;Initial Catalog=dbName.dbo;Integrated Security=True" %> When I run it, I get- error '80040e21' /filename.asp, line 3 Searching for the error code doesn't help. My best guess is that something is specified in the connection string that shouldn't be there. But I used Visual Studio to create the string, and that co

Sql server 2005 Calling web service from SQL Agent Job not working

I have a a SQL 2000 DTS package that is scheduled to run from a SQL 2005 SQL Agent job. In this DTS there is a ActiveX step that has the following VBScript to call a webservice. Dim http: set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") http.setProxy 2, "" http.open "GET", "http://mywebservices.com/MyWebMethod?Param1=value1", false http.setProxyCredentials "SQLServiceAccount", "" http.send When running this DTS manually i can see that it runs fine and is able to

Sql server 2005 How to manually expire a SQL login account?

I am writing vb.net code to log a user into my application using SQL to authenticate the user. Each app user is an actual Microsoft SQL Server 2005 user. Now I need to test the login mechanism and for this I need to: 1) test against a SQL account that is locked. 2) test against a SQL account who's password has expired. Is there some stored proc or SQL that I can run to manually lock out a SQL account and to update a SQL account so it's password has expired. I do not want to wait for a day

Sql server 2005 Don't understand rounding behavior in sql server when using division operator

Could anybody explain what happens in the following sql code? declare @dividend numeric(38,22) declare @divisor numeric(38,22) declare @otherDivisor int set @dividend = 1 set @divisor = 3 set @otherDivisor = 3 select cast(@dividend / @divisor as numeric(38,22)), @dividend / @otherDivisor The result returned is 0.3333330000000000000000 0.3333333333333333333333 I would expect the same result for both calculations.

Sql server 2005 What causes this error..please run "exec sp_register_custom_scripting 'CUSTOM_SCRIPT', your_script?

Configuration SQL 2005 (Server A) replicates to SQL 2008(Server B) which replicates to SQL 2008(Server C). I recently added a column (to Server A) to a replicated table via script & the DDL change replicated to Server B with out a problem. When the DDL change replicated to Server C, I received the error below. 'DDL replication failed to refresh custom procedures, please run "exec sp_register_custom_scripting 'CUSTOM_SCRIPT', your_script, 'EDI from xx', 'table_name_here' "and try again (S

Sql server 2005 A long stored procedure stops returning results after some execs, why?

Hi I've a long stored procedure which is used to populate a GridView in my asp.net(c#) application and another console application(c#). Every time I run this sp on management studio it works fine. Some days this sp stops returning values to applications (but at the same time works fine when executed from management studio). Why or how can this happen? (I'm using ms sql 2005 and win server 2003) thanks

Sql server 2005 Multiple columns are specified in an aggregated expression containing an outer reference

the following query is giving error. SELECT job.job, ( SELECT SUM((jrt_sch.setup_ticks / 100) + ((jrt_sch.run_ticks_lbr / 100) * job.qty_released)) FROM jrt_sch WHERE jrt_sch.job = job.job ) plnlbr FROM job WHERE job.job = 'J000069762' AND job.suffix = '0' I am not able to use job.qty_released in side second select giving following error. Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being ag

Sql server 2005 How to replace a part of XML node in SQL Server 2005

I would like to know how I can update a part of the text of an XML node in SQL Server 2005 using xquery In the following example I would like to replace the word "very" with "excellent" declare @xml as xml set @xml = '<root><info>well hello this is a very good example</info></root>' declare @replacement as varchar(50) set @replacement = 'excellent' declare @search as varchar(50) set @search = 'very' set @xml.modify('replace value of (/root/i

Sql server 2005 Views, Joins, Locks and Order By

I created a view using CREATE VIEW NewView AS WITH TableA AS ( SELECT * FROM RealTableA WITH (ROWLOCK, UPDLOCK, READPAST) WHERE (Something1) ) , TableB AS ( SELECT * FROM RealTableB WITH (ROWLOCK, READPAST) WHERE (Something2) ) SELECT * FROM TableA INNER JOIN TableB ON (TableA.ID = TableB.RefID) and everything is working smoothly - two processes running at the same time selecting from the view will select different records from it. The problem is that now I want to use an ORDER BY cl

Sql server 2005 Problem with Join in SQL with 2 databases

I am not so good at sql server. I am trying to do something like this Select * from Page_Image_ID, PageID, DI_ImageID, caption, sort_order from Merchant_Subpages_images where PageID = 12345 This gets me right values. My problem here is that for every DI_ImageID, there is a row in a an other table from other database which i want along with the above result. For each DI_ImageID, Select * from DI_Image from Images where DI_ImageID = @(all above IDs) How is it possible, guys? Thanks a lot i

Sql server 2005 Windows 7 OSQL Batch

I have just got a new work PC from running Windows 7. I have installed SQL Server 2008 and I have a batch file that runs an OSQL command as follow: osql -S MyServer -E -d MSDB -n -o results.txt -i MyScript.sql For some reason it is giving me exception below: Specified driver could not be loaded due to system error 126: The specified module could not be found. (SQL Server Native Client 10.0, C:\Windows\system32\sqlncli10.dll). I am not sure what the issue is and I have even tried to run

Sql server 2005 doesn't quotename work inside of exec?

I'm trying to do something similar to this question. The answer recommends using quotename inside of an exec. Similar to this: declare @var nvarchar(128) set @var = 'hello world' exec('print ''' + quotename(@var) + '''') But this doesn't work (Incorrect syntax near 'quotename'). Is the answer wrong? I know I can generate the string first, put it in a variable then use it with exec sp_executeSql, but I would rather do it the way in the question if it can work...

Sql server 2005 pivot in sql - generate grandtotal column from pivot columns

Below query works fine and i get the pivot table as required. The dollar amounts show up fine from January to December. Now, how would i add a total column that would give me a row total from January to December? Since these months column are created using pivot, i am not sure how to add the values for all months and display that in a new column. Could you please help? Let me know if you need more clarification on what i am looking for. Thanks in advance! CREATE TABLE #t ( Region v

Sql server 2005 Sql Server Paging issue

Friends, I have already implemented paging in my SP - with MyData As ( select ROW_NUMBER() over (order by somecolumn desc) AS [Row], x,y,z,... ) Select x,y,z,... From MyData Where [Row] between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize) The problem here is that data is retried very fast if with clause return smaller number of rows but it takes long time when there are millions of records. Sometimes it times out. Is there any other alternative? Thanks for sha

Sql server 2005 <SQL Script>Invalid column name 'XXXXXXXX'

I have a sql script say "abc.sql" which I am calling from a batch file using sqlcmd like Calling batch script like script.bat arg1 arg2 In batch param1=%1 param2=%2 Then calling SQL script like sqlcmd -S server -i abc.sql -v var1=%param1% var2=%param2% In SQL script DECLARE @node as sysname; DECLARE @serv as sysname; SET @node = $(var1); SET @serv = $(var2); But this giving erro while I call the batch script "Invalid column name 'XXXXXXXX'" where 'XXXXXXX' is the value of var

Sql server 2005 SQL Server - Creating a new login account with "sysadmin" role from an account without "sysadmin" role

My sql database has 2 accounts. 1st account is sa 2nd account is user Both account has sysadmin role enabled. We have already forgotten password for sa We only know password for user Somehow someone remove sysadmin role for user. Now I need an account with sysadmin role. How can I achieve that? I cannot change the password for sa as some websites might be using sa account to connect to the database. So, my best solution is to create a new account with sysadmin role by any means necessa

Sql server 2005 Are there any problems with this timezone approach?

Adding timezone support to an event management system using TimeZone-CFC. The approach I am taking has the user select the event timezone and enter datetimes based on that timezone. The datetimes are converted to the server's timezone on save. Before presenting the datetimes back to the user they are converted to the event's timezone. For example on save... <cfqueryparam value="#application.tz.castToServer(eventStartDateTime, eventTimeZone)#" cfsqltype="cf_sql_timestamp" /> then when p

Sql server 2005 Select from another table within an Insert Trigger

I am maintaining an audit table, where in I have a parent table and it's child table.I want to insert the primary key of the parent audit table into it's child audit table. Should I be declaring a "before insert" instead of a "for insert" trigger. Here's my code: CREATE trigger [trgAudtblChild] On [tblChild] for Insert as BEGIN declare @serNo bigint declare @expSerNo int declare @postPercent numeric (12, 2) declare @prdSAPid varchar (50) declare @lastUpdatedBy int declare @lastUp

Sql server 2005 bulk insert with format file sql server 2005

I would like to create a stored procedure that imports a txt file into an existing table using a format file and also update certain fields that will be null in the import. The import file will just update one column and I need to manually assign values for the other columns. bulk insert QueryData from 'E:\TrackInputLogs\NMSLog.txt' with(FORMATFILE = 'E:\TrackInputLogs\NMSFormat.fmt', ROWTERMINATOR='\n') I need to update the following columns as follows at the same time dateimported = getd

Sql server 2005 Create table for more categories

I need to know a suitable way to create a table with this info: ID, Category, Subcategory. The category has more subcategories, but also I can add new sub, sub category in the future, I tried to create to two tables for category and subcategory, but I will face a vital problem in the future when I need to add a new sub sub category. So how can solve this issue?

Sql server 2005 how to connect to SQL Server 2005 from Visual C++

Can anybody help to understand how to connect from Visual C++ to SQL Server 2005? i am developing an ISAPI dll using Visual C++ and i need to query the database to fetch a few details. I tried to connect to the DB using CDatabase. When I invoked the dll from a local system, it worked without any issues. But when i access the dll from a remote system, it's throwing Login failed for user NT AUTHORITY/ANONYMOUS LOGON. What should I do?

Sql server 2005 Connection string to SQL Server 2012 SP1 from SQL Server 2005 SP3

I have a table on a SQL Server 2005 SP3 system and has server name and a connection string in a table called SQLServer. An SSIS package looks at this table and uses the connection string data to fire off a bunch of other packages that gather data in regards to all SQL Servers on my network. (Disk Usage, DB size, Backup / Job details, etc. etc.) A very solid amount of data for administration and auditing purposes. I have a handful of systems being used with ease, until now. I have a new

Sql server 2005 System.Data.sqlClient.sqlError: cannt open backup device ....operating system error2( error not found )

I am working with SQl server 2005 and I am trying to back up the database using SQL server management studio Express but I encountered this error System.Data.sqlClient.sqlError: cannt open backup device 'E:........' operating system error2( error not found ). Even if i try to back up the device on another directory like c fore example I still having the same error . Please how can I solve this Error ? Please Help me Since I am new to Sql server ......

Sql server 2005 Bring through a newly created calculated column in another query

I have 2 separate queries below which run correctly.Now I've created a calculated column to provide a count of working days by YMs and would like to bring this through to query1(the join would be query1.Period = query2.Yms) please see the query and outputs below. SELECT Client, ClientGroup, Type, Value, Period, PeriodName, PeriodNumber, ClientName FROM metrics.dbo.vw_KPI_001_Invoice select YMs,sum(case when IsWorkDay = 'X' then 1 else 0 end) from IESAONLINE.Dbo.DS_Dates wh

Sql server 2005 SQL Server 2005, Caches and all that jazz

Background to question: I'm looking to implement a caching system for my website. Currently we're exploring memcache as a means of doing this. However, I am looking to see if something similar exists for SQL Server. I understand that MySQL has query cache which although is not distributed works as a sort of 'stop gap' measure. Is MySQL query cache equivalent to the buffer cache in SQL Server? So here are my questions: Is there a way to know is currently stored in the buffer cache? Follow up

Sql server 2005 SQL Advice - Aggregating data for specific cases

Essentially I need to group unique product data into a single row when: The supplier sku matches The price for the products match (or) 1 of the product lines is equal to '0.00' Here is a sample dataset set along with a working query for what I'm trying to accomplish. I'm simply not entirely comfortable that this is the best way to perform this query. DECLARE @Test TABLE ( SupplierSKU VARCHAR(25), Description VARCHAR(50), Quantity VARCHAR(25), Price VARCHAR(25) ) INSERT INTO @Test SELECT '

Sql server 2005 Entity framework 4 not closing connection in sql server 2005 profiler

I'm using entity framework 4 for the first time in an ASP.net application. I wanted to make sure that DB connections are closed after leaving the using statement, however, in the SQL Server 2005 profiler, I cannot see the connection logout when leaving the using, only the login when entering it, For example, I started with a blank asp.net page, and in the Page_Load, I tried with this simple code (and absolutly nothing else in the page) : using (var model = new DB("name=DB")) { var livre = (f

Sql server 2005 Cannot recover the master database. SQL Server is unable to run

Right, I powered off development server because it was hanged, when restarted directory services database was corrupted, so fixed it, then figured out that IIS Manager's metadata.xml is messed up, so fixed it using backup too. But now SQL Server service isn't starting giving this error, Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Serve

  1    2   3   4   5   6  ... 下一页 最后一页 共 18 页