|
Well, there's no "grade" here ... so I'll use "results" instead. But to illustrate only a point:
CREATE TABLE [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister](
[id][int],
[school][nvarchar](15),
[name][nvarchar](38),
[Subject][nvarchar](45),
[results][int],
[studentID][int]
)
That's the table.
BULK INSERT [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
FROM 'C:\Users\OT\registerOT(td)_.txt'
Where registerOT(td)_.txt has the posted data tab-delimited.
SELECT * FROM [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
WHERE [results] = 72
ORDER BY [id]
FOR XML PATH('Schools'), ELEMENTS, ROOT('Register')
Not what OP is after but perhaps more key than the usual webpage link.
<Register>
<Schools>
<id>5</id>
<school>Cambridge</school>
<name>john</name>
<Subject>English</Subject>
<results>72</results>
<studentID>2</studentID>
</Schools>
</Register>
[edit]
Is it possible that THIS is what is sought (notice the closed tags that have been added)?
<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>98</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>87</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>63</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>50</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>72</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>32</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
</Schools>
</Register>
[/edit]
modified 3-Jul-13 14:36pm.
|
|
|
|
|
Hi,
I keep getting the following message on MySQL on Windows Server.
How can I get a permanent fix for it?
Please try again....... Host '192.168.1.254' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Technology News @ www.JassimRahma.com
|
|
|
|
|
One way of course would be to stop creating that many connections.
One reason that happens is because the client is not closing the connection after usage. A variation on that is where there are many user clients and some one misused connection pools in that thus creating a many connections for each client when either a properly configured connection pool or no pool at all would have been a better solution.
|
|
|
|
|
According to http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors[^], the number of failed connection attempts are counted: i.e. the client tries to connect, but the connection fails for which ever reason. Network problems, wrong credentials, etc. But also - see Jeff's answer - having opened too many connections without closing them; in that case, you should find some entries in the logs that the maximum number of connections was excessed.
|
|
|
|
|
Hi,
I am using INSERT into MySQL database. I am inserting arabic character. The database character set is utf8 and collation is utf8_unicode_ci same with the field article_title.
but the title saved in the article_title field is like this ?????????? (just question marks)
look at this : http://www.volow.com/bahrain.images/About[^]
where is the problem?
Thanks,
www.volow.com
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
this is what I did but I still have the same problem.. Please Help..
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\share\charsets\
collation_connection utf8_general_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
and in my connectionstring:
<add name="SQLdb" connectionString="Server=xx.xx.xx.xx;uid=admin;pwd=xxxxxxx;database=xxxxx;Charset=utf8;" providerName="MySql.Data.MySqlClient"/>
Technology News @ www.JassimRahma.com
|
|
|
|
|
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything.
Each product can have any number of different properties.
I have a huge database,which containing about 500000 product in product table.
So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly.
Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
|
|
|
|
|
Member 7702110 wrote: Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
That's not due to a "single" thing, it requires a lot of things to be checked. 500.000 items isn't much.
A normalized design (upto BCNF, see wikipedia) would, IMHO, be the best startingpoint. Next, you'd need to determine indexes. You'd also need to optimize the calls you make to the database - a rewrite of the SQL statement can often improve it's performance.
..and do check the health of database-server once in a while; is the database fragmented? Latest Service Pack installed? Enough memory?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue)
Example:
100,Color,Black
100,KosherCertified,False
100,InsuranceValue,123.99
A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products.
Just an idea. ![Java | [Coffee]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/coffee.gif)
|
|
|
|
|
And AttributeType, so you can cast the attribute to the right type. And then normalize the Attribute and AttributeType into its own table.
Just my $0.02
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Fairly basic question really, if each of my Tables has a Primary Key of UNIQUEIDENTIFIER, when I create a Foreign Key to point at the PK of another Table, what Data Type should it be ?
|
|
|
|
|
Also uniqueidentifier. Types of PK and FK needs to match.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Really ? I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field ... I have a lot to learn here  !!!
|
|
|
|
|
Hi,
As I understand it, A Unique Identifier is only created if you also set it as Primary Key.
Regards 
Bram van Kampen
|
|
|
|
|
Gary Heath wrote: I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field
They're not generated by default; it's just a datatype to hold GUID's. For the Primary Key to generate one, it'll have to have a default - usually coming from the NewId function.
The PK is the unique value that identifies your record. An FK only references it; we copy the unique value of the PK that we want to link, and keep that information in the FK.
Person
PersonId
Name
OtherStuff
User
UserId
PersonId
Person1: 145, Joe, Stuff
User1: 132, 145 -- referencing a user by using it's PK
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I think it should be on same data type
|
|
|
|
|
hi,
i am using vs.net 2010 to make a web service project with linq to connect to a ms sql server.
however i notice that although i added the annotation for the classes classes that need to be persisted.
it is still very tedious to make changes to the class it self as i need to manually go into the database to make the table changes.
can anyone advice me if i should continuing using linq or is there something better out there? which is as simple to use as linq but can automatically change the ms sql table to reflect the annotation i used in my classes
|
|
|
|
|
|
neodeaths wrote: or is there something better out there
Incremental changes handled over time to a database schema with the only record being C# code annotations would be a nightmare for a non-trivial business system.
Of course in terms of that it makes me wonder how you are tracking your changes to the database now given that you are doing it in "the database".
|
|
|
|
|
Hi,
I am using C#.Net-VS2012 and SQL2008R2.
My Computer Network name is b-pc, The Connection is Softguard.
I have the following code snippet:-
SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"connection timeout=5");
myConnection.Open();
It excepts with the following reason:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
.....And Much More!!
It seems to look for some form of authorisation.No username or password was provided (nor asked for) when the connection was created new in DbExplorer. Widows Authorisation was used at the time.
What more must I do to connect to the server.
Kind Regards,
Bram van Kampen
|
|
|
|
|
Either use integrated security (the preferred way) or specify a user. Bookmark this[^] site, add below part to the connectionstring;
Trusted_Connection=True;
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks,
That worked! Unfortunately, the whole proces turns out to be One Step at the Time!
It connected to the SQL Database, to arrive at the Next hurdle:
Exception:
-Quote: System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed.
Login failed for user 'b-PC\Bram'.
Now, this very database exists in Ms SQL Management Studio under the Path:
-Quote: b-pc\SOFTGUARD\Databases\SgTextiles
It is definitely populated with tables with content. The correct spelling and case has been meticulously checked. The Tables were constructed and populated with the SQL management studio, and, also saved. In a further check, (done while writing this, I performed a 'Save All' in the studio, and closed it down. (this went without any complaint from the Studio about Unsaved Files etc) When re-starting the studio, The database had the 'SgTextiles', and all its components in the tree display. When Clicked upon, there was a Dlg stating that 'SgTextiles' is un-available, and the branch cleared. SgTextiles is still unavailable, and is displayed as if totally empty.
Any idea what other "magical incantations" are required to get to the next step.
To anticipate a following issue, I named the first two tables "Users" and "Roles". They are shown in the studio as "dbo.Users", resp "dbo.Names". Is this the correct way of addressing these tables in a queery string, or should I leave out the 'dbo' part.
Thanks for your much appreciated help sofar.
Regards,
N.B.(Modification)
A thought just entered my mind. I am running all this software under a Standard Win7 User Account.
Is there a problem with permissions? I have both Amin and User Access to this machine.
In retrospect, I noted a few times SQL Studio purporting to do thing without complaint, and then not doing them, without stating so. I had already a few detours via the 'Admin', trying to set HKLM Reg Keys by hand.
Thanks
Bram van Kampen
|
|
|
|
|
If you can't get it with a trusted connection, try creating a named user in SQLServer and give that user access to the database. Then changed your connection to use the username and password you assign.
|
|
|
|
|
Bram van Kampen wrote: A thought just entered my mind. I am running all this software under a Standard
Win7 User Account.
The user that creates the connection, regardless of how the user is specified, must have appropriate rights in the database to access the resources that are needed.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
