|
Good Morning Guys
I have the Following statement, that works well when i test it alone
declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] 'MasekoTS','MasterDatabase', @Results OUT
But now, i want to include this code in a Stored Procedure and i tried to do it like this
SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + '''@Results OUT'''
execute ( @sql1 )
and i get the Following Error
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'sp_RestoreDatabase_O_Bookin'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_RestoreDatabase_O_Bookin'.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Instead of doing execute ( @sql1 ) try Print @sql1 to see where its missing a quote - thats my usual method.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks i have Executed the statement without String Manipulation and its working
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Pleased to have been of service 
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Just a pointer to help you out in the future with debugging dynamic SQL. When you have a problem like this, rem out the EXEC(@SQL) and put a select @SQL or PRINT @SQL.
Take the returned value and attempt to exec it in a diff. query window. Compare your orig statement to the dynamic one and find the error.
I think the error in this case is the missing , after the file name.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
modified on Wednesday, January 21, 2009 8:46 AM
|
|
|
|
|
Thanks i got the Solution, i just used it without any string Manipulation
thanks for your help
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
Is it sometimes better to just delete and then reinsert some records instead of updating them? I have a complex scenario which is hard to explain but I am thinking that instead of updating, deleting and inserting is a better option. Has anyone come across such a scenario where deletion and reinsertion is better than update?
|
|
|
|
|
If you are doing a delete of a set of records, as apposed to a delete of ALL records, then the table is scanned to locate the records to be deleted. The delete action also affects any indexes on the table. Insert also affects the indexes but is less costly.
Update only affects the indexes if the indexed fields are updated.
You can do both in management Studio with the execution plan and costs turned on and compare your specific process.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can do both in management Studio with the execution plan and costs turned on and compare your specific process.
Like Holmes said....if there is any doubt and you are debating the speed differences between different SQL statements, do it both ways and compare the execution path. Stats don't lie. See what the costs are and avoid scans.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Without knowing anything more on the issue, I'd say you should try to avoid delete/insert combination if the logical request is to update record(s). For example even it may be possible now, what happens if you add child tables underneath this table. Would the data in those tables be deleted/inserted also. Also I think there would be some locking related issues etc.
In most situations I've encountered this technique, it's been a modeling/architectural issue either in db or in the program or in both.
But as said, with the info you provided, it's hard to say more.
|
|
|
|
|
Consider the following:
ClothingStyle Size
============= ====
ID ID
Description Description
For example, TShirts can come in S, M, L, XL, XXL where each of these is in the Description column of Size. So the relationship here is 1:M. However, sometimes some clothes have a two dimensional size. For example, jeans can come in waist sizes of 26, 28, 30, 32, 34 and so on but can also have length. How can I create the structure for such a scenario?
|
|
|
|
|
You can use something like this i think, I use something similar for Product Properties.
ClothingType(
ID
Name
)
ClothingProperty(
ID
ClothingTypeID
PropName
UOM
Description
)
ClothingPropertyValue(
ID
ClothingPropertyID
ClothingID
Value
)
I don't speak Illidiot - please talk slowly and clearly
|
|
|
|
|
No, no, you got it all wrong use Attribute instead of Property, why b/c I like Attribute, I have no idea why, prejudiced I guess.
Oh the structure is the same of course 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Table Name: MyTable
ID NAME DATE
=============================
1 James 2008-08-29
2 Jack 2008-08-29
3 James 2008-08-31
4 Jack 2008-08-31
=============================
Above is my table named MyTable with fields ID, NAME and DATE. I got 2 different Names with two different Dates.
There are two records for James with different Dates:
James 2008-08-29
James 2008-08-31
..and two records for Jack with different Dates as well:
Jack 2008-08-29
Jack 2008-08-31
What I want is to display the record like this:
NAME DATE1 DATE2
=================================
James 2008-08-29 2008-08-31
Jack 2008-08-29 2008-08-31
=================================
I want to combine the records with the same Name and place the different Dates in separate columns. Please help me figure out what is the right SQL Query for this. Thanks in advance!
|
|
|
|
|
I presume you are using sql server 2005 or later. What you need is a pivot table.
This article may help[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How do I get rid of the following erroe message?
The syntax 'for replication startup options' is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
|
|
|
|
|
|
It is already in my my.ini File on all my Slaves.
It is as follows:
CHANGE MASTER TO
MASTER_HOST='master-hostname'
MASTER_USER='slave-user'
MASTER_PASSWORD='slave-password'
What else do you suggest trying?
|
|
|
|
|
Instead of adding it to my.ini, I think you should execute the CHANGE MASTER command. That command modifies the master.info file and sets the new values for each option you specify. Those options that you do not specify are left unchanged.
|
|
|
|
|
Do you run that command on the MASTER Server or all of the SLAVE Servers. I am running Windows XP Professional on all Units.
|
|
|
|
|
On the slaves. From documentation:
"CHANGE MASTER TO changes the parameters that the slave server uses for connecting to and communicating with the master server"
|
|
|
|
|
I ran the same command set on each of the slaves, stopped each of the slave servers after updating the information and then restarted them each. Went back to the Main server and stopped it and restarted it. Got the same exact error message upon starting the server.
Trying to start the server ...
Server was started.
The syntax 'for replication startup options' is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
Slave SQL thread initialized, starting replication in log 'MEDCO1.000002' at position 4, relay log '.\MEDCO1-relay-bin.000036' position: 4
Event Scheduler: Loaded 0 events
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-debug: ready for connections.
Version: '5.1.30-community-debug-log' socket: '' port: 3306 MySQL Community Server - Debug (GPL)
Slave I/O: error connecting to master 'slave-user@master-hostname:3306' - retry-time: 60 retries: 86400, Error_code: 2005
|
|
|
|
|
Do you still have the options in my.ini. That would explain the problem. Also you should check that there are no leftovers in master.info. That overrides my.ini.
|
|
|
|
|
All lines of code that pertain to the Previous Change Master are removed from the My.ini files. Is there anyway to change or verify the master .info file or Purge it?
|
|
|
|
|
I don't know if they have a tool for that. You could search on their site or manuals. However, if I remember correctly, master.info is plain text so you can easily check it's contents.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
