|
Thanks for the reply, sorry i bothered you
I wish i could just copy my DB and atach it in the destination server, but i cant
Colin Angus Mackay wrote: Personally, I tend to backup the database, then restore it on the other server. But if you want a scripting option then there is a tool downloadable from the Microsoft website: Microsoft SQL Server Database Publishing Wizard 1.1[^]
I had tryed this tool before, this doesnt care about dependencies, it does script schema, data or both, but the problem is when i try to execute it, i get errors, because of SPs referencing Tables that havent been created or views calling views that havent been created
Sincerely, doesnt this suck?? Didnt the guys who programmed this think about it???
Does a real app have 4 tables and 1 SP and no reference between tables?? I dont think so.
Why didnt at least, the script is generated grouped by object type, NO, IT does mix it all: Tbale, view, sp, table, sp,...
What happens when one has many views and SPs??
Does one have to go one by one adding them?
This is what i think sucks
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: the problem is when i try to execute it, i get errors, because of SPs referencing Tables that havent been created or views calling views that havent been created
But if you try and use these SPs and views anyway they won't work. Why do you have a database that is broken in this way? When you see a dependency that doesn't exist, don't you get some sort of feeling that you want to fix it. I know I do.
AlexeiXX3 wrote: Why didnt at least, the script is generated grouped by object type, NO, IT does mix it all
That is what it says it does. It is operating within its design parameters. It is meant as a way of dumping everything in one script. It makes the descision on how best to do that.
Seriously, the only problem I've ever had with that tool is when I have varbinary(max) columns that contianed a large amount of data. I tracked the problem down to a limitation on SQL as a language rather than the tool.
AlexeiXX3 wrote: doesnt this suck?? Didnt the guys who programmed this think about it???
AlexeiXX3 wrote: This is what i think sucks
You still have a bit of anger going on here. I'm sure the folks that developed it gave it a lot of thought. I've met some of the product groups in Microsoft (although not the SQL guys I admit) and they do genuinely put a lot of effort into what they do. They give things a lot of thought and consideration. There ultimate creation may not be perfect for our particular situation but it does fit with what they've designed it for and they are always striving to improve things. Heck their vision for C# isn't there yet and it is all due to time constraints more than anything else. They have deadlines to work to and they have to get stuff out the door in a certain time frame so they have to make compromises and put stuff off to the next version. I'm sure that happens with the software you write also, it certainly happens with just about everybody I've ever spoken to.
|
|
|
|
|
Colin Angus Mackay wrote: But if you try and use these SPs and views anyway they won't work. Why do you have a database that is broken in this way? When you see a dependency that doesn't exist, don't you get some sort of feeling that you want to fix it. I know I do.
 I meant that the generated script by the tool doesnt run smoothly when i execute t on the server, because in the SP there is a referencie to a table or view not yet created
