Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>|||Hi Bill,
Ok then 'Why the go statement didn't work'
The reason is that the GO statement only works through
osql and isql (or in this case Query Analyser), as your
running this through VB, the go statement isn't supported
so it doesn't work.
The second error is a standard sql error, which I don't
think has anything to do with the go statement.
Could you possibly post the first 30 lines of your code ?
Thanks
J
>--Original Message--
>I know that most of the persons could not get me exactly
>so i am again going to clarify my problem.
>I want to create tables and views from inside the VB-code
>using SQL-script. I can do this by reading a text
(script)
>file, storing the string in a variable say MYSQLSTR and
>running it like CN.EXECUTE MYSQLSTR inside my code. Now I
>have created the sql-script from enterprise manager which
>includes GO for each create statement of the table. I run
>it from inside my VB-Code. error raised which says 'GO'
is
>not recognizable. OK. I removed 'GO' from the file and
run
>it again as i described above. It is running fine and
>creating tables in the database. This file, after
removing
>all the 'GOs' runs inside the query analyser too.
>Now when I do this with VIEWS, same problems come and I
>remove 'GO' but this time it says like (even inside query
>analyser):
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>10
>Incorrect syntax near the keyword 'SET'.
>Server: Msg 111, Level 15, State 1, Procedure DmView1,
>Line 16
>'CREATE VIEW' must be the first statement in a query
batch.
>Now I have to create views at one go as i create all the
>tables at one go by running the SQL-script through my
code.
>ur suggestions are welcome.
>Billi98
>.
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98
Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>sql

Receive "Acquiring a connection requires a valid Task name" when

Hi All,
I'm creating "DTSExecuteSQLTask" Task in DDQ ActiveX script code. Then
I'm trying to execute it from within this code with command:
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
but I receive error message:
"Acquiring a connection requires a valid Task name".
What's the matter?
Following is my activx script code
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "delete from tmp_table"
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function
What's wrong?
Thanks in advance.I have found the resolution, after you create the task, you need
create a step object whcih will refers this new created task, you also
need add the task to current package. And then execute the step, you
can get the result. After the step complets, you need to remove the
task from package.
This solution works for me, but I do not know this is the best
solution or not. If anybody has more better solution, please post it.
Thanks a lot.
Following is the code which can run on my SQL Server 2000 Enterprise
Edition
'***************************************
*******************************
' Visual Basic ActiveX Script
'***************************************
*********************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
'create task itself
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "select * from test_table"
oPkg.tasks.add oTask
'associate step with a task
Dim oStep
Set oStep = oPkg.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
'run step
oStep.execute
'remove the added customer task
Dim index
For index=1 To oPkg.tasks.count
If (oPkg.tasks.item(index).name = "DTSTask_DTSExecuteSQLTask_1")
Then
oPkg.tasks.remove index
Exit For
End If
Next
Set oStep = Nothing
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function

Receive "Acquiring a connection requires a valid Task name" when

