|
Hello,
I have the following situation which I can seem to resolve. When I do an "ALTER TABLE" in mysql (v5.5) it hangs in with "waiting for metadata lock". Here is how I got there:
- i have an xml which i want to import into a table; i use the elements in the xml to create columns in the table like this:
XML example
<name> some name </name> <address>dsada</address>
there are some nodes in the xml that have different number of elements, like:
<name> some name </name> <address>ds adsa</address> <phone> fndjsf</phone>
SQL queries
create table `person` ( `id` bigint(12) unsigned NOT NULL auto_increment ,`name` varchar(200) NOT NULL ,`address` varchar(250) NOT NULL ,PRIMARY KEY (`id`),) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into person(name, address) values ...
the column names are read from the xml, so are the values. If the for loop reads a column name that hasn't been created in the table yet, like "phone" it returns a sql exception "column name "phone" not found" and tries an alter table before inserting the values
alter table 'person' ADD COLUMN `phone` varchar(20) NULL default NULL;
here is where the program hangs. i've checked in mysql admin for the session and the state is "waiting for metadata lock"
how can i pass this? or how do i remove the table metadata lock created by the previous insert which raised the exception. (the import is made in java that's why i use a for loop to parse the xml and get the element names and values)
Thanks
|
|
|
|
|
You may need to commit your changes so that the alter table command will not wait. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
i am commiting the changes. i have also tried to unlock tables before alter but to no success. think i have to re-think the whole process.
|
|
|
|
|
Hi !
I have a very odd problem.
I am using LinqtoSql in my project in c#. , and what i do is i create a new table and set its column through sql server management studio express (2008 r2) , and I add a class file to the project(in visual studio) and using linq i do mapping of that table i made in Sql studio.
e.g
[column]
public string firstname;
[column]
public string lname;
etc etc.
My question is , if i want to make a new table and add its column etc through the windows forms application in c# at runtime (using sql commands etc) , how can i use linq to automatically map the newly created table without manually editing the code?
What should i do, i have to use LINQ to SQL.. PLZ HELP
|
|
|
|
|
OK, so you create your DB table at run-time. But what about the class to which you are mapping the newly created table, is it created at run-time as well? If it is (i.e. you generate and compile the code, or emit CLR byte code) you could provide [column] attributes too: there is no "LINQ-specific magic" in .NET, so you can do nearly anything the compiler can.
|
|
|
|
|
Thats the problem about classes .
that how can i add new class file to the project and add attributes at runtime?
|
|
|
|
|
You cannot add new classes to your project at run-time. You need to write a program that creates classes dynamically, for example using CodeDom[^] or Emit[^] APIs of .NET. CodeProject has examples of using both these sets of APIs[^].
|
|
|
|
|
I am using Microsoft SQL Server Management Studio 2005 v9.00.4035.00. When I view an existing stored procedure and copy the text or grid results to a new query window there are always several spaces after each line, and in some cases a ton of extra space. Before I commit any modifications to the stored proc I have to take the text into a text editor and trim the trailing spaces (okay, I guess I don't HAVE to but it just annoys me).
I was wondering if anyone has a better method of accomplishing this goal. I can't seem to find anything in options or any plugins I can install that will do this. Every time I try and Google this all I get is results on how to trim trailing spaces from the dataset you are returning.
Thanks in advance for your help!
|
|
|
|
|
Is you output datatype TEXT or CHAR, you may want to look at VARCHAR.
|
|
|
|
|
Thanks for your response on this however I am not referring to any output that I can control the datatype of. I am referring to the text that SMS brings up when you view an existing stored proc or view.
|
|
|
|
|
Okay I see what you mean...
I have not had a big problem with this as I usually have no trailing characters.
There might be a setting about trailing characters, but I cannot remember.
Sorry I cannot help.
djj
|
|
|
|
|
Hi all,
I uploaded some data to SQL Server 2008 from Excel.The table has 4 Fields,After upload the data to Database some of the data in fourth fields are NULL.I need to update the data from first as follows
Field1 , Field2 , Field3 , Field4
ABC_XYZ_123 , Office , 432322 , Null
XYV_PVC_321 , House , 908000 , 321
MBV_DHC_908 , House , 751232 , 908
MNP_BNM_876 , Office , 125152 , 876
As mentioned above,I need to update first row with 123 which already have in first field.
please help me to find to query for solvng above issue.
thanks
|
|
|
|
|
here it is
UPDATE mytable
SET field4 =
(SELECT RIGHT(mt.field1,CHARINDEX('_',mt.field1)-1)
FROM mytable mt
WHERE mt.field1 = mytable.field1
)
SELECT * FROM mytable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
thanks for you reply.let me try with it
|
|
|
|
|
That should works without problem, I tested it and it worked.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Hello,
I tried with above query.But it extracting 10 Digits.I dont know why.I have some datas in Field4 like this "74_125352" actually i need 125352.
modified on Sunday, May 8, 2011 1:46 AM
|
|
|
|
|
I'm trying to install MSSQL EXPRESS 2008 on my companies work computer.
I have the local admin right,just not the super admin. it gives me this not enough permission error.
When I did finally successfully install the MSSQL. It just that it gives these errors
2011-04-28 11:50:32.05 spid51 Error: 17204, Severity: 16, State: 1.
2011-04-28 11:50:32.05 spid51 FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2011-04-28 11:50:32.05 spid51 Error: 17207, Severity: 16, State: 1.
2011-04-28 11:50:32.05 spid51 FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
I did do some searching online. It says that this is caused because i wasn't able to finish the installation.
Is there any way to fix this or install the MSSQL? Since I'm just a normal dude in this company. There's no way I can get the super admin right. or even create another user account. Any help?
#EDIT all installation is directed to c:\ only. But it seems the default is e:\ weird setting presuming everyone has an E:\
modified on Thursday, April 28, 2011 4:29 AM
|
|
|
|
|
Just some ideas:
*I have had trouble with compression turned on. Once I turned off compression, my SQL installation went smoothly.
*Perhaps you had an external drive (e.g., flash drive or USB hard drive) plugged in during the install? Maybe it's a network drive that has since been disconnected?
I recommend uninstalling and installing again. If you don't have the permission to perform the install, have an administrator at your company install the software for you.
Also, run chkdsk to ensure your hard drive does not have any errors on it. This page explains some possible causes of the error. One reason might be that some files got corrupted or are not available (e.g., on a disconnected drive).
|
|
|
|
|
AspDotNetDev wrote: *I have had trouble with compression turned on. Once I turned off compression, my SQL installation went smoothly.
Where can i turn off the compression?
AspDotNetDev wrote: *Perhaps you had an external drive (e.g., flash drive or USB hard drive) plugged in during the install? Maybe it's a network drive that has since been disconnected?
Nope no external drive was installed and the network was on s:\ , so i don't think it will be e:\.
AspDotNetDev wrote: If you don't have the permission to perform the install, have an administrator at your company install the software for you.
I don't know if local admin right is enough to perform the install. I mean i can edit registry or change computer settings. I just can't create a new account. And asking the people in IT is like begging for mercy, I'd really like to avoid that until necessary.
AspDotNetDev wrote: Also, run chkdsk to ensure your hard drive does not have any errors on it.
Nope no errors.
|
|
|
|
|
loveangel888 wrote: Where can i turn off the compression?
I am speaking of NTFS compression. Right click the root drive (C:\ I suppose) and ensure "compress this drive to save disk space" is unchecked. Also, any folders that SQL installs to (e.g., Program Files) should also be uncompressed.
|
|
|
|
|
Well I checked them just now and I don't see this option anywhere. I suppose its not open by default .
BTW the file name I used to install is SQLEXPRWT_x86_ENU.exe
*EDITED NVM I found it , it wasn't opened
|
|
|
|
|
What drive are you installing from? a CD?
Also, did you run the install as administrator?
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
S Douglas wrote: What drive are you installing from? a CD?
From C:\ a downloaded File at Microsoft.
S Douglas wrote: Also, did you run the install as administrator?
My account is the local admin account. Except create account, I basically have right to everything ( I think ).
|
|
|
|
|
loveangel888 wrote: From C:\ a downloaded File at Microsoft.
Was it an installer file (EXE, MSI) or a virtual disk (ISO)? Speaking of, do you have drive emulator software that may have been enabled at some point (e.g., Alcohol 120% emulates DVD-ROM drives)?
|
|
|
|
|
right click installer, run as adminitrator. sql needs to install stuff that has to run as the local admin, which sounds like you have permissions to do so.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
