|
|
I am trying to restore 2 bak files which was created using SQL Server 2000. However, I am now working with SQL Server 2005 and it wouldn't let me do so. Instead, I these 2 error messages for each file.
Restore failed for Server 'C0K69319'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\Snapshot.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
----
error for the second backup file:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
Since I am new to SQL Server 2005, any help would be greatly appreciated.
John
"Some of the world's most greatest feats were made by people not smart enough to know that they were impossible"
|
|
|
|
|
Ok, I have a connection string from ADO.Net 2.0 that is named this, name="PDS.LSS.Properties.Settings.StateConnectionString" from my app.config file. This is a dummy database that is used just as a framework for the app.
When the user starts the app they do a File-Open and select the database that they are making changes to, this is the same structure as the dummy.
I have been looking at the ConnectionStringBuilder but can't seem to make sense of how do I replace the DataSource= section of this string, since all of the bound datasets seem to pull the connection string from here!
I am setting a text string to the path of the new datasource, I just can't find the right syntax structure to say dbpath = XXXXX???
Help!!!! Thanks everyone!
David
You are only as good as your last project.
|
|
|
|
|
hello,
I am wondering if it is possible to get a specific user group from my Ms Access database.In my application the user menu depends on the user group.So I need to assign a specific menu for each group of users. My connection string contains a UserId and Password attribute. What I'm left with is how to get to know the user group once the connection is established. I know of a method "Is_Member()" in sql Server but unfortunatelly it is not recognized by Access db.I need to implement a method for doing that and I couldn't get access to sysusers or sysfilegroups tables like in Sql. I'm new to MS Access so if any body can give me a hint.By the way I'm using C# ,MS Access, OleDb.
Thank you
|
|
|
|
|
Hi,
I know this is very easy... I just don't know how to do it:
We have the following table named Wzip_Detail
[Wzip_Detail_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarehouseNum] [int] NULL
Wzip_Detail_ID will be automatically populated.
CountryCode will always be 1
WarehouseNum will always be 1
HOWEVER:
PostalCode for row 1 will be:
00000
PostalCode for row 2 will be:
00001
PostalCode for row 3 will be:
00002
PostalCode for the last row will be:
99999
So there will be rows 00000 to 99999 incrementally a total of 100,000 rows (I think)
How do I do this programatically in SQL Query Analyzer.
Again, I'm sure it is very easy... I just don't know how to do it yet....
Thank you so much in advance for any help you can give,
Anne
|
|
|
|
|
Here's a way to populate the PostalCode column. I would set the default value of the columns CountryCode and WarehouseNum to 1 instead of explictly inserting the value 1 each time.
<br />
DECLARE @PostalCode varchar(10)<br />
DECLARE @Number int<br />
SET @Number = 0<br />
<br />
WHILE @Number < 100000<br />
BEGIN<br />
<br />
IF LEN(@Number) = 1<br />
BEGIN<br />
SET @PostalCode = '0000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 2<br />
BEGIN<br />
SET @PostalCode = '000' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 3<br />
BEGIN<br />
SET @PostalCode = '00' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 4<br />
BEGIN<br />
SET @PostalCode = '0' + CAST(@Number AS varchar)<br />
END<br />
ELSE<br />
IF LEN(@Number) = 5<br />
BEGIN<br />
SET @PostalCode = CAST(@Number AS varchar)<br />
END<br />
<br />
<br />
INSERT INTO Wzip_Detail(PostalCode)<br />
VALUES(@PostalCode)<br />
<br />
<br />
SET @Number = @Number + 1<br />
END<br />
|
|
|
|
|
Thank you so much John for your response and your very clean programming style!!!
 Anne
