Hi all. I was wondering if there was a way to supress the error messages while designing an SP in Enterprise Manager, such as if you have a column name in your T-SQL that doesn't exist in a table and you click 'Apply' or 'OK', Ent Mgr throws an error 'Invalid column name ...' and you cannot save the SP.
The reason I need to do this is because I am writing a large data-transforming SP. The SP takes data from an old, un-normalized DB and converts the data into a new, normalized format. I need two temp fields in three tables. These fields will act as references to bridge the data transformation from the old data to the new. So, I will create the temp fields in one SP, run the data transformation in a second SP, and drop the fields in a third SP. Note that I split out the adding and dropping of the fields because of experimenting with some debugging of the SP failure.
Which brings me to my second issue. If I run the SP to add the temp fields, the SP runs fine and the fields are added. Then I can open the data x-fer SP, develop and debug it, and click 'OK' or 'Apply'. No problem. Then, I can run the SP to drop the temp fields.
However, I'd like to have everything in one SP. This way, when I need to transform the production data, I can just execute one SP all under the scope of one BEGIN/COMMIT TRAN transation. Assuming that I have everything in one SP: if I comment out the lines that drop the temp fields, then the SP runs fine. If I uncomment the lines that drop the temp fields (at the end of the SP), then the SP fails, with and error in the middle part of the code that does an INSERT INTO <temp_field_created_in_the_SP>.
Why would the SP fail to insert into temp fields if I uncomment lines of code at the end of the SP to drop those fields. Does it have to do with the scope of the transaction? I even tried to COMMIT the main transaction that adds the fields and transforms the data, and wrapped the dropping of the fields in a separate transaction. It still errors out.
Here is the code to add and drop the temp fields:
IF NOT EXISTS (SELECT * FROM syscolumns WHERE ID=OBJECT_ID('Customers') AND NAME='TEMP_ad_addr')
ALTER TABLE Customers ADD TEMP_ad_addr VARCHAR(80) NULL
.
<transform all data code>
.
IF EXISTS (SELECT * FROM syscolumns WHERE ID=OBJECT_ID('Customers') AND NAME='TEMP_ad_addr')
ALTER TABLE Customers DROP COLUMN TEMP_ad_addr
I have one data flow task which creating a database table (tempTable) and other task will migrate data from other database into this table. Now I add third task which will read the data from this tempTable. If tempTable is not in the database, then validation will failed. Is there a way to turn off validation at runtime. In my case, my package running every night. I would like to delete this tempTable at end of package run. Now I have to pre create this temp table, and run package and leave the temptable around for the next run to pass validation problem.
I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
This may sound silly, but I'm calculating how much someone owes over time verses how much they have paid for that period. So if they owe more than they have paid the result is an under payment amount like -$100. Then I need to add the -$100 to the new payment due, let's say $100. So the total Due is $200. But if I add -100 to 100 that equals 0. Any suggestions?
Hello,In SQL Server 2005 Enterprise, I can change the CPU affinity viaManagement Studio. Is there a way that I can change these settings viat-sql?I wish to use all my cpu's at night when my data warehouse builds andthen durning the day reduce the number of cpus for SQL so that theapplication can get more time.TIARob
Ever since installing SQL Server 2005 Express Edition the XP Task Manager shows €œsqlservr.exe€? running normally, as a €œNETWORK SERVICE€? consuming 26MB of memory. This is true even after a cold reboot. I€™d like to turn SQL off since I am not currently planning on using it. I tried detaching a couple of projects I had started, as part of tutorials, but that did not make a difference.
So how do I turn SQL Server 2005 Express Edition off without removing it from the system, which I do not want to do?
Can anyone help me with this problem. I have old data that I need to insert into a new table. I need to keep the old identity values and then turn them on again for the new data.
I am running sqlserver 2000 Enterprise edition on Windows NT 4.0 service pak 6. When ever I run a long query, the transaction log seems to fill up. How do you turn this transaction log off? Please let me know ASAP.
I am looking for a smart solution without hardcoding the column names (e.g. CASE WHEN ResourceID = '200012' THEN Time END as ['200012'], etc...), as there can be up to 15 different resources involved; usually they aren't but I want to keep the result as slim as possible without tons of columns containing nulls.
I have a nvarchar(256) column and when I am using C# code to add data to it. When I enter data over 256 characters long, it automatically truncates. When I try to do this is the Query Editor, it will not allow me to and terminates with the error "Binary data or string would be truncated"
Is there a way to turn this automatic truncating off?
How do I return a list of sold CD's, showing the last once (least sold) first? Below sql, ordered by DESC, returns a CD-list from 1, 2, 3 ... 49, 50, 51. (but I would like the turn it like this)
Least sold cd
51 Martin 50 Elvis 49 Krall
SELECT top 10 SUM(Cd.Price) AS Sales, Artist.Name FROM Cd INNER JOIN purchase ON Cd.Cd_id = purchase.Cd_id INNER JOIN Cd_artist ON Cd.Cd_id = Cd_artist.Cd_id INNER JOIN Artist ON Cd_artist.Artist_id = Artist.Artist_id GROUP BY Artist.Name ORDER BY SUM(Cd.price) DESC(Due to "=RowNumber(Nothing)" numbers are return instead of salesfigures.)
I have around 1200 databases and some of them may be required to turn off one table pk's identity. And they might be turned on later with a different seed and incrment. But I don't find in alter table t-sql to turn them off or on or change the seed/increment value. I need to turn off/on via t-sql without drop/add the pk field. thanks David
How, using SQL, do I turn off the identity property in the table? I need a script to run against a number of db to permanently remove the identity property from the tables without needing to use Enterprise Manager.
I apologize in advance for bugging you all with a basic question, but I can't get it answered anywhere else.
I've been planning on learning MS SQL for a while and recently obtained SQL 2000 software. However, before I got the software I got several books on SQL 7. My question is this, are the differences between SQL 2000 and SQL 7 so great that I wouldn't be able to use the SQL 7 books with the v2000 software?
I'm nesting a bunch queries, the parent being a select, and the children being inserts. I'd like to retain the auto generated IDs from the original table, and insert them into the new tables (into identity fields). I believe that there is a command that I can use to temporarily turn identity auto numbering off for the current query - can anyone help me with this?
" I WAS SHOCKED WHEN I SAW HOW MUCH MONEY CAME FLOODING INTO MY PAYPAL ACCOUNT." Dear Friend,
This is a new program as you will see. It can be for the U.S. or the U.K. This is a fantastic opportunity, try it for yourself!! EVERYTHING DONE ONLINE ! ! With just £5 or $10 and a Paypal PREMIER or BUSINESS account, you could make $$Thousands$$ in 2 SIMPLE STEPS ! !
READ THIS VERY CAREFULLY THEN READ IT AGAIN. THIS IS FAST, THIS IS NOT THE ONE YOU WILL BE WAITING TO WORK.
GUARANTEED - you will have $10,000 in two weeks. Only $10, a Paypal PREMIER or BUSINESS account and 30 min are all that is needed to get this going.
GIFTING MADE EASY, PLEASE READ ON TO FIND OUT HOW
If you want to make a few thousand dollars real quick, then please take a moment to read and understand the MLM program I'm sharing with you. NO IT'S NOT what you think. YOU DON'T have to send $10 to five or six people to buy a report, receipt or get on their mailing list. Nor will you need to invest more money later to get things going. THIS IS THE FASTEST, EASIEST PROGRAM EVER, you will be able to complete it in LESS THAN 30 MINUTES and you will never forget the day you first viewed this online.
If you're already running a home business, you can easily do this one along with it. If not, this is the FASTEST and EASIEST way to earn serious money online that you've ever seen so far in the history of the Internet! This program works no matter what country you are in, or what currency you use. It doesn't matter how old or how young you are. And you certainly won't need any special knowledge or talent. You won't need to run a website, or make phone calls, or make photocopies, or send out letters through the mail, or pay for advertising, etc. The only things you will need are:
* An email address * A Premier or Business PayPal account with at least $10 deposited in it * 30 minutes of your time
ONLY 2 SIMPLE AND EASY STEPS ! !
If you are doing other programs, by all means stay with them. The more the merrier, BUT PLEASE READ ON!! First of all, there are only TWO POSITIONS, not four, five or six like many other programs. This program is more realistic and much, MUCH FASTER. Because it is so easy, the response rate for this program is VERY HIGH and VERY FAST, and you will receive your reward in FOURTEEN DAYS. That's only TWO WEEKS - not one or two months. Just in time for next months BILLS.
TRUE STORY --> Grace Kelly tells how she ran this gifting summation four times last year. The first time, she received $6,000 in cash in two weeks and then $14,000 in cash the next three times.
When this program is continued, as it should be, EVERYONE PROFITS!! DON'T BE AFRAID to send gifts to strangers. It will come back to you ten folds. A person can give you up to $10,000 per year and you don't have to report it to the IRS.
THIS CAN AND WILL WORK FOR YOU! HERE ARE THE DETAILS:
Post this letter in 50 message boards, chat rooms, or newsgroup. You don't need to post 200 like other programs or email 100 people just to get started. Even if you are already in a program like the send twenty dollars to six people. CONTINUE IT and stay with it, THOSE DO PAY OUT, but do yourself a favor, DO THIS ONE TODAY as well, RIGHT NOW!!! It is simple and takes a very small investment, only $10 and a Paypal PREMIER or BUSINESS account. iT WORKS AND IS DONE IN 2 SIMPLE AND EASY STEPS ! ! This program WILL PAY FASTER before the other programs you are working on even trickle in!! Just give one person $10. THAT'S IT!! Follow the simple instructions and in two weeks, you should have $10,000 because most PEOPLE WILL RESPOND due to the LOW ONE TIME INVESTMENT, SPEED AND HIGH PROFIT POTENTIAL.PLUS, since its done all ONLINE, there is no time wasted mailing out letters and the such ! ! We are not even figuring a 50% response! So let's all keep it going and help each other in these tough times. We all owe money to bill collectors, and creditors. But, with this, if people take the time to help each other, the good fortune will follow. SO, Take the measly minutes this SIMPLE and REWARDING program offers and give the gift that gives back TODAY ! !.
Simply send a $10 gift to the person at POSITION #1, if your birthday is BETWEEN JAN 1 THRU JUNE 30, OR, if your birthday is BETWEEN JULY 1 THRU DEC 31, send the gift to the person at POSITION # 2.
HERE'S WHAT YOU NEED TO DO. . .
STEP 1
Ok, if you're not already a PayPal user, the very first thing you need to do is use the PayPal link below and SIGN UP. It takes just two minutes!
Here's the URL:
https://www.paypal.com/us/mrb/pal=AQH5ZY3CXRHZN
Be sure to use this link so you can sign up for a free PREMIER or BUSINESS account. You'll need to have a PREMIER or BUSINESS account (and not a PERSONAL account) otherwise you won't be able to receive credit card payments from other people.
STEP 2
It is an undeniable law of the universe that we must first give in order to receive. So the first thing to do when you have your Premier/Business PayPal account is to IMMEDIATELY send a $10 payment from your PayPal account to the email address in the position (1 or 2 - depending on your birthday in the timeline), along with a note saying: "HERE IS A $10 GIFT FOR YOU." Be certain to add this note, as this is what KEEPS THIS PROGRAM LEGAL. Instructions on how to send a payment are under "SEND MONEY" at the Paypal Site. It's so Easy!!
When you send your $10 payment to the first address in the list, do it with a great big smile on your face because "as you sow, so shall you reap!"
After you have transferred a $10 payment to the email address, something very eerie happens. It gives you an indescribable, overwhelming sense of certainty, belief and conviction in the system. You've just proved to yourself that, because you have done it, there must be a great number of other people ready to do exactly the same. Thus you have now seen for yourself, first hand, that this business actually works!
REMOVE the email address you are replacing (the one you sent $10 to), and REPLACE it with your own email address.
REMEMBER: Use the same EMAIL ADDRESS that is used with YOUR Paypal account ! !
Paste your newly typed email address over the old one and then post to 50 Message Boards, Chat Rooms, or Newsgroups. DON'T MULL OVER IT, JUST 30 MIN OF YOUR TIME, THAT'S IT THERE IS NO MORE TO DO!!
ACT FAST AND GET THE GIFT. HONESTLY AND INTEGRITY MAKE THIS PLAN WORK!! DO UNTO OTHERS AS YOU WOULD HAVE THEM DO UNTO YOU .
POSITION#1 --- iexpoi@yahoo.com
POSITION#2 --- bumpsie@comcast.net YOUR NAME COULD CYCLE FOR A LONG TIME ! ! THIS MAKES IT THE GIFT THAT KEEPS ON GIVING.
This is excellent seed money to start or expand a business, buy you dream home, car, and pay off what ever bills you may have. Good luck and God Bless. Play fairly, Good fortune will follow.
WHY THIS PROGRAM WORKS:
!! HONESTY AND INTEGRITY !!
Straight to the point, the reason this program is SO SUCCESSFUL is beacuse when you follow these instructions EXACTLY = GIVE A $10 GIFT and POST this in 50 different message boards, there are only TWO outcomes.
(1) A persons birthday will fall on the same half as yours, therefore, YOU GET THE $10 . .
OR,
(2) a persons birthday will fall on the other half of the timeline, therefore, YOU STAY IN YOUR POSITION and get posted in 50 other MESSAGE BOARDS, etc . .
BOTTOMLINE: Either you get the $10 or your name gets passed on for other people to give YOU $10 or pass YOUR name on to other people that will. It makes you think though, the more you POST YOUR OWN POSTS, the BETTER the results ! !
4 FACTORS THAT MAKE THIS PROGRAM SO SUCCESSFUL. . .
* EXTREMELY FAST RESPONSE
* EXTREMELY HIGH RESPONSE RATE
* UNLIMITED PROFIT POTENTIAL
* QUICK, SIMPLE AND CHEAP TO GET STARTED
So there it is. You now have the knowledge that will enable you to make over $10,000 within the next two weeks. The only thing that can hold you back now is a lack of faith or a lack of self-belief. However, any doubts you may currently have will disappear within a few days of putting this plan into practice. Trust me on this! You certainly won't regret it.
SOME NOTES FROM PAYPAL WHICH YOU MAY FIND USEFUL
PayPal lets you pay anyone with an email address and is the world's Number 1 online payment service. PayPal is accepted on over 3 million eBay auctions as well as a countless number of online shops. You can also use PayPal to pay your friends - for example, it's a convenient way to split the phone bill with your room-mate, send cash to your kids in college, or send cash to someone in another country. Better yet, you can also earn a referral bonus of up to $100 each time someone signs up for a PayPal account using your referral URL!
When you send money through PayPal, you can fund your payments with your credit card or cheque account. You won't have to worry about your privacy, because PayPal keeps your accounting information safe and secure. Making a purchase with PayPal is far more secure than mailing a cheque or giving your credit card number to a stranger. That's why over 9 MILLION people from around the world use PayPal to move money. Signing up for a PayPal account is free, easy, and it takes only a couple of minutes.
Thank you, and good luck in your ventures
Best Regards
P.S. Does this sound too good? Well maybe to some skeptics it is. But it actually works, and is worth the 30 minutes of your time now. So, just make a nice cup of tea and get started on it now. After all, you can't lose, but you stand to gain more in the next few weeks than many people earn in a year!
I added Validate Subscriptions to my publication using the procedure outlined in BOL. Procedure is listed below. I want to stop the validation for this publication. I do not want to validate any longer. How can I stop this process? To validate transactional data using SQL Server Enterprise Manager
At the Distributor, expand Replication Monitor, expand Publishers, and then expand a specific Publisher.
Right-click a transactional publication, and then click Validate subscriptions.
Choose whether you want to validate all subscriptions or just specific subscriptions, and if you want to validate specific subscriptions, select those in the text box.
To choose the type of validation, click Validation Options.
Choose whether you want to compute a fast rowcount based on cached table information, compute an actual row count by querying the tables directly, or compute a fast row count and if differences are found, compute an actual row count.
You can also choose to enable Compare checksums to validate data, a binary checksum (if the Subscriber is running SQL Server 2000), and you can choose to stop the Distribution Agent after the validation has completed.
I've been stress-testing SSQ queue by pumping a lot of data to it. For the most part, the queue is cleared out fairly quickly using the activation stored procedure. However, for a second time now, I encountered a strange problem. For some reason, one message got stucked in the queue and would not dequeue. The status of the queue is turned OFF somehow. So I ran:
ALTER QUEUE [dbo].[TestQueue] WITH STATUS = ON;
to turn the queue status back on, but it would not turn ON. The last time I had this problem, I ended up dropping and recreating everything.
Has anyone experienced this strange behavior? Please help.
Hi. I setup a group on report service and give this group "Brower, My reports, report builder" permision, but I can't found report builder on client. does anybody can help?
Is it possible to turn off transaction logging when making a table schema change? For example, when expanding a varchar field from 10 to 40 characters? This is occurring on a hosted site for a table with about 150,000 records. The db size is 200 Mb. If I try this normally with transaction logging enabled, I get the dreaded log file is full message, even if I first truncate it. I was thinking about doing the following instead:
taking the db offline creating a backup disabling logging change the schema re-enable logging put db back online If a problem occurs during the schema change, I would just restore from the backup. Please let me know the following:
Is it possible to turn of the transaction logging for the schema change? Do you see any problems with the above. Alternatives? One alternative is to look into increasing the db size. A second is to add a new field (named temp) to the table, copy the old field to the new field, delete the old field, add another field (with the original field name) with the new schema, copy the temp field to the new field, and finally delete the temp field. This should require less space in the transaction log. Unfortnately, it could possibly affect some linked Access databases with the new field order. Thanks in advance.
I presently have Error Logging turned on for the SQL Executive, but I no longer want it. When I open up the "Configure SQL Executive" window under the Server menu, the "Error Log File" field is dimmed and there is no option to turn it off.
Is there anyway I can turn off delete in SQL server? I want to prevent anyone inadvertently deleting rows in tables. I thought worse case I could have triggers on tables to perform roll back.
To get deadlock victim alert do we need to turn on deadlock trace flags or if I create an alert and if there's any deadlock incident happen, it will throw alert (if no deadlock flag is set)?
I've a table with an ID column, it is an auto increment id column that increases by 1.
ID -- 9724 9725
When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.
My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)
So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?
example: id, place, locstion, gps, v2, v3, v4, comment ================================================ 9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat 9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photos
I'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?
