|
So how can I solve this issue.
It will be great if you can guide me for it.
Thank you!
Rock Star
|
|
|
|
|
The answer is to not declare any transaction, just do the select.
For example: select ID,FullName from Customer
This will retrieve the data and not create any implicit or explicit transactions. No need to commit or rollback anything.
|
|
|
|
|
Thanx for reply
My question here is that consider I excuted an update query on a table without defining any transaction point, but now I want to roll back this update query so I can recover my data. How can I do this?
Thank u!
Rock Star
|
|
|
|
|
You need to do some work on your question construction b/c thats not what anyone would have though the OP was about.
If you are talking about recovering from an error in the statement then a transaction is required, if there was not transaction you are gone.
If you want to recover from an incorrect update you may be able to restore the database from a backup or possibly by working the transaction logs. Restore the DB to another SQL instance and compare the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have 3 tables.
1. A contact table
2. A company table
3. A company to contact association table.
If a company to contact association is created, a field, 'CompanyName', in the contact table needs to contain the name of that company (in order for our mailing lists to print correctly).
I've been trying to write a query which will fill in all of the contact table 'CompanyName' values, with all of the corresponding values in the company table.
The company to contact association table is the link between the two so:
1. I need to determine which contact's have an association with a company
2. I need to get the companyID from the association table, and get the company name
3. I need to set the contact company name to the retrieved value.
Only problem is... IM STUCK!!!
This is what i've done so far.
<br />
<br />
SELECT Contacts.CompanyName<br />
FROM MarkSandpit.dbo.Contacts<br />
INNER JOIN<br />
MarkSandpit.dbo.Associations_Companies_to_Contacts<br />
ON Contacts.mxContactID = Associations_Companies_to_Contacts.mxContactID<br />
INNER JOIN mxContact.dbo.Companies<br />
ON Associations_Companies_to_Contacts.mxCompanyID = Companies.mxCompanyID<br />
<br />
This just gives me a list of all the company names in the contact table, which have associations with a company.
Could some please help me out?
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
HI GUYS
I have installed sqlserver 2005 exp with advanced services and toolkit , but i cant see
report server ,
can any body tell me how to install a report server ?
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
|
Hi, thanks for looking
Im trying to make a query that will show me something like a report
I have a Homes table and a History table
Table Homes: Info about homes
Col: HomeID
Col: AddressID
Col: ...
Table History: A record for every status in wich a home has been
Col: HistoryID
Col: HomeID
Col: StatusID
Col: DateStarted
Col: DateFinished
Col: ...
A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this:
HomeID - Status1 - Status2 - Status3 - Status4
1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09
2 - ................
Here, im showing a home and the DateFinished column from the History table
In case that a home doesnt have statusX, null is ok
I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on
It seems really unefficient this way
Please let me know if you know another way to do it
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
Hmmm I presume you are using SQL Server 2005/8. Try this article [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can try something similar to this.
select
homeid,
max(case when statusid=1 then datefinished else null end) status1,
max(case when statusid=2 then datefinished else null end) status2,
max(case when statusid=3 then datefinished else null end) status3,
max(case when statusid=4 then datefinished else null end) status4,
from history
group by homeid;
<edit>fixed typo</edit>
modified on Friday, May 15, 2009 4:36 AM
|
|
|
|
|
Thanks lot!!!
This reduced my query a lot
I just have a question
What is the max for?? 
I removed the max as well as the group by and still works
Thanks again
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: Thanks lot!!!
You're welcome
AlexeiXX3 wrote: What is the max for??
I removed the max as well as the group by and still works
In case you have duplicated values in the table, the query will then select the last date (use Min() for the first date).
This is of course not necessary if you have HomeID and StatusID as a composite key in the table.
|
|
|
|
|
Hi thanks again 
Now i see the need for the max and group by
I had just added homes to the History with status 1 only, thats why it wouldnt make any difference, but when i inserted homes with status 2, 3 and 4, i got duplicated rows
Alexei Rodriguez
|
|
|
|
|
If I has database but in sqlite command can't write into exists dataase
sql_con = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");
how to command that write whatever database is exist !!!
I 've already use command
but it's not work
if (File.Exists("" + info + ".db"))
{
sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=False;Compress=True;");
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = "drop table par ;";
sql_cmd.ExecuteNonQuery();
}
else
{
sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=True;Compress=True;");
}
sql_con.Open();
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = "create table par (Date nvarchar(20), Time nvarchar(10), Linein NVARCHAR(20), Lineout NVARCHAR(20), Number NVARCHAR(20), Status nvarchar(10),Duration int(20), Cost nvarchar(20), Detail nvarchar(10), Network nvarchar(10));";
sql_cmd.ExecuteNonQuery();
modified on Thursday, May 14, 2009 11:03 PM
|
|
|
|
|
|
I am not sure how you are passing the binary type to the sp, I am guessing it is in error because you are passing a string to a binary field. Either convert it to binary when passing it to the sp or do a CONVERT(binary(16), @password)
I don’t believe the SQL stage is the place where you want to build you cryptology, the application should send you a encrypted string.
PS.. I assume this is MS SQL Server
John
|
|
|
|
|
Hi,
Thanks for the response.
I realised i was entering a string rather than a binary. I was simplytesting the procedure in the SQL editor to test it in preparation for use with my website. The coding of the website passes in a hashed string, hence the SQL / procedure set-up.
P.s. I did mention in the orginal post that it was an SQL2005 DB.
I deleted the post once i realised my foolish mistake.
Thanks again.
|
|
|
|
|
Hi
I am having trouble setting up SQL 2008 Express on a Novell network. I want to host the database on one of the client PCs and then connect to it from both that PC and the rest of the client PCs. However, I am unable to log in from the other client PCs. This is the first time I am setting up an SQL server, so I really have no idea where I am going wrong.
Thanks
Karl
modified on Thursday, May 14, 2009 4:56 AM
|
|
|
|
|
|
Yes, it should work. The host machine is running Windows XP but is a client on a Novell network. I'm trying to connect over TCP/IP. I am using a c# app that need to log in to the db - maybe I am setting the connection string incorrectly?
Data Source = x.x.x.x,1433\SQLEXPRESS or x.x.x.x\SQLEXPRESS,1433? ; Network Library=DBMSSOCN
Do I have to connect with a user ID and password over TCP/IP?
With the above I am not even able to connect from the host machine (it works fine when I substitute with NovellClientName\SQLEXPRESS though)
|
|
|
|
|
Like I said, I haven't used Novell in a long time, so I won't be able to duplicate anything. However, I would think you'd need to specify a username/password, unless you're specifying a Trusted_Connection. Have you checked out the connection strings bible - http://www.connectionstrings.com/sql-server-2005[^]?
Scott
|
|
|
|
|
Hay ,
I edit a stored procedure to select a Name column from various tables as the next :
Create PROCEDURE [dbo].[SelectName]<br />
@table_name sysname,<br />
@sName nvarchar(50)<br />
AS<br />
<br />
exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName )
then when i execute it and pass the next valriables
<br />
@table_name = 'emp'<br />
@sName = 'NameTest'<br />
the next error message appear
<br />
Invalid column name 'NameTest'<br />
what the error here ,,
thanks
jooooo
|
|
|
|
|
If you add a print statement to your stored procedure, such as:
print ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName
and execute it, you'll see this as output:
SELECT * FROM emp WHERE Name =NameTest
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameTest'.
From there you can see that it's trying to compare the Name column to what it interprets as another column name - not a text literal. You need to put the literal in quotes, so your procedure would need to look like:
exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =''' + @sName + '''')
That being said, that code is dangerous, and you should read up on SQL injection, e.g. SQL Injection Attacks and Some Tips on How to Prevent Them[^]
Scott
|
|
|
|
|
Thanks for reply
I use print as you said
<code>print ' SELECT * FROM ' + @table_name + ' WHERE Name ="' + @sName + '"'
it's ok no errors ,, but no rows are returned ,, for eg
@sName = "Jooo"
this name is already exit but no rows return ,,
I test the same statement but with another column type
<code>print ' SELECT * FROM ' + @table_name + ' WHERE ID =' + @iID
@iID = 1
it's ok and return the the required row,
what the diff between nvarchar and int column to get the int and nvarchar no
jooooo
|
|
|
|
|
The print statement that I mentioned was just to help you debug what was happening. Did you also change your exec statement as I recommended?
The difference between the nvarchar and int columns is that to find something in the nvarchar column you need to specify a text literal, which needs to be in quotes. If it's not in quotes it's going to interpret it as another column name. To find something in the int column you specify a numeric literal, which doesn't need to be in quotes, and the numerical literal won't be interpreted as a column name.
Scott
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