Colin Angus Mackay wrote: You still have a bit of anger going on here
Yes there is
You know.... when one is all frustrated because something doesnt work as expected
I hope, this thing about dependencies gets fixed, I think it could be as easy as one option to disable constraints in the whole DB and then reenabling them (Assuming one will execute the correct script with no errors at all) and we could forget about the creation order
Anyways, thanks for your answers
Alexei Rodriguez
|
|
|
|
|
Hi,
I Have two tables.
Table1 has the following columns:
TableID
FieldID
FieldName
FieldValue
DateAdded
and Table2 has the following Columns
FieldID
TableID
Dataadded
And I want to display Each FieldName as a Column name for Dridview control.How can i form SELECT Statement To Achieve this ?
Thanks in advance
sri
|
|
|
|
|
Don't cross post - I just answered this in the C# forum.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Since morning I am not able to access my database. It is marked as Suspect. The server was down in the morning and when it was on , I tried to access my database. However, now I am not able to access it. I even tried searching for the .mdf file.the size of .mdf is in MBs. But, I am not able to find it. I've no latest backup of my database. Now, if I dettach the database from the server or run sp_resetStatus commnad then, the database might not get recovered. So, please help. I need to fix it asap so that I could start my work.
|
|
|
|
|
|
Hi all,
I have a database on SQL Server 2005, serving several clients. Some of my queries need more than 20 seconds to finish. Problem occurs when ClientA runs the query, and one second later ClientB runs it as well. Since the query involves some editing, it will lock the table for the duration of the query. So what usually happens is that ClientB will have "query timeout expired" error message because the request cannot be completed within the default 30 second query timeout.
a. Is there any way to know whether a table/record is being locked, and by which computer?
b. Can I change the default 30 seconds query timeout by code?
Thanks a lot for any pointer.
|
|
|
|
|
A. not easily - I would look at your workflow and query and try to isolate the part where the updates to the tables are done and split them into a seperate process from the rest of the query.
B. Change the command timeout or the connection timeout.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You probably need to split your query down to isolate the actual updates, then the problem will be reduced. Take locks late and release early is the best advice I can give.
You can increase the timeout on the command or connection.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have also faced the same problem when i used multithreading with very complex query that takes 25 sec to execute. and there are multiple threads so they hit the sql server with the same query at a time which cause Time Out Expired...
I think following might help you.
You can view the Locks by executing
sp_lock
Also try these undocumented system stored procedures to get the info about blocking.
sp_who
sp_who2
Read this MSDN KB Article and Try to divide the update/delete statement by block of rows like this [^]
|
|
|
|
|
I am building a VB.Net system using MySql database from XAMPP.
The problem is, i try to run the system from other computer and change the localhost to 10.0.0.15 because the 10.0.0.15 is the ip for the computer running the database. But, i get the error message "Unable to connect to any of the specified MySQL hosts"
I do a lot of searching and i find that i need to add privilage to mysql. But i try many time and still fail. I not really sure how to add it. I am really new to it. I am using phpmyadmin running windows xp.
So, example computer A has the mysql server install(using XAMPP) and the ip adress is 10.0.0.15.
On the other hand, i install the system on computer B(IP = 10.0.0.20) and want to run the system from computer B but access the database at computer A. So, what is the thing that i need to do?
Could someone tell me what i need to add at the privileges and at the connection string, what i need to put at the 'Server' for the connection string?
Example connection string:
server=localhost; user id=root; password=; database=cegms;
Thank.
|
|
|
|
|
Shouldn't the server be 10.0.0.15 instead of localhost? Eliminate all the LAN issues if any and enable the port 3306. When you are trying out the connection, is the Mysql service running or not?
---------------------------
Both optimists and pessimists are important in technology. The optimist invented the aeroplane; the pessimist invented the parachute.
Regards,
Hesbon Ongira
Nairobi, Kenya.
|
|
|
|
|
I already change the localhost to 10.0.0.15 actually. I think the problem is on mysql. I am using phpmyadmin. Could you tell me how to enable the port 3306? And any other thing that i need to configure?
I also do some searching and found need to "grant access to remote IP address" but i does not know how to do it!
I am really new and never configure the mysql, so any detail explain of the step is appreciated.
Thank you.
|
|
|
|
|
Just Reconstruct My Question
i have the following function
public static int AddParent(ParentEntity objParentEntity)
{
return ((int)(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner + "CreateParent"
,objParentEntity.ParentName
)));
}
and this stored procedure
ALTER PROCEDURE [dbo].[CreateParent] <br />
(<br />
@ParentName nvarchar(MAX)<br />
)<br />
AS<br />
BEGIN<br />
insert into [dbo].[Parent]<br />
(<br />
[ParentName]<br />
)<br />
VALUES <br />
( <br />
@ParentName<br />
)<br />
RETURN SCOPE_IDENTITY();<br />
END
the SP work fine
the error is the follwoing
Object reference not set to an instance of an object.
i think it is when it is returning the value because when i used CONVERT.TOINT32 it retrun 0
the point from all of this is to create a recored and then take its generated ID to use it somewhere else
so can someone help me and tell me what i did wrong
modified on Monday, May 26, 2008 6:02 AM
|
|
|
|
|
You never assign any other values to it then the value in
SELECT @ParentID = ParentId FROM Parent WHERE ParentNationalNumber = @ParentNationalNumber
Is that not the correct value? If it isn't, then you must assign another value.
|
|
|
|
|
Hi all,
I m hgetting this strange message while ctreating a trigger. I can`t figure out the problem
create TRIGGER TRIG_SS_BUFFER ON INVOICE_MAIN
FOR INSERT
AS
INSERT INTO SS_BUFFER
{
SS_INV_NO,
SS_INV_USER_NO,
SS_INV_DATE,
SS_BUF_ORDER_NO,
SS_BILL_LOC_CODE,
SS_SUPP_LOC_CODE,
SS_PARTY_CODE
}
SELECT INS.INVOICE_NO, INS.INVOICE_USR_NO, INS.INVOICE_DATE, INS.INVOICE_REF_NO,
ORDER_MAIN.ORDER_BILL_LOC_CODE, ORDER_MAIN.ORDER_SUP_LOC_CODE, ORDER_MAIN.ORDER_PARTY_CODE
FROM INSERTED INS INNER JOIN
ORDER_MAIN ON INS.INVOICE_REF_NO = ORDER_MAIN.ORDER_NO
Any help would be appreciated....
When you fail to plan, you are planning to fail.
|
|
|
|
|
It was a typo. I have used '{' instead of '('. Sorry Again!!!!
When you fail to plan, you are planning to fail.
|
|
|
|
|
hai,
curretly i m using sql server 2000.
here, When i insert a record to "Table-A", an auto generated number will be stored in column "Col-AutoID" (say... 7). This 7 should be immediately stored in column "Col2-AutoID" of "Table-B".
how to proceed it with triggers?. help me. - KARAN
|
|
|
|
|
currently i m using sql server 2000.
i need a query where i would like to get the DISTINCT records from table "A" and copy to table "B".
For example...
table A consist of...
1 - testvalue
2 - testing
3 - processing
1 - testvalue
3 - processing
then the table B resultant should be of...
1 - testvalue
2 - testing
3 - processing
Since the column which going to carry the 1,2 and 3 values is PRIMARY KEY.
how to achieve it? - KARAN
|
|
|
|
|
insert into tableB select distinct col1,col2,col3 from TableA
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks blue boy... but the thing is i m having nearly 50 columns in my table "A". I want to remove the duplicate records from table "A" (depending upon the 1st column), and insert the values in table "B" as setting the 1st column as PRIMARY KEY.......
Now, my questing is as u said...
insert into tableB select distinct col1,col2,col3... col 50 from TableA
do i want to mention all the 50 columns???????
|
|
|
|
|
So do you want distinct rows or distinct based on a specific column - if its the first then you have the answer, otherwise you are not asking the right question
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How can I assign the result of a query to a variable. For example
DECLARE @FoundID VARCHAR(10)
IF NOT EXISTS(SELECT @FoundID = Employee.EmployeeID
FROM Employee
JOIN EmployeeTerritory
ON Employee.EmployeeID = EmployeeTerritory.EmployeeID
JOIN Territory
ON EmployeeTerritory.TerritoryID = Territory.TerritoryID
WHERE AssignedPostal = SUBSTRING(@CustomerPostalCode, 1, 1))
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.
Thanks
|
|
|
|
|
CodingYoshi wrote: So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.
If your SELECT results in only one row being returned then it should work. However, you are doing the assignment inside an IF (NOT) EXISTS which doesn't obey the normal rules of processing. It is designed to short circuit as soon as any matching data is found in order to allow the appropriate conditional statements to be run.
I would suggest moving the SELECT out of the IF condition and changing the IF condition to an ISNULL(@FoundId).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
