|
Pleased your are sorted
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi everyone,
I've been trying to make the XML Schema of my SQL Server Database and currently I've found this to generate an XSD schema,
create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in (’M', ‘F’, ‘O’)),
BirthDate datetime null,
)
DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA(’PersonSchema’))
select @schema
OUTPUT:
But the problem is that this is generating the schema of only one table where as I want to generate the schema of the whole Database, Can u suggest some quick solution for that?
Thanks in advance
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Hi Everyone,
I am developing an asp.net c# application. I use this environment
MySql 4.1
Driver 5.2
Provider MySql.Data.MySqlClient
Visual Studio 2008 Framework 3.5
Execute this request with a DbCommand doest work;
StringBuilder oStringBuilder = new StringBuilder();
oStringBuilder.Append("LOCK TABLES <table> WRITE;");
oStringBuilder.Append("SET @maxid = IFNULL((SELECT max(<table>.ID)+1 FROM <table>), 0);");
oStringBuilder.Append("INSERT INTO <table>(ID, USERID, DTCREATED, DTEDITED");
oStringBuilder.Append("VALUES (@maxid , {0}, SYSDATE(), NULL);");
oStringBuilder.Append("UNLOCK TABLES;");
DbCommand cmd = cnx.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = szSQL;
cmd.ExecuteNonQuery();
MySql.Data.MySqlClient.MySqlException: Parameter '@maxid' must be defined.
à MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlStream stream, String parmName)
à MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlStream stream)
à MySql.Data.MySqlClient.Statement.BindParameters()
à MySql.Data.MySqlClient.Statement.Execute()
Please help me regarding this. The best elaborated answer with some examples would be appreciated.
Thanks in Advance,
B. Jourdain
modified on Friday, June 20, 2008 2:44 PM
|
|
|
|
|
hi every body
i hava a problem with exception handling when i execute my stored procedure which is
/////////////////////////////////////////////////////////////////////////////////
ALTER Procedure [dbo].[PaginRec]
(
@PageNum int,
@NumOfRec int
)
as
begin try
begin with DIVRES as (
select Row_Number() over(order by Responsible desc)
as row, Responsible , Division
from Bulist)
select Responsible,Division
from DIVRES
where row between
(@PageNum - 1) * @NumOfRec + 1 and @PageNum*@NumOfRec
end
end try
begin catch
select 'Please enter the right number of parameters'
end catch
///////////////////////////////////////////////////////////////////////////////////
PaginRec
(press F5 without any input parameter)
it gives an error msg like
Msg 201, Level 16, State 4, Procedure PaginRec, Line 0
Procedure or Function 'PaginRec' expects parameter '@PageNum', which was not supplied.
my question is that how can i catch this exception and display my own msg like
Please enter the right number of Parameters or whatever it is.
thanx in adv
learner
|
|
|
|
|
This exception occurs before your stored procedure is called, So you don't have control over it from the stored procedure itself.
You can handle it from the calling application or make all SP parameters have default values equal to NULLs, and check the values in the stored procedure. If they are NULLs then you can raiserror[^]
|
|
|
|
|
thanx for ur expalaination
actually someone already told me that set varaibles as null
but i wasnt satisfied any way now i got it.
and one thing more if u have any helpfull example for try catch block cos lot of simplest form is availabel on different sites but i m luking for effective one.
fnx
learner
|
|
|
|
|
Hey, i have a table with several columns Q1 to Q9, ID, datetime, ID is the same for a given group of data. Each column Q1 to Q9 can have up to 12(-2 - 9) values in it, and 10 to hundreds of thousands of rows per ID(dont ask it does make sense i just dont feel like explaining it). and i'm trying to make a report in CR 2008 but i cant get it generate graphs of all the occurances of values in all 9 columns w/out 9 graphs. so the only solution i can think of is to change the columns in to rows and make the differnt possible values columns like
Question -2s -1s 0s 1s 2s ...9s
Q1 3 0 0 2 1 (a count of the number of times each value appears)
i'm managed it with this, long but simple, and suprisingly speedy
SELECT 'Q1' AS Question,
COUNT(Q1) - COUNT(NULLIF(Q1,-2)) AS '-2s',
COUNT(Q1) - COUNT(NULLIF(Q1,-1)) AS '-1s',
COUNT(Q1) - COUNT(NULLIF(Q1,0)) AS '0s',
COUNT(Q1) - COUNT(NULLIF(Q1,1)) AS '1s',
COUNT(Q1) - COUNT(NULLIF(Q1,2)) AS '2s',
COUNT(Q1) - COUNT(NULLIF(Q1,3)) AS '3s',
COUNT(Q1) - COUNT(NULLIF(Q1,4)) AS '4s',
COUNT(Q1) - COUNT(NULLIF(Q1,5)) AS '5s',
COUNT(Q1) - COUNT(NULLIF(Q1,6)) AS '6s',
COUNT(Q1) - COUNT(NULLIF(Q1,7)) AS '7s',
COUNT(Q1) - COUNT(NULLIF(Q1,8)) AS '8s',
COUNT(Q1) - COUNT(NULLIF(Q1,9)) AS '9s'
FROM table
UNION
-- repeat query/union for all 9 Q columns
personally i think this way sucks, and there has to be a better one, but i'm at a loss as to what it is. Any help would be appreciated, oh and because i'll know very little about the data before running the report and it will be accessed from a web app i dont think a temp table would work very well either, and it wouldnt be as simplistic.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
My company will be moving from SQL Server 7 to 2005 in the near future.
There are only 3 of us in IT, none of whom are DBAs 
Is there a good book out there that will help us set up 2005 effectively, otherwise we might have to get £££contractor in.
Maybe the internet is as good a place as any but I am wary of being misled by "experts" and trying to decipher half-baked articles with no proper examples.
Thanks in advance.
I still remember having to write your own code in FORTRAN rather than be a cut and paste merchant being pampered by colour coded Intellisense - ahh proper programming - those were the days
|
|
|
|
|
I must admit that I like the books from the Microsoft Press series.
The "administrator's companion" is a good help to start setting up databases, so you can see whether you can avoid contractors.
|
|
|
|
|
Hi everyone,
I am a beginner to mysql and msmq. How to make the automatic data transaction from msmq to mysql.
I develop the application using asp.net with c#. In the application,the user entered data is saved into the Msmq. These msmq data need to be transferred to mysql database for the particular time period. Please help me regarding this. My application is too slow when I execute it due to bulk of data in the database.
Please help me regard this.
Is there any other way to improve the speed of the application running with mysql database, please guide me.
Thanks in Advance,
J. Mohan.
I see the God face in the helping people
|
|
|
|
|
Hi,I want to create OLAP cube for my database for generating reports.My database is not in star schema.I have tried without star schema but its not giving appropriate results sometimes.I have seen exmples with adventure works database which looks like a star schema database.Is it necessary for database to be in Star Schema before creating cube ?
Please tell me any link to start with OLAP cube.
|
|
|
|
|
Can u explain the meaning of these normal forms
Third Normal Form (3NF)
Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF)
A relation is in 4NF if it has no multi-valued dependencies.
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Why, can't you do your own homework?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
First CP Normal Form: Google for [^], [^]!
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
These links seems to ne inaccessible.
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Hence go to Wikipedia (English) home page and type "Third Normal Form" in the search text box.
Then ... (Now I suppose you know what to do with "Fourth Normal Form").
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
CPallini wrote: (Now I suppose you know what to do with "Fourth Normal Form").
The last time I heard someone tell another what to do with their 4th Normal form was shortly before the bar brawl broke out.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
Your comment applies only if my sentence is taken out of its context.
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
Hi Friends,
I hv got 2 fields in a table. Year and title.
year Title
1945 t1
1945 t2
1945 t3
i want a query to retrieve answer like foll. :
1945 //Field name
t1
t2
t3
1945 should be a field name and under that i need 3 rows where i can display title values..
Thanks in advance
|
|
|
|
|
Why exactly do you need something like this?
It looks weird. You want do mix pieces of different information in the same column.
|
|
|
|
|
Use Union
select distinct Year as [ColumnName] from myTable
union
select Title from myTable
Use group by in first query if required in your circumstance.
|
|
|
|
|
Thanks yaar. It works..
But can we get a output like this :
1945 1950 1960
t1 t4 t6
t2 t5 t7
t3
i hv also added group by. But it doesn't work..
|
|
|
|
|
What you are trying to achieve (I think) is known as pivoting. This sample should point you in the right direction.
create table UserArea(Country varchar(20))
insert into UserArea
select 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'
DECLARE @SQL nvarchar(4000)
SET @SQL=''
SELECT @SQL= @SQL +
'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'
FROM (select distinct Country from UserArea) as a
select @SQL = left(@SQL,len(@SQL)-1)
SET @SQL='SELECT ' + @SQL + ' FROM UserArea'
EXEC(@SQL)
drop table UserArea
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi Niraj Here ,
I hope this will work for you....
try this..
Select fieldnamewhich containst1t2ec as [1945]
From Tablename
Where year = 1945
Have a noce Day..
Stay Tune .....
Take Care....
|
|
|
|
|
Any ideas? I'm coming across this, and it makes no sense to me.
Marc
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