Hi All,
I'm creating "DTSExecuteSQLTask" Task in DDQ ActiveX script code. Then
I'm trying to execute it from within this code with command:
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
but I receive error message:
"Acquiring a connection requires a valid Task name".
What's the matter?
Following is my activx script code
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "delete from tmp_table"
oCustomerTask.execute oPkg, Nothing, Nothing,
DTSTaskExecResult_Success
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function
What's wrong?
Thanks in advance.I have found the resolution, after you create the task, you need
create a step object whcih will refers this new created task, you also
need add the task to current package. And then execute the step, you
can get the result. After the step complets, you need to remove the
task from package.
This solution works for me, but I do not know this is the best
solution or not. If anybody has more better solution, please post it.
Thanks a lot.
Following is the code which can run on my SQL Server 2000 Enterprise
Edition
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg, startDate, oTask, oConnection, oCustomerTask
Dim oResult
set oPkg = DTSGlobalVariables.parent
startDate = DTSGlobalVariables("startDate")
Set oConnection = oPkg.Connections("db_conn2")
'create task itself
Set oTask = oPkg.tasks.new("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomerTask = oTask.CustomTask
oCustomerTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomerTask.description = "test task"
oCustomerTask.ConnectionID=oConnection.id
oCustomerTask.CommandTimeout=0
oCustomerTask.OutputAsRecordset = False
oCustomerTask.SQLStatement = "select * from test_table"
oPkg.tasks.add oTask
'associate step with a task
Dim oStep
Set oStep = oPkg.Steps.New
oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
'run step
oStep.execute
'remove the added customer task
Dim index
For index=1 To oPkg.tasks.count
If (oPkg.tasks.item(index).name = "DTSTask_DTSExecuteSQLTask_1")
Then
oPkg.tasks.remove index
Exit For
End If
Next
Set oStep = Nothing
Set oCustomerTask = Nothing
set oTask=Nothing
Set oConnection=Nothing
set oPkg=nothing
Main = DTSTaskExecResult_Success
End Function

Rebuild .LDF File

I'm running SQL Server 2000, trying to rebuild a
transaction log since I only have an .MDF file. I've tried
creating a new database with the same name, stopping SQL
Server, then inserting the real data file, and setting the
database to emergency mode, then running the DBBC
Rebuild_Log('dbname', 'c:\somepath') and that command
appears to execute ok in Query Analyzer, but the log file
is not created. Is there any other method for attaching
the .mdf when you don't have the valid .ldf that goes with
it. (I've tried sp_attach_single_file) and that doesn't
work either. I get an error message that says it can't
find the log file (this db was pulled off of another
system that had the log file path stored pointed to
another drive). Any help is appreciated.
ThanksSounds like you have tried "sp_attach_single_file_db" (although this isn't
what you typed). This command to use to attach a database that only has a
the data file. I would review BOL and try this command again. Also is it
possible that you data file is corrupted?
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:382a01c4a55e$ce066150$a301280a@.phx.gbl...
> I'm running SQL Server 2000, trying to rebuild a
> transaction log since I only have an .MDF file. I've tried
> creating a new database with the same name, stopping SQL
> Server, then inserting the real data file, and setting the
> database to emergency mode, then running the DBBC
> Rebuild_Log('dbname', 'c:\somepath') and that command
> appears to execute ok in Query Analyzer, but the log file
> is not created. Is there any other method for attaching
> the .mdf when you don't have the valid .ldf that goes with
> it. (I've tried sp_attach_single_file) and that doesn't
> work either. I get an error message that says it can't
> find the log file (this db was pulled off of another
> system that had the log file path stored pointed to
> another drive). Any help is appreciated.
> Thanks|||If sp_attach_single_file doesn't work then see
http://www.nigelrivett.net/RecoverCorruptDatabase.html
"Steve" wrote:
> I'm running SQL Server 2000, trying to rebuild a
> transaction log since I only have an .MDF file. I've tried
> creating a new database with the same name, stopping SQL
> Server, then inserting the real data file, and setting the
> database to emergency mode, then running the DBBC
> Rebuild_Log('dbname', 'c:\somepath') and that command
> appears to execute ok in Query Analyzer, but the log file
> is not created. Is there any other method for attaching
> the .mdf when you don't have the valid .ldf that goes with
> it. (I've tried sp_attach_single_file) and that doesn't
> work either. I get an error message that says it can't
> find the log file (this db was pulled off of another
> system that had the log file path stored pointed to
> another drive). Any help is appreciated.
> Thanks
>|||ok, that's what I meant, I didn't check the exact syntax,
but the "sp_attach_single_file_db" doesn't work... I don't
believe the .mdf data file is corrupted. All error
messages seem to point to the log file.
>--Original Message--
>Sounds like you have tried "sp_attach_single_file_db"
(although this isn't
>what you typed). This command to use to attach a
database that only has a
>the data file. I would review BOL and try this command
again. Also is it
>possible that you data file is corrupted?
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Steve" <anonymous@.discussions.microsoft.com> wrote in
message
>news:382a01c4a55e$ce066150$a301280a@.phx.gbl...
>> I'm running SQL Server 2000, trying to rebuild a
>> transaction log since I only have an .MDF file. I've
tried
>> creating a new database with the same name, stopping SQL
>> Server, then inserting the real data file, and setting
the
>> database to emergency mode, then running the DBBC
>> Rebuild_Log('dbname', 'c:\somepath') and that command
>> appears to execute ok in Query Analyzer, but the log
file
>> is not created. Is there any other method for attaching
>> the .mdf when you don't have the valid .ldf that goes
with
>> it. (I've tried sp_attach_single_file) and that doesn't
>> work either. I get an error message that says it can't
>> find the log file (this db was pulled off of another
>> system that had the log file path stored pointed to
>> another drive). Any help is appreciated.
>> Thanks
>
>.
>|||Hi Steve.
I have a feeling that the reason it didn't work is because
you did not detatch the database first using sp_detach_db,
though that is just a gut feeling.
Unfortunatly now you have a problem, anyway you have three
choices
1. Use Nigels very interesting and funky method to restore
it
2. Restore it from backup
3. Have a pretend nervous breakdown, take down your pants
and run from the office (be it slowly) screaming (that
SHOULD stop your boss from shouting at you).
nb if you have the original database try it again either
back it up, or try an sp_detatch_db.
Peter
"There is no such thing as public opinion. There is only
published opinion."
Winston Churchill
>--Original Message--
>I'm running SQL Server 2000, trying to rebuild a
>transaction log since I only have an .MDF file. I've
tried
>creating a new database with the same name, stopping SQL
>Server, then inserting the real data file, and setting
the
>database to emergency mode, then running the DBBC
>Rebuild_Log('dbname', 'c:\somepath') and that command
>appears to execute ok in Query Analyzer, but the log file
>is not created. Is there any other method for attaching
>the .mdf when you don't have the valid .ldf that goes
with
>it. (I've tried sp_attach_single_file) and that doesn't
>work either. I get an error message that says it can't
>find the log file (this db was pulled off of another
>system that had the log file path stored pointed to
>another drive). Any help is appreciated.
>Thanks
>.
>|||Ok, I just tried your instructions and it didn't work.
The database is in emergency mode, and I can't see any of
the tables, and when I try to setup a dts, I get an error
saying that it can't initialize the database.
>--Original Message--
>If sp_attach_single_file doesn't work then see
>http://www.nigelrivett.net/RecoverCorruptDatabase.html
>"Steve" wrote:
>> I'm running SQL Server 2000, trying to rebuild a
>> transaction log since I only have an .MDF file. I've
tried
>> creating a new database with the same name, stopping
SQL
>> Server, then inserting the real data file, and setting
the
>> database to emergency mode, then running the DBBC
>> Rebuild_Log('dbname', 'c:\somepath') and that command
>> appears to execute ok in Query Analyzer, but the log
file
>> is not created. Is there any other method for attaching
>> the .mdf when you don't have the valid .ldf that goes
with
>> it. (I've tried sp_attach_single_file) and that doesn't
>> work either. I get an error message that says it can't
>> find the log file (this db was pulled off of another
>> system that had the log file path stored pointed to
>> another drive). Any help is appreciated.
>> Thanks
>.
>|||I'll buy that... I received the file from a client, who
probably just did a standard detach through EM. I'll
probably have to resort to trying to get a hold of
the .BAK file. However I might pull the 3rd suggestion
just for fun.
Thanks
>--Original Message--
>Hi Steve.
>I have a feeling that the reason it didn't work is
because
>you did not detatch the database first using
sp_detach_db,
>though that is just a gut feeling.
>Unfortunatly now you have a problem, anyway you have
three
>choices
>1. Use Nigels very interesting and funky method to
restore
>it
>2. Restore it from backup
>3. Have a pretend nervous breakdown, take down your pants
>and run from the office (be it slowly) screaming (that
>SHOULD stop your boss from shouting at you).
>nb if you have the original database try it again either
>back it up, or try an sp_detatch_db.
>Peter
>"There is no such thing as public opinion. There is only
>published opinion."
>Winston Churchill
>
>>--Original Message--
>>I'm running SQL Server 2000, trying to rebuild a
>>transaction log since I only have an .MDF file. I've
>tried
>>creating a new database with the same name, stopping SQL
>>Server, then inserting the real data file, and setting
>the
>>database to emergency mode, then running the DBBC
>>Rebuild_Log('dbname', 'c:\somepath') and that command
>>appears to execute ok in Query Analyzer, but the log
file
>>is not created. Is there any other method for attaching
>>the .mdf when you don't have the valid .ldf that goes
>with
>>it. (I've tried sp_attach_single_file) and that doesn't
>>work either. I get an error message that says it can't
>>find the log file (this db was pulled off of another
>>system that had the log file path stored pointed to
>>another drive). Any help is appreciated.
>>Thanks
>>.
>.
>|||Other ways of stopping you boss from shouting at you
include superglue...
>--Original Message--
>I'll buy that... I received the file from a client, who
>probably just did a standard detach through EM. I'll
>probably have to resort to trying to get a hold of
>the .BAK file. However I might pull the 3rd suggestion
>just for fun.
>Thanks
>
>>--Original Message--
>>Hi Steve.
>>I have a feeling that the reason it didn't work is
>because
>>you did not detatch the database first using
>sp_detach_db,
>>though that is just a gut feeling.
>>Unfortunatly now you have a problem, anyway you have
>three
>>choices
>>1. Use Nigels very interesting and funky method to
>restore
>>it
>>2. Restore it from backup
>>3. Have a pretend nervous breakdown, take down your
pants
>>and run from the office (be it slowly) screaming (that
>>SHOULD stop your boss from shouting at you).
>>nb if you have the original database try it again either
>>back it up, or try an sp_detatch_db.
>>Peter
>>"There is no such thing as public opinion. There is only
>>published opinion."
>>Winston Churchill
>>
>>--Original Message--
>>I'm running SQL Server 2000, trying to rebuild a
>>transaction log since I only have an .MDF file. I've
>>tried
>>creating a new database with the same name, stopping
SQL
>>Server, then inserting the real data file, and setting
>>the
>>database to emergency mode, then running the DBBC
>>Rebuild_Log('dbname', 'c:\somepath') and that command
>>appears to execute ok in Query Analyzer, but the log
>file
>>is not created. Is there any other method for attaching
>>the .mdf when you don't have the valid .ldf that goes
>>with
>>it. (I've tried sp_attach_single_file) and that doesn't
>>work either. I get an error message that says it can't
>>find the log file (this db was pulled off of another
>>system that had the log file path stored pointed to
>>another drive). Any help is appreciated.
>>Thanks
>>.
>>.
>.
>

Really need urgent help in Insert statement

Hi,

I am creating a event registration system. Right now my program is able to insert user's registered event into the database. This is the code i wrote:

INSERT INTO EventRegistration(eventId,userId,status) VALUES('" + eid + "','" + id + "','" + status + "')

However I notice that same user are able to register the same event when i use this code. How should i improve my code in order to prevent same user from registering the same event. Which means my sql statement will not insert registered event into the database if same user register the same event. I will really appreciate the help all of you offer.

Thank you.

Regards,
ferDepending on your database tool, you probably just need a UNIQUE CONSTRAINT or a UNIQUE INDEX and things should be lovely.

-PatP|||As Pat.P infers good table design should preclude you users entering duplicate data.

What makes an event / entry unique in the real world and how do you represent that in your database?|||if you are providing the user a list of options to register, I assume you are pulling the list of things to register from the database too ?

only show them options to register for, that they have not registered for already.

for instance, if you have 10 time slots available for something and someone picks time slot #3, then the next user will only see 9 time slots to pick from. (#3 is no longer displayed)|||Hi Kropes2001,

Ya you actually get wat i mean. I am providing a list of options for user to register, and options(events) are retrieve from the database. I get wat u mean but i dun realli noe how to implement it as i am new to sql. Can you provide me a sample coding of wat u mean?

Thank you.

ferlina|||If you define the primary key, or alternatively a unique key correctly there can't be any duplicates.

If you handle any eror thrown by the db engine then you can gracefully handle the situation where a single user has tried to make more than one booking for the same event.
HTH|||Sorry... but
why "handle an error" when you can prevent it from happening to begin with ?
i see too many programmers that do that. let the user enter whatever they want and then try to deal with all the errors it generates.
the more logical design is to simply provide the necessary information in a way that prevents the user form making any errors like that to begin with. and its usually a lot easier too.

an ounce of prevention vs. a pound of cure.

ferlina,
i would really need to see your table designs to give you an absolute answer.

i am assuming that you have 1 table with all of the events that are available. plossible fields :
EventID
Description
StartDate
StartTime
Active
etc....
etc....

i assume that you then use a SELECT statement to build a recordset of all of the availalbe events. something like :
SELECT EventID, Description, StartDate, StartTime from EventList WHERE Active=true

i also assume that by the time you hit this page, you already know who your user is, or at least what their ID is ? (they already are defined in the database somelace, yes ?)

if so, then combine the SELECT of available events with an outer join. you want to retrieve a list of all events that are active in teh database, except for the ones that are already scheduled by this UserID.

take a look at this article

http://www.dev-archive.com/dbzone/Article/17403/0/page/4

instead of selecting all of the registered events that match the user's ID, you are selecting all of the ones that they did not register for.|||In a multi user environment you have to trap for errors and handle them gracefully. After a user has booked an event then you can exclude them from appearing in future combo select boxes, but untll they have booked you are running the risk of an error. Granted you could set a flag on the user to say they are in process of making a booking - but that doesn;t stp clients that have already loaded available users from attempting to make a duplicate booking.

It is always theoretically possible for a user to be booking an event from more than one session at the same time (either through user error or deliberate attempt to subvert the system.