Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Friday, March 30, 2012

ID Edition? General MS SQL training?

I have two questions.

First is there an easy way to tell what
edition of MS SQL Server is on a machine?
I found some code that is supposed to do
this, but if I put that code into the query
analyzer, it doesn't work. (Doesn't look
like SQL query in any case.)

A more general question: Education about SQL.

I have an MS in biostats so I'm not totally
computer illiterate, but I've never programmed
anything GUI.

Thanks for any answers

Michael Young<mcl2@.vms.cis.pitt.edu> wrote in message
news:c5eton$kp4$1@.usenet01.srv.cis.pitt.edu...
> I have two questions.
> First is there an easy way to tell what
> edition of MS SQL Server is on a machine?
> I found some code that is supposed to do
> this, but if I put that code into the query
> analyzer, it doesn't work. (Doesn't look
> like SQL query in any case.)
> A more general question: Education about SQL.
> I have an MS in biostats so I'm not totally
> computer illiterate, but I've never programmed
> anything GUI.
> Thanks for any answers
> Michael Young

SELECT @.@.VERSION
SELECT SERVERPROPERTY('Edition')

The second one is only in SQL2000 only. As for education, you might want to
start with some general books on databases and the SQL language, although
Microsoft's own training material is usually pretty good. You may find some
useful information here:

http://vyaskn.tripod.com/sqlbooks.htm

Simon|||<mcl2@.vms.cis.pitt.edu> wrote in message
news:c5eton$kp4$1@.usenet01.srv.cis.pitt.edu...
> I have two questions.
> First is there an easy way to tell what
> edition of MS SQL Server is on a machine?
> I found some code that is supposed to do
> this, but if I put that code into the query
> analyzer, it doesn't work. (Doesn't look
> like SQL query in any case.)
> A more general question: Education about SQL.
> I have an MS in biostats so I'm not totally
> computer illiterate, but I've never programmed
> anything GUI.
> Thanks for any answers
> Michael Young

SELECT @.@.VERSION
SELECT SERVERPROPERTY('Edition')

The second one is only in SQL2000 only. As for education, you might want to
start with some general books on databases and the SQL language, although
Microsoft's own training material is usually pretty good. You may find some
useful information here:

http://vyaskn.tripod.com/sqlbooks.htm

Simon

Wednesday, March 28, 2012

Iam trying to backup my database but nothing is backedup and no error message is displayed

Iam trying to backup my database but nothing is backedup and no error message is displayed. Here is my code.

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data.SqlClient" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Net.Mail" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Try

Dim con As SqlConnection

con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\safetydata.mdf;Integrated Security=True;User Instance=True")

Dim com As New SqlCommand("BACKUP DATABASE safetydata TO c:\backuptest")

com.Connection = con

com.Connection.Open()

com.ExecuteNonQuery()

com.Connection.Close()

Response.Redirect("\Developer\Backup_success.aspx")

Catch exAs Exception

Response.Redirect(

"~\Developer\backup_error.aspx")EndTryEndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>BackUP Page</title>

Try:

EXEC sp_addumpdevice 'disk', 'safetydataBAKUP',
'c:\backuptest\safetydata.bak'

-- Back up the full safetydata database.
BACKUP DATABASE safetydata
TO safetydataBAKUP

Check also if user under which SQL server (not web page, not you) is running has rights to access c:\backuptest directory?

|||

Ok iam gonna try that but it would also be helpful to me if you gave just a one line explanation of what the lines below do.

EXEC sp_addumpdevice 'disk', 'safetydataBAKUP',
'c:\backuptest\safetydata.bak'


Thanksjpazgier

|||

See help topic examples for BACKUP DATABASE in Enterprise manager T-SQL Help

Friday, March 23, 2012

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go


insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go


drop table submain
go
drop table main
go
drop table combined
go

Friday, March 9, 2012

I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it into
a variable after the 'use ' keyword. My test code thus far looks like this:
begin
declare @.db_name varchar(200)
declare @.codevarchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
end
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John Miceli
Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
inside the same EXEC.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it into
> a variable after the 'use ' keyword. My test code thus far looks like this:
> ----
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli

