At Sql Server Live, I saw Sharon Dooley using the debugger in Query Analyzer, but now that I've tried it I can't step an sp, instead, I get....Quote:
SP debugging may not work properly if you log on as 'Local
System account' while SQL Server is configured to run as a
service.
You can open Event Viwer to see details.
Do you wish to continue?
End Quote:
I'm logged in at a W2Kpro box connectig via client tools
to a W2KSVR box via Integrated NT security. SS2K is NOT
installed locally.
is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?
I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?
I'm developing a web app using ASP.NET and SQL Server 2005 Express. So far it's all been on my local computer, it hasn't gone live yet, so if I need to add a column to a table or make some other schema change I just do it right in Visual Studio, nice and simple. If I have to delete all the old content and start over, no problem. When I deploy it to a staging server I just overwrite the existing file with my new one, losing its data in the process. But soon enough I'll be deploying this to a public web server, there will be real live data in the db, people using it when I need to make updates.
What are some common strategies for updating the schema of a database on a live server?
It's obvious that when I need to update the db I'll have to shut down the site temporarily. But my biggest question is how to keep the existing data from the live db? I obviously can't overwrite it with my local copy. I want to overwrite its schema, without touching its data. How's that done?
I am new to SQL server 7. Recently I need to design a 400-or-so table database. I need an automatic method to delete some relative data in all those table. Can anybody tell me: Does SQL server 7 support cascade delete referenctial action?
How to create a row number for a consecutive action. Example: I have a listing of people who have either completed a goal or not. I need to count by person the number of consecutively missed goals.
My sql table is this: PersonId, GoalDate, GoalStatus (holds completed or missed)
My first thought was to use the rownumber function however that doesn’t work because someone could complete a goal, miss a goal, then complete one and when they complete a goal after a missed goal the count has to start over.
I am writing a trigger for getting values to auditlog table when the values gets updated. Below is the code of my trigger.
CREATE TRIGGER [dbo].[Update_Temp] ON [dbo].[Temptable1] FOR UPDATE AS DECLARE @bit INT , @field INT , @maxfield INT , @char INT , @fieldname VARCHAR(128) , @TableName VARCHAR(128) ,
[Code] ....
The code is working fine when the table has primary key associated. However due to some restrictions I will not be able to have a primary key for some tables. I want to implement the same trigger in those tables too. When there is primary key, that primary key needs to get inserted into the audit table and if there is no primary key, i want a specific column value to get inserted instead of the primary key value into the audit table.
For example, i have a student table in which there is a student id, name, dob. there is no primary key defined for the table. So when i update the name or dob, i need the student id to get inserted into the Pk column of the audit table.
I tried modifying the code by checking the @pkcols for Null and if its null to get the old value as the primary key however I was not able to do it .
I am experiencing a situation where certain functions work perfectly when I run it on my local machine under MVS, but when I upload it to the server, then it does not?! Here is an example: I am using a drop down box (in a FormView) that is databound in an online submission form. When I run the application in MVS, then I can edit the records by opening the form, and selection the new value in the drop down list. The new value is then also saved to the database when I hit the update button. When I upload the code to the server, then the drop down list shows the correct information (so the databinding to the control seem to work correctly), but the new value is not saved to the database. Here is the code for the drop down list: "DropDownList1" runat="server" DataSourceID="SqlDataSource3"DataTextField="UserName" DataValueField="UserId" SelectedValue='<%# Bind("UserId") %>'CssClass="text">"SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users] ORDER BY [UserName]"> Here is the code updating the database with the record (I have removed some records as well as the Insert and Delete parts): <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tourism_connect1 %>"UpdateCommand="UPDATE Resorts SET typeid = @typeid, destrictid = @destrictid, UserId = @UserId, WHERE (resortid = @resortid)"OnInserted="SqlDataSource1_Inserted"> <UpdateParameters><asp:Parameter Name="typeid" /><asp:Parameter Name="destrictid" /><asp:Parameter Name="UserId" /><asp:Parameter Name="resortid" /></UpdateParameters></asp:SqlDataSource> What I can mention further, is that if I connect to the database that is on the live server (so the application runs in MVS on my local machine, but it then retrieves the info from the online database), then it also works fine. It is just giving this issue when the online application is trying to update the values. There is also no errors during the process. I will appreciate any advise on how to overcome this, as I really do not know where to look anymore... Thanks in advance! Regards Jan
I need to restart (becouse of the Database Mail :( ) SQL Server Agent on a live server which acts as a distributor for a lot of replications. I know that it shouldn't cause any problem, but I want to confirm that it want couse a subscriptions to be reinitiated.
This is a bit confusing but here goes: I need to access data in SAP via OLE DB. I can't go direct to the back end database (Oracle), we have to use RFC or BAPI calls to access the SAP data. That's part works, we have a DLL that accesses the SAP data we need.
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1. Both has SQL server 2005 running and has visual studio 2005 Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server. Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this. Thanks George
I have created a site using VWDExpress and now that I’ve finished testing have moved it over to the server which runs SQLServer 2000. Part of the site requires login, so I created the membership using the ASP.net web configuration tool and when testing locally worked well. Now though that I’ve copied the web site over, when I try to log in I get the error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
Obviously its some sort of configuration issue, but I don’t know what. What do I have to change to make this work from a test machine to a live server? Thanks
Hi,Ive been building a project on a local server and now need to transfer the files and database over to the live server. I have managed to transfer the files quite easily, however I am having a few problems transferring the database using Microsoft SQL Server Management Studio. When I try importing data to the live database, all the data and tables are brought across however some of the values for the fields are lost. For example all the fields which had a primary key no longer have one, and all the set default values for the fields are also lost. Ive been considering just going through every table in the database and re-entering the correct values for the fields, but this seems a bit time consuming and I'm also concerned about possible errors which could arise as there will be an inconsistency.Another method I tried was copying the SQL query across which created all the tables and the correct values for the fields. However when I tried to import the data an error was produced as you cannot import data into a table which has read only fields etc..Does anyone know a solution?Thanks for your time.
I have a live server running windows Nt and sql server We brought a new backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
Im fairly new to this so apologies if Ive put this in the wrong place.
I have just rented out server for a new asp.net site. Ive been developing the site on my local machine and the database in Sql Enterprise. Ive recently put a copy of the site on the live server and got everything configured.
However everytime I make a change to my local database, or add values into my look up tables I have to go do the same on the live server and its proving a bit of a pain in the ass, so Im guessing there has got to be a much smarter way of doing this. I dont want (if possible) to open up the live sql server to allow remote connections, but if thats what neccesary so be it.
So my question is basically what are my options for keeping my live server up to date with my development server. As I add new features on my local server, should i be saving sql scripts of all the changes I make?
I have just upgraded from Report server 2005 to 2008 r2. I have a program that calls the report server to run some reports but I have getting the error Server did not recognize the value of http header SOAPAction:
[URL] .....
I can log in to the report server and run any report that I want without any issues.
I need to live update my SQL-Server 2000 database from AS400 database. I usually use Access 2002 database between the AS400 database and the SQL-Server 2000 database to reflect any live update. I use the (Link Tables...) option when creating a new table in access and link it with the ODBC to connect with the AS400 database. After that I work with the Access link table.
Is there any tool in SQL-Server 2000 to show the live transactions in AS400 databasee so I can work on it?
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master GO EXEC sp_dboption CurDataBase, 'Single User', True EXEC sp_renamedb 'CurDataBase', 'OldDataBase' GO 3- Rename Newdb to current DB. USE master GO EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
Hi, Is it advisable to use full text search on asp.net live applications? Will live database servers allow full text search?. If so what are the problems we could face in future. My DB server is SQL Server2000 and ASP version is 1.1. Thanks in advance Hamlin Stephen
I am doing a proof of concept and part of the solution is to configure Common Live Server. However, I am running into problems and do not have that much experience in SQL Server 2005.
I have downloaded and installed the trial version and it seems to be running. I run the configuration manager and see the instance running. When I continue to configure my Live Communications Server - Creating an Enterprise Edition pool, it prompts me for the pool name, FQDN and Instance. I fill these fields in and it fails immediately. I think that I have not completely installed and configured SQL 05. Problem is, I don't know how to connect to the database as well. I found an sqlservr.exe in the MSSQL/Binn directory, but when I run that it pops up with a dialog saying that my installation may be corrupt. I have re-installed the thing again - but same thing happens.
Any clues/tips on how I should approach debugging this even resolving the problem.
Anyone know of any third party T-SQL debuggers for stepping through stored procedures? I understand there is one with VB6, but a student in my class doesn't work with VB. TIA.
Hi, I've just read in books on line that in Query Analyser there is a debugger tool. Unfortunately I can't work out how to access and use it. Can any one send me some info on this please.
Once I open my TSQL Debugger all the icons are disables ( Step Into , Step Over ). This is the fisrt time I am using it can you please point be in the right direction.
This happens after giving the Input parameters in the Dubug Window for the Stored Procedure and when I click the EXECUTE button.
NOTE : This happeds when I lauch the Debugger from the ClIENT Machines. If I run the debugger on the server directly it works fine. Am I missing any of the DLLs
I'm trying to get the T-SQL Debugger to work between SQL Server 7 and Visual Studio 6.0.
First of all, I am confused by the various articles I have read in MSDN. Some indicate that the debugger will work in Visual Basic and some indicate Visual Interdev. Does it work in both environments? After following all of the steps listed in the MSDN articles I cannot get the T-SQL Debugger to appear in my Add-Ins window in Visual Basic. Is there something else I need to do?
Secondly, I can see my stored procedures in Visual Interdev and once I open one in the editor the only commands enabled on the DEBUG menu are Clear All Breakpoints, Breakpoints... and Processes.... According to one of the MSDN articles I should see all of the DEBUG menu commands enabled. Any ideas what I can do to get the rest of the menu commands enabled?
Also, I can set a breakpoint in a stored procedure by clicking in the gray area to the left of the line of code where I want the debugger to stop. The red dot appears next to the line of code. But when I execute the stored procedure the debugger asks for the parameters but never stops at the breakpoint. It runs the stored procedure to completion.
I would appreciate any ideas on how to get this working.
Can anyone tell me besttool for debugging the Stored Procedure user friendly. We are using Powerbuilder/Sql Server 6.5 as back end. we got lots of stored procedures and I am having tough time in debugging. I actually moved from Oracle and don't know much about Sqlserver just started learning.
If anyone tell me best tool,method for doing then I will be very greatful
I can get the debugger to work: I get a message:"sp debugging may not work if you log as as Local System Account" whileSQL Service is configured to run as a service. You can open Event Viwerto see details." (Viwer means "Viewer" this is not one of my typos...)My text book says that this message means there is a problem that canbe fixed in the SQL Server registration node (just under the servergroup in Enterprise Manager), Properties dialog, Security tab, Start UpService.The book says to enter a valid user account and password in the StartUp Service area. However, there is in my case a "LocalSystem" and anencrypted password entered in this area and and I have no idea howthese got in there.I assume the latter are default settings from when SQL wasinstalled...Can I can this to "sa" and no password? I'm afraid tochange the existing setting if I don't understand what it is and whereit came from..Thank you **very** much for help...I would like to use the debugger.J.
Hi,When I debug our sp by using T-SQL Debugger, I got following error.Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissiondenied on object 'sp_sdidebug', database 'master', owner 'dbo'.and when I grant the permission of sp_sdidebug, I got following error.Server: Msg 4610, Level 16, State 1, Line 1You can only grant or revoke permissions on objects in the currentdatabase.I am using following statement to grant the permission.GRANT EXECUTEON sp_sdidebugTO myuserHere, I want to clear, myuser is I, which one i log QA.and myuser has public and dbowner permission--Rahul
I'm using the Transact-SQL Debugger for the first time here.I go to Object Browser, find my stored procedure, right click andchoose "Debug".The "Debug Procedure" window comes up, and I click on the Executebutton.The Debug environment comes up and automatically executes myprocedure. It doesn't allow me to single step through the code.What am I missing here?All of the "step" buttons, "restart", and "stop debugging" buttonsare dimmed out.Thanks.Ken
I am using Visual Studio 2005, and I go to server explorer and make a new connection to a database. I select a stored procedure and right mouse click.
There is no menu item called "Step Into ... " in the pop up menu. So what am I missing? Do I have to install or set up something in order to use Transact SQL Debugger?
Any pointer or answer is greatly appreciated. Thanks a lot.