|
|
|
|
|
Using REPLICATE() is a little shorter:
DECLARE @PostalCode varchar(10)
DECLARE @Number int
DECLARE @cTemp varchar(10)
SET @Number = 0
WHILE @Number < 100000
BEGIN
SET @cTemp = CAST(@Number as VARCHAR(10))
SET @PostalCode = REPLICATE('0',5-LEN(@cTemp)) + @cTemp
INSERT INTO Wzip_Detail(PostalCode)
VALUES(@PostalCode)
SET @Number = @Number + 1
END
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
hi all,
which one faster? ODP.NET or System.Data.OracleClient.
I do a test on my computer. the result following:
--
System.Data.OracleClient
100
00:00:03.0156250
10'000
00:00:03.0156250
100'000
00:00:04.2187500
1'000'000
00:00:13.5000000
Oracle.DataAccess.Client
100
00:00:03.1875000
10'000
00:00:13.3750000
100'000
00:01:53.0000000
1'000'000
(I don't test this item  I think it will be take a long time, so I don't test it.)
--
my computer environment
os: windows 2003 server enterprise edition - sp1
computer:
Intel(R) Celeron(R) CPU
2.53 GHz
2.55 GHz, 736MB of RAM.
--
How do you think about this? I thought the ODP.NET would be faster that System.Data.OracleClient before test.
|
|
|
|
|
|
when is the SQL sentence? in a .cs file? or in the Database procedure?
if it's in a .cs file, you can using
<br />
string[] strNumbers = { "1", "2", "3", "4", "5"};<br />
string strResult = string.Join(",", strNumbers);<br />
if it's in a database procedure, you need declare a variable for it,
then execute by Execute command. and treated the whole sentence as a string variable.
hoping this help.
|
|
|
|
|
Hi,
i have one table, the columns are ParentInvId(bigint),Invids(bigint)
now iam going to update the for a set of invids whoose paretnId is 2
for this how can i write a parameterized query using in statement
update(long ParentInvId,string Invids)
{
In this method i need to write update query
}
can u send the solution for this
|
|
|
|
|
sorry, we won't send you anything. we just want to tell you how to do it.
maybe you want using "update" sentence?
this is a sample:
update tablename set invids = 'newids' where parentnId = 2
|
|
|
|
|
A parameterized query uses parameters (yeah, really! ) and it is a little different depending on the .NET data provider you use. For SQL Server, it might look like this:
using(SqlConnection conn = new SqlConnection("conn string"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE Customers SET Phone = @Phone WHERE CustomerId = @ID",
conn);
cmd.Parameters.Add("@Phone", newPhoneNumber);
cmd.Parameters.Add("@ID", customerId);
cmd.ExecuteNonQuery();
}For the generic OLE DB Data Provider, you use ? for each parameter, and must provide them in the order they appear on the query.
I hope this gives you an idea.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
-- modified at 1:50 Thursday 25th May, 2006
|
|
|
|
|
hi all,
i want to query that create forenkey in my second table.
pls give the query
vipin paliwal
|
|
|
|
|
you must define diagram for your database that can define foriegn keys between your tables
|
|
|
|
|
What database are you using?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi,
I am trying to update a dbf table using ado. If I were working with an access database I would update the datetime field with the following string:
UPDATE MyTable SET MyDateTimeField = #2006/05/23 17:00:00# WHERE . . .
How could a do it for a dBase table, I mean, how to specify the date and time in the SQL string?
Thanks in advance,
Vinicius
|
|
|
|
|
Use braces (not parenthesis) around the date instead of the octothorpe:
UPDATE MyTable SET MyDateTimeField = {05/23/2006 17:00} WHERE...
There are other formats too:
{05/23/2006 03:00:00 PM} or {^2006/01/01 17:00}, etc.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hey guys, need to format a column of results to display as columns in another query.... let me explain a little better *hopefully*
in the result set below notice how the empID is repeated in some cases
ID empID phone_num displayTel type
----------- ----------- ------------ ---------- -----------
159 625 123-123-4567 1 1
163 625 465-456-4657 1 1
154 626 944-789-4515 1 1
155 626 984-321-4567 1 2
150 639 984-266-3336 1 1
145 802 465-465-7894 1 5
146 802 654-798-4657 1 6
165 853 132-123-1324 1 1
i need to beable to separate the "phone_num" data based on the "type" and then display that data in-line on another query
i.e.
Not This!!!
Name empID phone_num activeUser contact
----------- ----------- ------------ ---------- -----------
Demo User 625 123-123-4567 1 1
Demo User 625 465-456-4657 1 1
Instead Like This!!
Name empID phone_num1 phone_num2 activeUser contact
----------- ----------- ------------ ------------ ---------- -----------
Demo User 625 123-123-4567 465-456-4657 1 1
I need to be able to do this all at once while i'm also quering about 15 other tables.... so i need to be able to do it in-line or subqueried...
my only idea would be to do something like this ('course it won't work))
<br />
SELECT col1, col2, (if (select phone_num from phone where empID = @empIDNum) > 1 <br />
{<br />
for i = 1 to (select count(phone_num)) <br />
select top (i) phone_num as phone_num+(i) from Phone where empID = @empIDNum <br />
Next}<br />
)<br />
else<br />
{<br />
select phone_num from phone where empID = @empIDNum<br />
}<br />
from employees<br />
thanks!!!
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Can you use a stored procedure?
As far as I know, you cannot do a cross-tab sql statement in SQL Server. There are ways to do it in a stored procedure though.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
EricDv,
Yes I can use a SP, my ultimate goal out of all this is to setup a dataset, for select and update
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
It isn't pretty and I don't know how it will perform, - but here is one solution:
DECLARE @iColumns INT, @iCounter INT, @sql VARCHAR(2500)
SET @iCounter = 1
SET @sql = ''
select @iColumns = (SELECT TOP 1 COUNT(*) AS cnt FROM phonenumbers GROUP BY empid ORDER BY cnt DESC)
WHILE (@iCounter <= @iColumns)
BEGIN
SET @sql = @sql + ', Phone' + CAST(@iCounter AS VARCHAR) + ' VARCHAR(12)'
SET @iCounter = @iCounter + 1
END
SET @sql = 'CREATE TABLE #CrossPhones (empid INT' + @sql + ');'
+ 'DECLARE @sql2 VARCHAR(100),@iCounter INT,@iEmpIDHolder INT, @iEmpID INT, @phone_num VARCHAR(12), @teltype INT;'
+ 'DECLARE curNumbers CURSOR FOR SELECT empid,phone_num,teltype FROM phonenumbers p ORDER BY empid,teltype;'
+ 'OPEN curNumbers;'
+ 'FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ 'WHILE @@FETCH_STATUS = 0'
+ 'BEGIN'
+ ' SET @iEmpIDHolder = @iEmpID;'
+ ' INSERT INTO #CrossPhones (empid) VALUES (@iEmpID);'
+ ' SET @iCounter = 0;'
+ ' WHILE @iEmpIDHolder = @iEmpID'
+ ' BEGIN'
+ ' SET @iCounter = @iCounter + 1;'
+ ' SET @sql2 = ''UPDATE #CrossPhones SET Phone'' + CAST(@iCounter AS VARCHAR) + ''='''''' + CAST(@phone_num AS VARCHAR) + '''''' WHERE empid='' + cast(@iEmpID as VARCHAR);'
+ ' EXEC(@sql2);'
+ ' FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ ' IF @@FETCH_STATUS != 0 BREAK;'
+ ' END;'
+ 'END;'
+ 'CLOSE curNumbers;'
+ 'DEALLOCATE curNumbers;'
+ 'SELECT e.name,c.* FROM #CrossPhones c INNER JOIN Employees e ON c.empid = e.empid;'
EXEC(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