I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it into
a variable after the 'use ' keyword. My test code thus far looks like this:
----
begin
declare @.db_name varchar(200)
declare @.code varchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
en
----
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John MiceliUse is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
inside the same EXEC.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it into
> a variable after the 'use ' keyword. My test code thus far looks like this:
> ----
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli|||Thank you Tibor for your response!
If I have to put all of what follows into the same exec, I think I'm going
to have problems because there are multiple, very large queries that have to
run against the same database. Then when the next database cycles through,
the same statements have to be run against each one (10 total).
Do you have any other ideas of how to make that persistent? Thanks again!
"Tibor Karaszi" wrote:
> Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> inside the same EXEC.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> >I am trying to make a dynamic 'use' statement so that some code can loop
> > through the databases listed in sysdatabases, pull the name and place it into
> > a variable after the 'use ' keyword. My test code thus far looks like this:
> > ----
> > begin
> > declare @.db_name varchar(200)
> > declare @.code varchar(200)
> >
> > set @.db_name = 'nic_int_ldr'
> > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > print @.code
> > exec (@.code)
> >
> > --use nic_int_ldr
> > select max(id_bat) from ldr_batch
> > end
> > ----
> > At this point I'm just trying to get it to work with a hard coded database
> > name and I can't even get that to go.
> >
> > Is it not possible to make a 'use ' statement work this way?
> >
> > Thanks,
> > John Miceli
>
>|||> Do you have any other ideas of how to make that persistent?
Not that I can think of, I'm afraid... Sorry :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<John Miceli>; "MCDBA" <jdmiceli@.hotmail.com> wrote in message
news:BA5C17DB-F769-47DA-AF09-7AA414561BBE@.microsoft.com...
> Thank you Tibor for your response!
> If I have to put all of what follows into the same exec, I think I'm going
> to have problems because there are multiple, very large queries that have to
> run against the same database. Then when the next database cycles through,
> the same statements have to be run against each one (10 total).
> Do you have any other ideas of how to make that persistent? Thanks again!
>
> "Tibor Karaszi" wrote:
> > Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> > inside the same EXEC.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> > news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> > >I am trying to make a dynamic 'use' statement so that some code can loop
> > > through the databases listed in sysdatabases, pull the name and place it into
> > > a variable after the 'use ' keyword. My test code thus far looks like this:
> > > ----
> > > begin
> > > declare @.db_name varchar(200)
> > > declare @.code varchar(200)
> > >
> > > set @.db_name = 'nic_int_ldr'
> > > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > > print @.code
> > > exec (@.code)
> > >
> > > --use nic_int_ldr
> > > select max(id_bat) from ldr_batch
> > > end
> > > ----
> > > At this point I'm just trying to get it to work with a hard coded database
> > > name and I can't even get that to go.
> > >
> > > Is it not possible to make a 'use ' statement work this way?
> > >
> > > Thanks,
> > > John Miceli
> >
> >
> >

I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it int
o
a variable after the 'use ' keyword. My test code thus far looks like this:
----
begin
declare @.db_name varchar(200)
declare @.code varchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
end
----
--
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John MiceliUse is local to a batch, and EXEC is effectively a batch. Put all the thing
you want to execute
inside the same EXEC.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in me
ssage
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it i
nto
> a variable after the 'use ' keyword. My test code thus far looks like thi
s:
> ----
--
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
--
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli

Sunday, February 19, 2012

I need help please! My Exception Handling is not working.

I need help please...

I am using exception handling and Transaction handling combined.
The code is written to delete a record from 2 different table.
The purpose of using exception and transaction handling combined is tomake sure that the record is deleted in BOTH table. If there is errorwhile deleting a record from one of the tables, it will rollback thedatabase to the original and will stop deleting any record.

However, The Transaction handling is not working. When error occurswhile deleting a record on table A, the code still delete a record fromone table B. The transaction.RollBack() doesn't work. This is my code:

Try
Conn= NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Conn.Open()
Trans = sConn.BeginTransaction

For Each row In GridView1.Rows
Dim sql As String = "SELECT * FROM Member
Using Cmd As New SqlCommand(sql, Conn, Trans)
SqlDataSource1.DeleteParameters("MemberID").DefaultValue =DropDownListMember.SelectedValue
SqlDataSource1.Delete()
SqlDataSource2.DeleteParameters("CarID").DefaultValue =DropDownListCar.SelectedValue
SqlDataSource2.Delete()
End Using
End If
Next
sTrans.Commit()
Label1.Text = "deleted."
Catch ex As Exception
If sTrans IsNot Nothing Then
sTrans.Rollback()
End If
Label1.Text = "error"
Return
Finally
If sConn IsNot Nothing Then
sConn.Close()
End If
End Try

soo much thanks in advanced!!!got my question answered!

I found it myself by trying change the codes. thanks all! (?)

i need Code Generator...

do u still need code generators, we may help finding the best around.
"Tark Siala" wrote:

> thanks
> --
> Tarek M. Siala
> "Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
> news:9A22C240-0FA2-4A25-9D3B-2993FFEF982E@.microsoft.com...
>
>
=?Utf-8?B?TXVraHRhciBaYXllZA==?=
<MukhtarZayed@.discussions.microsoft.com> wrote in
news:EFC25B9A-9B60-44C5-9CA5-D1830D4C955A@.microsoft.com:

> do u still need code generators, we may help finding the best around.
LLBLGen Pro is great :-)