I am using MSSQL 2000 and trying to debug a stored procedure that has a #temp table in it. I am using the .NET SQL debugger and I would like to display or view the contents of the #temp table while debugging. Does anyone know how to do this?
View 1 RepliesI have a real table with an identity column and a trigger to populate this column.
I need to import / massage data for data loads from a different format, so I have a temp table defined that contains only the columns that are represented in the data file so I can bulk insert.
I then alter this table to add all the other columns so that it reflects all the columns in the real table. I then populate all the values so that this contains the data I need.
I then want to insert into the real table pushing the data from the temp table, however this gives me errors stating that the query returned multiple rows.
I specified all the columns in the insert grouping as well as on the select from the temp table.
ANY thoughts / comments are appreciated. This is beginning to drive me nuts.
Rob
Having probs debugging trigger ... need to view the "inserted" table contents, how do I do this? Manuals are pants, any tricks folks?
View 1 Replies View RelatedI am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
[Code] ....
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work?
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
View 7 Replies View Relatedis 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?
View 1 Replies View RelatedHi,
I have found that when I'm debugging a custom component in BIDS that I've created in another instance of Visual Studio, every time I rebuild the component I have to shutdown and restart BIDS and then reattach to the BIDS process. Which is pretty time consuming... And if I find a small error in my custom component when debugging then I don't seem to be allowed to make any changes to the code unless I stop debugging and go through the process above.
Am I missing something here? Or do I really have to manually go through these steps every time I want to change code in the component I'm debugging?
Can I automate the process with MSBuild or NAnt? If so, is there an example of this anywhere?
Thanks in advance,
Lawrie.
Hello
I want to have a print of table records in sql server express.
I went several locations, but File|Print item didn't on.
How can i print table contents in sql express environment ?
Thank you for helping me.
Hi,
I am relatively new to this stuff.
I am using Microsoft SQL Server Management Studio Express (9.00.1399.00)
Can someone tell me the way to get a table and its content to another database (I use two at webhosts4life)
Or perhaps a way to export the data of a table so I can do it at a later stage.
Is that at all possible with this program or do I have to use the non-express version?
Thanks in advance,
Lex
Hello, Its hard trying to explain this.
I have 3 tables
Table 1 is where the users are stored, each user has a username and a userrank
Table 2 is where the points that decides the userrank are stored
Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields
Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields
Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:
WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END
Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this...
DECLARE @username nvarchar(20)DECLARE @pts int, @rank int
...something that starts a loop thru table A (user_list) and get the username into @username...
SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username)
...next persion in the loop...
This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...
But how do i loop thru all the users? with a cursor?
Is it possible to do an update * where in we update all the contents of the table after selecting specific rows of the same table.
for ex:
create table #Result(rowid int identity(1,1),cust_no int,companyname varchar(100),address varchar(200),zip varchar(9));
insert into #Result(cust_no,companyname,address,zip)
where (customertype='r' and customer.comp_id ='11')
group by customer.cust_no, customer.company
select * from #Result where customerstatus='s';
after this i want to update/replace the #Result with whatever results I have from the select statment.
Thanks for your replies.
Hi!!!
Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.
Holy MOLY I've been banging my head up against the wall on this matter for months now. I have two databases and I need to be able to see the tables from different different databases. Usually I create a view like this
LicensingActions.dbo.License_Suspensions
But it wasnt working kept telling me that the License_Suspensions table didnt exsist and at the time the name was in all caps. So I decided to change the name, and low and BEHOLD IT WORKS. The funny was that I wasnt having that problem any of the other tables just that one. Well live and learn. Just thought I'd share that with you guys
Using Sql Server 2005 Express and Management Studio I need to create
a SQL insert statement for the contents of a table (FullDocuments) so
that I can run the query on another server with that same table schema
(FullDocuments) and the contents will automatically be inserted into
the new instance of the FullDocuments table.In Management Studio
I have used "Script Table as" for the create table query. The
second instance of FullDocuments has been created on the remote
server. Now how do I generate an insert query for the contents of
FullDocuments so that the contents can be moved/inserted to the new
instance of the table?Thanks for any help provided.
What is the best way to read and edit data in the tables of a sql server 6.5 database?
Thanks
Gunnar
gunnardl@yahoo.com
I am trying to perform a query in SQL that will merge the contents of various table, using search criteria to narrow down the results. All of the tables have the exact same fields, and the search criteria is the same for all of them. What I am looking for is the most efficient way to perform this. I am using SQL stored procedures, and passing the results into my code. Note that in my real-world scenario there are more than three tables.
1) Merge all the tables into one and then perform the search criteria:
SELECT a, b, c FROM
(SELECT a, b, c FROM Table1
UNION ALL
SELECT a, b, c FROM Table2
UNION ALL
SELECT a, b, c FROM Table3) AS MY_MERGE
WHERE {SEARCH CRITERIA}
2) Perform the search criteria on each table and merge the results
SELECT a, b, c FROM
(SELECT a, b, c FROM Table1 WHERE {SEARCH CRITERIA}
UNION ALL
SELECT a, b, c FROM Table2 WHERE {SEARCH CRITERIA}
UNION ALL
SELECT a, b, c FROM Table3 AS MY_MERGE WHERE {SEARCH CRITERIA})
I'm copying date from a sql server 2000 table to a sql server 2008 R2 table using a Linked Server.I need to be sure the remote table is correct before processing it.I first delete all the remote table contents and after that, I insert into remote_table from local_tableWhat happens if the communication is interrupted during the insert?I mean, Can I be sure all records were copied just checking if record count > 0 ?
View 3 Replies View RelatedHi,
I've a bunch of records that may contain data that I'm after. For example:
This is a fake title [electronic resource]. 1997.
I have a very small table (~10 rows) of things like '[electronic resource]'
Is there any way to see if my record contains any of the 'target' items in the other table?
An application uses a database table having proprietary information. We do not want our customer to be able to look at that.
This being a real-time application, performance can not be sacrificed. What is the best way to keep the table data non-viewable without sacrificing the performance?
This is an SQL Server 2000 question. Without having access to the "Design Table" option in the pop-up menu of a table, is there another way to see what constraints, indexes, primary key, etc. that a table has?
Thank you.
I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.
thanks!!
Toni Eibner
I have a large table containing about 800 million rows with an average row length of about 1K. The columns in the table are char columns. I need to move the contents of this table into a similar table where the target columns are varchar. The original table column definitions are compatible with the target table but the reverse is not necessarily true. For example, one column is being changed from int to bigint. The table is partitioned.
So, what is the fastest way to migrate the data. I was thinking to unload each partition into a flat file and load the target table running multiple load streams? Is this a good way?
This is a new problem to me, I have been running SQL Server 2000 with VS 2003 and last summer upgraded to SQL Server 2005 with VS 2005 - (both evaluation copies) all was fine
I bought the full upgrade of VS 2005 and SQL server dev edition in Dec 2006 and recently I have started to get a total system fallover dreadful noise followed by black screen and reboot.
This happens only when I view the Table Data "Show Table Data" in "Server Explorer" in VS2005, viewing design is OK, stored prcredures OK
I suspect this is either a hardware impending failure or software conflict but I would like some pointers please
I am using XP Pro sp2 with 1GB ram, F Secure Internet security, the Database is hosted externaly
Hello, I'm Hernan I wonder if someone knows How to query the last table used date in sql server. Note that I'm interested in "used date", not "modification date".
Thanks.
I hope someone can help me.
If I have a table with three fields (A, B, C) and 'A' is a primary key, 'B' and 'C' do not have any content and another table with the same number of fields contains the same fields(A, B, C) and the fields do contain data.
I want to populate the first table with the data from the second table where the primary key 'A' is equal.
How would the sql work?
Any idea on how I do this?
Any advice welcome.........
HI all, we have a table that has it's primary key data type Bigint. Isthere any way with Access to view this table? I find it hard tobelieve that two tools from MS don't play nice with each other, may beI should.SQL Server 2005Access 2003TIA
View 5 Replies View RelatedWhat are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.
Any help is much appreciated. Thanks.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
declare @tTable(col1 int)
insert into @tTable(col1) values (1)
select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies View RelatedIf on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.