I am working on a sql server SP to delete records based on inactive customer ID.
There is a table A which contains the cust-ID and the status(e.g A-active,i-inactive). My SP is fetching all the inactive cust-id records from table A and deleting the entries from other tables(around 20 tables) based on the cust-id.
I am trying to do the data set-up to test this SP but have some queries:
My approach is to copy all the impacted tables(big size) into another database for testing and tried implementing the below logic :
1)select all(inactive customers) records from table A and store it in a temp table
2) use the below sql query to copy the records
insert into DB2.<tablename>
where cust-id in (select * from temp table)
I am getting error while creating the temporary table (SQL version 2008).
1)create temporary table <tablename> - invalid keyword temporary
2) create table #<tablename> - the table is getting created but is not getting deleted thereby if I run it the next time it says table already exists. This doesn't seems to be the property of temp table.
Both the above syntax are not working.
Also, will temporary table be a good way (performance wise) or can I use the below sysntax for all the 20 impacted tables :
insert into DB2.<tablename>
where cust-id in (select cust-id from table A where cust-status = 'I')
will it degrade the performance . The records to be copied for testing is around few hundreds for each table.
I have a SQL Table called Consumers that contains 495,037 records. The record size is about 350 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.
During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".
When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.
When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.
This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.
The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?
There is an Advance Tab on the DTS Properties Page; there are some options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I need to take a further look at. Have you used any of these options and do you think any particular one may help my problem?
There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem records to disk.
Before I start asking for favours I think it only polite that I introduce myself. My name is Doug I'm a EC&I engineer from the UK.
I'm developing a recipe managment system with SQL Server 2005 at the database system. I have a table which contains recipe parameters for an industrial process. I would like to generate a stored procedure which will copy either all or just some of the fields from one record in a recipe table to another record in the same table while retaining the unique recipe id. For example, CREATE PROCEDURE sp_CopyRecipes @RecipeNumberCopyFrom int @RecipeNumberCopyTo int AS SELECT Param1,Param2,Param3 FROM tblRecipe WHERE RecipeID = @RecipeNumberCopyFrom (And Copy to) SELECT Param1,Param2,Param3 FROM tblRecipe WHERE RecipeID = @RecipeNumberCopyTo
there are 45 recipe paramters. There is a requirement to copy all paramters or just a selection. I written a procedure in the VB.NET front end which works well, but I'd prefer it to be done on the DB server. Thanks in advance Hope I can contribute in the future
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I am using SqlServer 2005 Express Edition. I have written a stored procedure that has a transaction to make sure that it rolls back in case of failure. Is there a way to test that the transaction works. I don't now how to cause an error that would cause the transaction to roll back and raise the error with the details of the exception. Thanks, laura
hello everyone i was hoping to get some help. i have sql server2000 and iis5.1 running and im using vbscript through asp. i have a table that has two keys so that together they make a unique key. so i am trying to test my table in asp so that if there is a duplicate unique key inserted then show an error... Can anyone help me?
I want to run remote procs from a local - central proc, but don't want to crash my proc if the remote NT Server or SQL Server is not available. So, I want to test for NT and SQL availability first.
/* I find the server-dbasename name in a lookup table, and then build dynamic script */ SELECT @DBName = "<SRVRNAME~DBASENAME>" SELECT @QueryString = N'master..xp_cmdshell "ECHO >' + SUBSTRING(@DBName, 1,6) + 'pipesqlquery", no_output' EXEC @Results = sp_executesql @QueryString
SELECT @Results /* <-- testing here.... */
=====================
If the NT / SQL is running, "@Results" should = 0 (success on the ECHO to the PIPE) If the NT / SQL is NOT running, "@Results" should = 1 (error on the ECHO to the PIPE)
If I test in QA, I get the expected "@Results" values.
If I run the proc, I get "@Results" = 0 for both events, and them my proc fails on the remote proc call if the NT / SQL is NOT running.
I don't know how to write DMO in TSQL to try that. I have also tried using 'odbcping' too.
HINTS? Better way to test remote NT / SQL is NOT running?
i have performed all the steps for the replication there are two problems i encounytring (i) how to check whether the replication is working or not (ii) can replication on the subscribing server automatically create the table which publishing server is publshing (iii)please tell me someother forums for sql server
what my knowledge says that in replication if i modify the data at one place it should automatically be modified in the subscribing server but this thing is also not happening (its not flashing any error message as such) please tell me what all things i need to do to make sure that everything starts working
I'm new to this forum and as well as new to OLAP testing . I would like to know OLAP testing and guidance for SQL server anlaysis services .
I have been trying install SQL server 2000 in my system but it not going through, Can anybody send information how to install SQL server and how to play with sample database.
Has anyone ever heard of using Response.Write to test sql queries in the query designer? Here is the actual suggestion I was given recently:
Response.write your sql query. Copy the query to your db query tool and run the query. Adjust the query as necessary to return the recordset you desire. When you have the proper query, adjust your asp code.
The query editor/designer doesn't like certain symbols and extraneous code. So how exactly can you use Response.Write with a sql query without getting errors in designer?
I've begun some testing with the June beta of SQL 2005. One problemI've hit is with scalar-valued functions. The error I often get whenexecuting functions is "Select statements included within a functioncannot return data to a client.". These same functions are workingfine under SQL 2000.Has anybody seen this behavior or know what the deal is?
I've started researching on Unit Testing and I must admitI had never heard of Unit Testing until a couple of monthsago. Obviously I am interested in Unit Testing StoredProcedures.I read the TSQLUnit documentation (not all of it) and i also raninto a newsgroup post saying TSQLUnit is very small comparedto NUnit. The conclusion I am making out of this post is thatI should rather spend time resarching/reading about NUnit thanTSQLUnit. Is that a good assessment?I would like to you what you use and if you use actuallyUnit Testing or some other method? I ran into White Box/Black BoxQA testing. All these are new to me. Any good place to read about"Extreme Programming"? I ran into one link that I saved it at work.That's one place i will read more.Any links, documentation or books you would suggest?I searched Amazon and I didn't find anything interestingregarding SQL Server and Stored Procedures.Thank you
I am running windows xp pro on my home computer and would like to test andatabase driven ASP .NET web application. I need to be able to create an SQLdatabase which will be assessed by the web application. I already have alocal IIS Server up and running.At work, I use SQL Enterprise Manager. I wanted to download it on my homecomputer but cannot find it anywhere on the net.How can I test my web applications at home on a person computer?Thanks.Jess
Hi,How do I write a query where if a column, "value," is NULL I returnthe phrase "No value entered" but otherwise return the column's value,even if it is the empty string? I'm tried to modify this simple querySELECT value FROM meta_dataThanks, - Dave
can any one guide me how should i do performance testing on database size for sql server. I dont know how to read the execution plan, though i have read many document but still i am not clear. For are the reports which shows the performance has been down and its all accurate.
Is it possible to get a list of all the fixes and enhancements that are going into SP1 for SSIS? This will make it alot easier to test seeing as we will know what we are looking for.
How do I test failover to point to the mirrored database instead of the principal without having to stop the primary instance? I would like to just bring the mirrored database on the primary instance to an offline status and see that the same database on the mirrored instance getting activated as the primary. This does not happen if I try to put the database on the primary server "offline" as it complains that any database that is being mirrored cannot be taken offline.
At the moment, the only way (that I know of) to test a subscription seems to be to schedule it a few minutes ahead in time and wait for it to happen. Dies nayone know if there are plans for and "Execute" or "tTst" button? It would be nice to have one.
i've been informed that one way to test a query is to execute and roll-back it back as a transaction. my question is the following: using the aforementioned mechanism, how can i examine the database state before the query is rolled-back?
How does one determine in a VS load test the exact limit of the number of users the report server can handle without failing?
If 200 users proces request reports simultaneously, what is the best configuration for a single box deployment. The reports are parametrized reports and do not run from cache.
Hi I’ve got a simple SQLdataSource which is wired up to a gridView. When the page loads, it’s sometimes likely the data source would contain no data – how can I test for that in the code behind (no data)? I’d like to only show a button if there is data? I’m using ASP.NET 2.0 with C# Many thanksRichard
Hi I really need your help. I’ve got a table with 4 columns – username (a string of text), dateTime (a date and time), duration (time in minutes), roomID (URL). I really need some help with writing up some code behind to check if a user has permission to continue. I’ve got the first bit, but I need help with collecting the data and testing it: SqlConnection conn = new SqlConnection(Global.ConnectionString); SqlCommand cmd = new SqlCommand("select * from tblActiveSession where Username=@Username", conn); SqlParameter paraUsername = cmd.Parameters.Add("@Username", SqlDbType.NVarChar); paraUsername.Value = this.txtUsername.Text; try { conn.Open() This is where I need the help. What I need is to test each row against the following: If now() is within the allowed time frame – greater than dateTime, but less than dateTime + duration, then break the loop and redirect to roomID. I really hope this makes sence, and I’m greatful for any help. Many thanksRichard
I am creating a View in SQL2000 where i concatenate values from multiple tables. My problem is that if one of the values is null then the whole string is null, so I would like to know how to test for nulls within the Select part if the View.
Can someone advise me as to the best way to loop through a collection of columns for each row in a table testing the value of each column for nulls etc and then entering the column name into another column where the condition is true..
We need to start testing our Microsoft cluster servers. Has anyone heard of any plan or products one can use to test the failovers? Please advise Thanks Susan
