|
Hi,
This is my stored prosecure.But when i execute
exec CreateDataBases 'testdb','testdb','c:\\testdb.mdf','c:\\testdb.ldf'
It says the db already exists??
Am confused plaease help me...
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
Create PROCEDURE CreateDataBases
-- Add the parameters for the stored procedure here
(
@database nvarchar(100),
@databaseName nvarchar(100),
@mdfName nvarchar(100),
@ldfName nvarchar(100)
)
AS
declare @qry nvarchar(800)
BEGIN
select @qry = 'CREATE DATABASE ' + @database +
' ON PRIMARY (NAME= ' + @databaseName +',
FILENAME ='''+ @mdfName + ''',
SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME ='+@databaseName+',FILENAME='''+ @ldfName+''',SIZE = 1MB,
MAXSIZE = 5MB, FILEGROWTH = 10%)'
--print @qry
exec SP_Executesql @qry
END
GO
exec CreateDataBases 'testdb','testdb','c:\\testdb.mdf','c:\\testdb.ldf'
|
|
|
|
|
It probably means that there is already a "testdb" database on your server.
|
|
|
|
|
That's a pretty wild guess based on such an unclear error message... 
|
|
|
|
|
Yes,because there is no other by that name.Thats why i am so much lost.
For example i am creating the same stored procedure in another comp.But still getting the same error.
|
|
|
|
|
A database creation script is supposed to look like:
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data', FILENAME = N'C:\testdb_Data.mdf',
SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME = N'testdb_Log', FILENAME = N'C:\testdb_Log.ldf',
SIZE = 5MB, MAXSIZE = 5MB, FILEGROWTH = 10%) If uncomment your "print" statement you will find that you are giving the log the same name as the database.
Regards
Andy
|
|
|
|
|
Thanks Andy,I will check it out and keep you posted
|
|
|
|
|
Hi,
I am having the following problem AFTER converting to VS2008 from VS2005 and SQLCE 3.5 from 3.01:
SQL CE db file has a table called Court0 with various columns of type float. I populate the values by copying floats from another table/tables. I do this via ado.net using this code snippet:
foreach (DataColumn column in this.mycourtsDataSet1.Tables[tableName].Columns)
{
string columnName = column.ColumnName.ToString();
string columnValue = aRow[0][columnName].ToString();
object cValue = aRow[0][columnName];
if (columnName.Remove(1) == "T" && !string.IsNullOrEmpty(columnValue))
{
// Add the value to the Court0 table.
DataRow[] bRow = this.mycourtsDataSet1.Tables["Court0"].Select("BookingPeriod = '" + columnName + "'");
if (bRow.Length > 0)
{
double colValue = Convert.ToDouble(cValue);
//bRow[0][tableName] = Convert.ToInt32(columnValue);
========> bRow[0][tableName] = colValue; <==== colValue is '1055.01'
}
}
}
}
This works fine in VS2005/CE3.01 BUT not in VS2008/CE3.5
In CE3.5, the value entered into the cell looses it's decimal value.
For example, '1055.01' becomes '1055.0' in CE3.5 .
Can someone explain to me why the conversion stuffs up in CE3.5 and what do I do to fix it.
Thanks,
Glen Harvy
|
|
|
|
|
If you need a decimal quantity to retain its decimal representation through storage and manipulation in a program, you should be using decimal data types.
Binary floating point types are generally more effective for scientific computation, but they cannot preserve what appear to be simple decimal values. To two decimal places, only the fractions 0.00, 0.25, 0.50 and 0.75 are recorded accurately; the other 96 values are approximations.
Binary floating point records fractional values as sums of negative powers of two, that is, 1/2 + 1/4 + 1/8 + 1/16 + 1/32 + ... 0.01's closest approximation is 1/16 + 1/32 + 1/256 + 1/512 + 1/4096 + 1/8192 + 1/65536, to 16 fractional bits. This is 0.0999908447265625.
Conversions between different floating point representations can cause significant digits to be lost.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks for your response - much appreciated as I'm pulling my hair out down here!!
I have changed the SSCE 3.5 to type Money (there is no 'decimal' in SSCE) as well as the dataTable to type decimal. (I have also tried other numeric types as well in my trials and tribulations).
Providing I don't execute the following, I don't lose the decimal portion:
this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);
this.mycourtsDataSet1.AcceptChanges();
this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);
I'm almost at the stage of using strings to store my data and a hell of a lot of conversions but that seems ludicrous 
Somethings changed between VS2005/2008 and CE3.01/CE3.5 
Later ...
The problem was with VS2008 not updating the SQLce data engine during conversion. Once I "reconfigured" the dataadapter all was fixed.
Glen Harvy
|
|
|
|
|
What are advantages of using common table expression(using with statement) in SQL.
Do good and have good.
|
|
|
|
|
Will this[^] or this[^]address your concern?
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
CREATE PROCEDURE GetThreadMessages
@iThreadID int
AS
SELECT message_id,
thread_id,
user_id,
first_names,
last_name,
email,
subject,
body,
date_submitted,
category_name,
category_id,
last_edited
FROM message_view
WHERE thread_id = @iThreadID
ORDER BY date_submitted asc
PLS HELP
DELETE ?
UPDATE ?
Boya - Badana - Tadilat ve Tamirat Isleri Bir Yudum Kahve Molasi (Coffee) Dügün Nisan ve Gelinlik (wedding)
Google Reklamlarinizi Sadece Siz Yönetin Elektrik Proje Tesisat Projesi Çizimi Illere Göre Nakliyat Firmalari Istatistik
http://www.evdenevenakliyatbul.com
|
|
|
|
|
love_man001 wrote: PLS HELP
DELETE ?
UPDATE ?
What does that mean?
|
|
|
|
|
DELETE code ?
UPDATE code ?
Boya - Badana - Tadilat ve Tamirat Isleri Bir Yudum Kahve Molasi (Coffee) Dügün Nisan ve Gelinlik (wedding)
Google Reklamlarinizi Sadece Siz Yönetin Elektrik Proje Tesisat Projesi Çizimi Illere Göre Nakliyat Firmalari Istatistik
http://www.evdenevenakliyatbul.com
|
|
|
|
|
You have just repeated what you said before. You did not explain what you want.
I am guessing you want assistance with the SQL to delete information from the table and update information in the table. However, it is not clear what criteria you are using for the delete or update. You will have to explain more.
If it is this simple, you could always look in SQL Server's books online.
|
|
|
|
|
hi,
I m creating a Dynamic Query in Procedure on that time if some Syntax Error Occurs due to unformatted input how can I handle It in side procedure.If any one can guide me please help.
rup
|
|
|
|
|
I had to cast datetime-parameters to an nvarchar(50) to make the dynamic query work. Note: This had to be done within the query-string.
I also had to specify the length of an varchar-parameter to make it work.
Maybe this could help you, too.
|
|
|
|
|
|
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
I am a SysAdmin, I battle my own daemons.
|
|
|
|
|
There are two types of temporary tables. If you specify single # sign to the table name, it's local temporary table which will be unique for each session. This will be dropped automatically when connection closes. If two # sign is provided, it's global and all connections made to SQL server can access that.
|
|
|
|
|
Hi all,
I've got a problem when manipulating on float type data, I'm working on SQL Server 2005.
Here is the code:
DECLARE
@First float,
@Second float,
@Third float
SET @First = 1.22
SET @Second = 2.72
SET @Third = 3.94
SELECT @First + @Second - @Third
The result is "4.44089209850063E-16" instead "0"
I can't understand this situation
Thanks a lot
|
|
|
|
|
Use Decimal or Numeric data types. if you are looking for fixed decimal places.
float and real are represented using scientific notation.
In your case you have to use Decimal or Numeric
Regards
KP
|
|
|
|
|
Yeah floating point arithmetic isn't ever exact. 4.4 * 10^-16 is so small its pretty much zero anyway - close enough!
|
|
|
|
|
Good evening All
am creating a Procedure for a Login Screen, Obviously i want to search for a Username and password that has been passed as a Parameter. and return "0" if it has been found and If it was not Found it must return "1"
So i have Written this
<br />
<br />
ALTER Proc [prcLogin]<br />
(<br />
@Username VarChar(50), <br />
@UPassword varChar(50),<br />
@OutRes int OUTPUT<br />
)<br />
AS<br />
DECLARE @rc Int<br />
SELECT @OutRes = 1<br />
FROM [Login] <br />
WHERE Username = @Username And UPassword = @UPassword<br />
IF @@RowCount > 0 <br />
BEGIN<br />
SELECT @rc = 0<br />
RETURN @rc<br />
END<br />
ELSE<br />
BEGIN<br />
SELECT @rc = 1<br />
RETURN @rc<br />
END<br />
<br />
Well i dont Get an Error with this code here is how i use it
<br />
Public Function Check_Login(ByVal Username As String, ByVal Password As String) As Boolean<br />
<br />
con = New SqlConnection(strcon)<br />
<br />
cmdselect = New SqlCommand<br />
<br />
cmdselect.CommandTimeout = 0<br />
<br />
cmdselect.CommandType = CommandType.StoredProcedure<br />
<br />
cmdselect.CommandText = "prcLogin"<br />
<br />
cmdselect.Parameters.Add("@Username", SqlDbType.VarChar, 50, "UserName")<br />
<br />
cmdselect.Parameters("@Username").Value = Username<br />
<br />
cmdselect.Parameters.Add("@UPassword", SqlDbType.VarChar, 50, "UPassword")<br />
<br />
cmdselect.Parameters("@UPassword").Value = Password<br />
<br />
cmdselect.Parameters.Add("@OutRes", SqlDbType.Int)<br />
<br />
cmdselect.Parameters("@OutRes").Direction = ParameterDirection.Output<br />
<br />
cmdselect.Connection = con<br />
<br />
Dim res As Integer<br />
<br />
Dim ds As New DataSet<br />
Try<br />
con.Open()<br />
<br />
res = cmdselect.ExecuteScalar<br />
<br />
con.Close()<br />
<br />
Catch ex As Exception<br />
<br />
MsgBox(ex.Message)<br />
<br />
End Try<br />
<br />
Return res<br />
<br />
End Function<br />
<br />
I keep on getting a "0" and that means even a Bad login will login
What am i doing Wrong here
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I am just wondering if your proc can be restructured the following way.
Create Proc [prcLogin]
@pUserName varchar(50),
@pPwd varchar(50)
as
Select count(*) from [Login] where UserName = @pUserName and Password=@pPwd
The above proc returns a value 1 for a valid login, and 0 for an invalid login (assuming that the PK constraints are set properly for the username column in your login table).
You may now remove the code that deals the output param (vb.net)
res = cmdselect.ExecuteScalar
res will now hold a value 1 for a valid login and 0 for a bad/invalid login.
HTH
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
