|
Hi,
check whether the views are converted to tables.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hello,
I have a table with a primary key on Serial No. I need to add a new column called softwareID and set that to the new primary Key.
It is possible to write some sql what will remove the PK from Serial No, and set the softwareID as the new PK?
I was thinking about dropping the table and creating a new one, but I don't want to lose the data.
Many thanks,
Steve
|
|
|
|
|
|
Hello,
I have a sql 2005 server database and using .Net 2005.
The client has a database when contains all there business data. In the currently I have been making changes to the schema i.e. creating new table, altering procedures, etc. All these has been done by writing the scrips and when the program installs it will execute these scripts. The customer gets an update every few months.
However, the updates are becoming more and more frequent and also becoming more complex and sometimes difficult to keep track of all the table's schema.
Is it possible to send them a backup so that they can restore it with all the changes I have made to the schema without destroying all their data?
Is there any software that can be used to automate this process?
What could make this difficult as we are in different offices. Development office and the customer's office in different countries.
Many thanks for any advice,
|
|
|
|
|
Try using Red-Gate SQL compare. You can use it to compare both schema and data and it can be automated using .Net scripts. The best thing to do would be to keep a backup of the schema for each series of updates. When you make a new release, create a backup. Then the next time you need to release a new set of changes, restore the last backup to a place where you can run SQL compare to generate the change scripts. Then you can send those scripts with your update to the customer.
|
|
|
|
|
hi,
Everyone know this error? the database has been placed in state by user "Admin"!
We are using a shared database in access databases. When more than one user has connect to my databases by my application (my program developed by vb.net) at the same time, the following error message appears. "The
database has been placed in a state by user 'Admin' on machine XXXXX that
prevents it from being opened or locked."
* Although i already add the "Persist Security Info=False;Mode=Share Deny None" in my OleDbConnection, it still got same error!
How to solve this error? please help!
My following code as below:
Try
DB = New OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source=C:\prdsystem\prdnsf.mdb; Persist Security Info=False;Mode=Share Deny None")
DB.Open()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "")
End
End Try
Da = New OleDb.OleDbDataAdapter(sql, DB)
Ds = New DataSet
Da.Fill(Ds, "emp")
|
|
|
|
|
I'm trying to create a SqlDbType.SmallDateTime parameter and have been unable to find the correct answer.
I'm trying to take a date in mm/dd/yyyy format from a text box and pass it to a stored procedure.
This is about as close I have got but it still doesnt work:
SelectCommand.Parameters.Add("@dateofbirth", SqlDbType.SmallDateTime).Value = DateTime.Parse(StoredProcedureValues[3]).ToShortDateString();
Does anyone know what the answer is ?
Thanks
|
|
|
|
|
When you are adding a datetime value of any sort to a database leave it in a date, I mean DATE, format if possible. Otherwise you must format it correctly to insert. So ensure that the value you are parsing to DateTime is a date value and dont set it to short date string.
SmallDateTime is about precision not how it looks in the DB[^]
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
hi every one,
well I've set up transactional replication here and the problem is that when I try to insert a new record at the subscriber it gives me an error that the primary key is being dupicated. I have the NOT FOR REPLICATION Option for the ideantity column but its not working..
can u plz help me
thanks in advance
Rocky
|
|
|
|
|
Hello EveryBody
I got 2 Table Customer and Order
Customer Table got two column CustomerID, CustomerName
Order Table got Two Column OrderID, ProductID
CustomerTable:
CustomerID CustomerName
101 James
102 Andy
103 Bryan
OrderTable:
OrderID ProductID CustomerID
1001 10001 101
1002 10002 101
1003 10003 101
1004 10004 102
1005 10005 102
1006 10006 103
I want the output as below
CustomerID CustomerName OrderID ProductID
101 James 1001,1002,1003 10001,10002,10003
102 Andy 1004,1004 10004,10005
103 Bryan 1006 10006
Please help me.
Thanks
Sarfarj Ahmed
|
|
|
|
|
SELECT Column1 & ', ' & Column2 & ', ' & Column3 & ', ' & Column4 FROM TableName WHERE Whatever Try something along those lines.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
getting error message
Msg 402, Level 16, State 1, Line 1
The data types nchar and varchar are incompatible in the boolean AND operator.
Sarfarj Ahmed
|
|
|
|
|
Try replacing the & with +.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Still Getting Error
Please send me an Example with SQL code
Thanks
Sarfarj Ahmed
|
|
|
|
|
Ahhh, you need to ensure that all types are the same.
So if you have some int datatypes along with varchar or char you need to cast the int to a varchar. CAST(IntColumn as varchar(length))
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Hello,
I was trying to build an application using sql server 2000 and asp.net which would use real time resources, and; I have been facing an issue in which when two transactions occur at the same time, down to hh:mm:ss; then in that case a deadlock occurs and none of the transactions are completed. They remain pending unless u manually goto enterprise manager and change the seconds by making them +/-1
This issue can be reduced considerably if milliseconds can be used hh:mm:ss:fff. Is there a way to implement this?
Nikhil Joshi
|
|
|
|
|
Deadlocking does not occur because you have a datetime. It occurs because you have two resources which are locking each other.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I’m trying to make a loop that will select a count of all entries that have a "TimeIn" value on the date defined by an input parameter with an hour value between x & y.
So the first pass through the loop would select a count of all rows with a TimeIn between 00:00:00 and 01:00:00 on the selected day ...
The second pass will select a count of all rows with a TimeIn between 01:00:00 and 02:00:00.
finally it will return counts for each hour in the day.
This is my first attempt at loops in SQL so any push in the right direction would be much appreciated.
Thanks.
|
|
|
|
|
SELECT
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '00:00:00' AND '01:00:00') AS 'Time1',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '01:00:00' AND '02:00:00') AS 'Time2',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '02:00:00' AND '03:00:00') AS 'Time3',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '03:00:00' AND '04:00:00') AS 'Time4',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '04:00:00' AND '05:00:00') AS 'Time5',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '05:00:00' AND '06:00:00') AS 'Time6',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '06:00:00' AND '07:00:00') AS 'Time7',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '07:00:00' AND '08:00:00') AS 'Time8',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '08:00:00' AND '09:00:00') AS 'Time9',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '09:00:00' AND '10:00:00') AS 'Time10',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '10:00:00' AND '11:00:00') AS 'Time11',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '11:00:00' AND '12:00:00') AS 'Time12',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '12:00:00' AND '13:00:00') AS 'Time13',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '13:00:00' AND '14:00:00') AS 'Time14',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '14:00:00' AND '15:00:00') AS 'Time15',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '15:00:00' AND '16:00:00') AS 'Time16',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '16:00:00' AND '17:00:00') AS 'Time17',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '17:00:00' AND '18:00:00') AS 'Time18',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '18:00:00' AND '19:00:00') AS 'Time19',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '19:00:00' AND '20:00:00') AS 'Time20',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '20:00:00' AND '21:00:00') AS 'Time21',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '21:00:00' AND '22:00:00') AS 'Time22',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '22:00:00' AND '23:00:00') AS 'Time23',
(SELECT COUNT(TimeIn) FROM TableName WHERE TimeIn BETWEEN '23:00:00' AND '24:00:00') AS 'Time24'
FROM TableName
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
If you have SQL-Server then
select convert(varchar(13), TimeIn, 120) + ':00', count(*)
from MyTable
where TimeIn between @x and @y
group by convert(varchar(13), TimeIn, 120) + ':00'
order by 1 SQL is best for set operations. You can do loops and cursors but they tend to be slower and are harder to code.
Regards
Andy
|
|
|
|
|
Ohh so that is better than mine? Cool... 
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
I sort of figured that it wouldn't work. It would be a shame for something to be easy once.
Does linking the SQL Servers only work if they are on the same machine? If not, could you give some keywords I can do some searching with. There's no reason to be humble about my ignorance now 
Thanks for your help.
OT: I see you are from Glasgow. Any ideas for some good day trips for someone visiting? I've been there twice last year for work and will going back at least once this year.
Brad
The secret to creativity is knowing how to hide your sources. - Albert Einstein
|
|
|
|
|
BRShroyer wrote: Does linking the SQL Servers only work if they are on the same machine?
You can link any SQL Servers together.
BRShroyer wrote: could you give some keywords I can do some searching with
Your starter for 10: sp_addlinkedserver[^]
BRShroyer wrote: I see you are from Glasgow. Any ideas for some good day trips for someone visiting?
Loch Lomond, Isle of Arran (aka Scotland in Miniature), Edinburgh (45 mins on the train), Falkirk Wheel.
|
|
|
|
|
Hi guys,
Please help,I am having an issue with data access block connection to the database from my class library project.
using the data access block to connect to database works fine in my website project. but i try creating a separate class library project to connect to the database i now receive an error message.
"The value can not be null or an empty string."
and this is the code:
<br />
Database motomandb = DatabaseFactory.CreateDatabase();<br />
DbCommand motomandbCommand = motomandb.GetSqlStringCommand(query);<br />
<br />
using(DbConnection motomanConnection = motomandb.CreateConnection())<br />
{<br />
DbTransaction motomanTransaction = motomanConnection.BeginTransaction();<br />
try<br />
{<br />
rowsaffected = motomandb.ExecuteNonQuery(motomandbCommand, motomanTransaction);<br />
motomanTransaction.Commit();<br />
}<br />
catch (Exception e)<br />
{<br />
motomanTransaction.Rollback();<br />
}<br />
}<br />
thanks in advance
|
|
|
|
|
any one here for giving me help that i get a problem to store store jpg files in sql or oracle (blob).
please 
i am student of ignou .
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
