Hi all,
I just installed reporting services on a new server and need to migrate
existing reports from an old server to the new one. Being new to Reporting
Services, I am not sure of how to do this. Any help you gurus can provide
will be greatly appreciated.
Thanks,
--
LynnYou might want to use Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Any questions just drop me a mail using the link in the readme file
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:7BEBF34B-420F-42D0-BEB0-33BF5E01AD46@.microsoft.com...
> Hi all,
> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
> Thanks,
> --
> Lynn|||> I just installed reporting services on a new server and need to migrate
> existing reports from an old server to the new one. Being new to
> Reporting
> Services, I am not sure of how to do this. Any help you gurus can provide
> will be greatly appreciated.
Hi Lynn,
I think this tool [1] can help you.
Look in the generated script to find out how it is working.
[1] http://www.sqldbatips.com/showarticle.asp?ID=62
Best regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
How do I insert into existing Temp table?
Hi,
How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.
What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).
I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.
If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.
-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
My problem is that #GroupConfidence already exists so in fact I have this code below:
CREATE TABLE #GroupConfidence
( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)
-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.
Thanks in advance,
Ian.SELECT .... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.
If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:
CREATE TABLE #GroupConfidence ...
INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
Hope the above helps you.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.
So thanks for this.
Ian.
:D
How do I insert data into an existing temporary table? Note: Im primarily a .NET programmer who has to do T-SQL to grab data from time to time.
What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).
I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I dont want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.
If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.
-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
My problem is that #GroupConfidence already exists so in fact I have this code below:
CREATE TABLE #GroupConfidence
( [FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)
-- Insert the results into the #GroupConfidence table
SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO #GroupConfidence
FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.
Thanks in advance,
Ian.SELECT .... INTO <NEWTABLE> FROM <ANOTHER TABLE> will create the table and then insert the rows of the SELECT statement. You are getting the error because first you had created the table using "CREATE TABLE" statement then again are using SELECT ... INTO statment to create the table and insert the rows.
If you want to perform CREATE and INSERT operations in two statements then you can execute below statements:
CREATE TABLE #GroupConfidence ...
INSERT INTO #GroupConfidence SELECT RTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories' FROM RodResultPercentages RP
JOIN #UsersCompleted UC ON UC.SubmitID = RP.SubmitID
Hope the above helps you.|||EXCELLENT - I knew it had to be simple. But unless you know it just isn't going to happen.
So thanks for this.
Ian.
:D
How do I insert data from a flat file or .csv file into an existing SQL database?
How do I insert data from a flat file or .csv file into an existing SQL database?
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
May I suggest posting this question on sql server programming news group?
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming
Thanks
|||You might get extra help in sql server programming news group for these kind of questions.
thanks
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming
|||Thanks. I'll do that.How do I insert data from a flat file or .csv file into an existing SQL database?
How do I insert data from a flat file or .csv file into an existing SQL database?
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
Take a look at this post. I have given an example using OleDbCommand and SqlCommand.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1
Note that I am reading from excel and inserting into SQL database.
Hope this helps
|||Sample code below:
Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"
cn.Open()
sqlcn.Open()
cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")
sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text
sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)
For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next
cn.Close()
sqlcn.Close()
Hope this helps
|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.
Ed
"I don't know what I don't know."
How do I insert data from a flat file or .csv file into an existing SQL database?
How do I insert data from a flat file or .csv file into an existing SQL database?
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
Take a look at this post. I have given an example using OleDbCommand and SqlCommand.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1
Note that I am reading from excel and inserting into SQL database.
Hope this helps
|||Sample code below:
Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"
cn.Open()
sqlcn.Open()
cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")
sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text
sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)
For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next
cn.Close()
sqlcn.Close()
Hope this helps
|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.
Ed
"I don't know what I don't know."
How do I insert data from a flat file into an existing SQL databas
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql
How do I impose the IDENTITY property on an existing column?
How do I impose the IDENTITY property on an existing
column with data in it already? Scenario:
Column "joe" is not an IDENTITY column
Data imported into table and "joe" now has data.
***I want to now assign "joe" the IDENTITY property
without dropping and re-adding the column.
How do I do this?
Thanks,
MikeActually, this method will work as long as "joe" is an integer, as the
identity must be anyway to increment.
This table (YourTable) consisted of joe and jim both integers and initially
loaded, say with these values.
1,2
5,3
3,4
6,3
7,2
We run this script:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_YourTable
(
joe int NOT NULL IDENTITY (1, 1),
jim int NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable ON
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
SELECT joe, jim FROM dbo.YourTable TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.Tmp_YourTable', N'YourTable', 'OBJECT'
GO
COMMIT
Now YourTable has joe as the identity column and the next entry into
YourTable will enter 8 in joe
"Michael Berry" <anontaddler@.hotmail.com> wrote in message
news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
> How do I impose the IDENTITY property on an existing
> column with data in it already? Scenario:
> Column "joe" is not an IDENTITY column
> Data imported into table and "joe" now has data.
> ***I want to now assign "joe" the IDENTITY property
> without dropping and re-adding the column.
> How do I do this?
> Thanks,
> Mike|||I cannot have any temporary tables created - I must work
within the confines of one table and the specific column I
have been given - I would like to "toggle" the identity
property as you can in the Enterprise Manager GUI
interface...
Michael
>--Original Message--
>Actually, this method will work as long as "joe" is an
integer, as the
>identity must be anyway to increment.
>This table (YourTable) consisted of joe and jim both
integers and initially
>loaded, say with these values.
>1,2
>5,3
>3,4
>6,3
>7,2
>We run this script:
>BEGIN TRANSACTION
>SET QUOTED_IDENTIFIER ON
>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>SET ARITHABORT ON
>SET NUMERIC_ROUNDABORT OFF
>SET CONCAT_NULL_YIELDS_NULL ON
>SET ANSI_NULLS ON
>SET ANSI_PADDING ON
>SET ANSI_WARNINGS ON
>COMMIT
>BEGIN TRANSACTION
>CREATE TABLE dbo.Tmp_YourTable
> (
> joe int NOT NULL IDENTITY (1, 1),
> jim int NOT NULL
> ) ON [PRIMARY]
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable ON
>GO
>IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
> SELECT joe, jim FROM dbo.YourTable TABLOCKX')
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
>GO
>DROP TABLE dbo.YourTable
>GO
>EXECUTE sp_rename N'dbo.Tmp_YourTable',
N'YourTable', 'OBJECT'
>GO
>COMMIT
>Now YourTable has joe as the identity column and the next
entry into
>YourTable will enter 8 in joe
>
>"Michael Berry" <anontaddler@.hotmail.com> wrote in message
>news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
>> How do I impose the IDENTITY property on an existing
>> column with data in it already? Scenario:
>> Column "joe" is not an IDENTITY column
>> Data imported into table and "joe" now has data.
>> ***I want to now assign "joe" the IDENTITY property
>> without dropping and re-adding the column.
>> How do I do this?
>> Thanks,
>> Mike
>
>.
>
column with data in it already? Scenario:
Column "joe" is not an IDENTITY column
Data imported into table and "joe" now has data.
***I want to now assign "joe" the IDENTITY property
without dropping and re-adding the column.
How do I do this?
Thanks,
MikeActually, this method will work as long as "joe" is an integer, as the
identity must be anyway to increment.
This table (YourTable) consisted of joe and jim both integers and initially
loaded, say with these values.
1,2
5,3
3,4
6,3
7,2
We run this script:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_YourTable
(
joe int NOT NULL IDENTITY (1, 1),
jim int NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable ON
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
SELECT joe, jim FROM dbo.YourTable TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.Tmp_YourTable', N'YourTable', 'OBJECT'
GO
COMMIT
Now YourTable has joe as the identity column and the next entry into
YourTable will enter 8 in joe
"Michael Berry" <anontaddler@.hotmail.com> wrote in message
news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
> How do I impose the IDENTITY property on an existing
> column with data in it already? Scenario:
> Column "joe" is not an IDENTITY column
> Data imported into table and "joe" now has data.
> ***I want to now assign "joe" the IDENTITY property
> without dropping and re-adding the column.
> How do I do this?
> Thanks,
> Mike|||I cannot have any temporary tables created - I must work
within the confines of one table and the specific column I
have been given - I would like to "toggle" the identity
property as you can in the Enterprise Manager GUI
interface...
Michael
>--Original Message--
>Actually, this method will work as long as "joe" is an
integer, as the
>identity must be anyway to increment.
>This table (YourTable) consisted of joe and jim both
integers and initially
>loaded, say with these values.
>1,2
>5,3
>3,4
>6,3
>7,2
>We run this script:
>BEGIN TRANSACTION
>SET QUOTED_IDENTIFIER ON
>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>SET ARITHABORT ON
>SET NUMERIC_ROUNDABORT OFF
>SET CONCAT_NULL_YIELDS_NULL ON
>SET ANSI_NULLS ON
>SET ANSI_PADDING ON
>SET ANSI_WARNINGS ON
>COMMIT
>BEGIN TRANSACTION
>CREATE TABLE dbo.Tmp_YourTable
> (
> joe int NOT NULL IDENTITY (1, 1),
> jim int NOT NULL
> ) ON [PRIMARY]
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable ON
>GO
>IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.Tmp_YourTable (joe, jim)
> SELECT joe, jim FROM dbo.YourTable TABLOCKX')
>GO
>SET IDENTITY_INSERT dbo.Tmp_YourTable OFF
>GO
>DROP TABLE dbo.YourTable
>GO
>EXECUTE sp_rename N'dbo.Tmp_YourTable',
N'YourTable', 'OBJECT'
>GO
>COMMIT
>Now YourTable has joe as the identity column and the next
entry into
>YourTable will enter 8 in joe
>
>"Michael Berry" <anontaddler@.hotmail.com> wrote in message
>news:2c3201c38e20$b3ed8010$3501280a@.phx.gbl...
>> How do I impose the IDENTITY property on an existing
>> column with data in it already? Scenario:
>> Column "joe" is not an IDENTITY column
>> Data imported into table and "joe" now has data.
>> ***I want to now assign "joe" the IDENTITY property
>> without dropping and re-adding the column.
>> How do I do this?
>> Thanks,
>> Mike
>
>.
>
How do I identify dependencies before adding a column to a table?
I need to add a new column to a frequently used table in Production.
I want to make sure that the addition of the column does not break any
existing code in stored procs or triggers.
I can run sp_depends to identify potential problems but I believe this
system procedure is not entirely reliable.
Is the best approach a text search of sysobjects/syscomments to identify
dependencies? And other than "SELECT *," what are some things that might
break that I should be looking for?
Thanks
Dave> And other than "SELECT *," what are some things that might
> break that I should be looking for?
Off the top of my head, there is at least one other thing. The following
will now fail with incorrect number of columns specified:
INSERT table VALUES(blah, blah, blah)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In trigger code, the function columns_updated() is positional.
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||Sp_depends only contains info about objects that existed when you created
the sp... So it is possible that sp_depends does NOT show all sp
dependencies... I generally script out all sps and do a find...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||> In trigger code, the function columns_updated() is positional.
True. Though for the benefit of the original poster, code using
columns_updated() would only be affected if you used Enterprise Manager to
inject the column in the middle of the table. If you use ALTER TABLE ...
ADD COLUMN, the ordinal_position is next, so that existing code for
columns_updated should not have any effect...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks to everyone who responded.
I created a stored proc to search the code for potential problems.
This approach helped me find some things that sp_depends did not.
For anyone else who may face this problem, I include the proc below.
Thanks again.
Dave
USE admin
GO
--sample call:
--EXEC checkdepends 'mls', 'select *'
--dbo.checkdepends.PRC
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[checkdepends]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[checkdepends]
GO
CREATE PROC checkdepends
@.objname varchar(128) --object on which to identify dependencies (e.g.,
table name)
,@.str varchar(60) -- string used to search for dependencies (e.g., SELECT
* )
AS
DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
,@.c int -- record counter for coursor
,@.i int -- affected records counter
SET @.c = 0
SET @.i = 0
SET @.objname = '%' + @.objname + '%'
SET @.str = '%' + @.str + '%'
SET NOCOUNT ON
--build a table to hold the list of referencing objects (i.e., any db
object that references the target object #objname)
IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
DROP TABLE #refs
CREATE TABLE #refs
(
objname varchar(128)
,objtype varchar(24)
)
--grab all of the objects that reference the target object in their code
INSERT INTO #refs
SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id WHERE
text LIKE @.objname ORDER BY xtype
SELECT @.i= count(*)
FROM #refs
PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
PRINT ''
--create a table to hold the referencing object's code
IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
DROP TABLE #text
CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
--create a cursor to examine each referencing object in detail
DECLARE curs CURSOR
FOR
SELECT objname, objtype
FROM #refs
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs
INTO @.objname, @.objtype
WHILE @.@.fetch_status = 0
BEGIN
-- Add the proc code to the table (1 line of code = 1 record)
INSERT #text
EXEC sp_helptext @.objname
--check for the presence of search string within the referencing object's
code
SELECT @.i= count(*)
FROM #text
WHERE obj_text LIKE @.str
IF @.i > 0
BEGIN
PRINT
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++'
PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
PRINT ''
-- Select from the lines of code that have the string we are looking for
SELECT *
FROM #text
WHERE obj_text LIKE @.str
--empty the table to get ready for another proc
TRUNCATE TABLE #text
END
--increment the counter
SET @.c=@.c+1
FETCH NEXT FROM curs
INTO @.objname, @.objtype
END
CLOSE curs
DEALLOCATE curs|||Note that their still might be another problem, and that is if you have a
stored procedure > 8kb, a column or table name might actually start at
character 7995 and end at character 8012, which means that the result could
be split across separate columns in syscomments.
Might be safer to script the procedures, functions etc. and do a brute force
file-based search instead of using syscomments.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DaveF" <davef@.comcast.net> wrote in message
news:uG1MTR1wDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Thanks to everyone who responded.
> I created a stored proc to search the code for potential problems.
> This approach helped me find some things that sp_depends did not.
> For anyone else who may face this problem, I include the proc below.
> Thanks again.
> Dave
>
> USE admin
> GO
> --sample call:
> --EXEC checkdepends 'mls', 'select *'
> --dbo.checkdepends.PRC
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id => object_id(N'[dbo].[checkdepends]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[checkdepends]
> GO
> CREATE PROC checkdepends
> @.objname varchar(128) --object on which to identify dependencies (e.g.,
> table name)
> ,@.str varchar(60) -- string used to search for dependencies (e.g.,
SELECT
> * )
> AS
> DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
> ,@.c int -- record counter for coursor
> ,@.i int -- affected records counter
> SET @.c = 0
> SET @.i = 0
> SET @.objname = '%' + @.objname + '%'
> SET @.str = '%' + @.str + '%'
> SET NOCOUNT ON
> --build a table to hold the list of referencing objects (i.e., any db
> object that references the target object #objname)
> IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
> DROP TABLE #refs
> CREATE TABLE #refs
> (
> objname varchar(128)
> ,objtype varchar(24)
> )
>
> --grab all of the objects that reference the target object in their code
> INSERT INTO #refs
> SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id
WHERE
> text LIKE @.objname ORDER BY xtype
> SELECT @.i= count(*)
> FROM #refs
> PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
> PRINT ''
>
> --create a table to hold the referencing object's code
> IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
> DROP TABLE #text
> CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
>
> --create a cursor to examine each referencing object in detail
> DECLARE curs CURSOR
> FOR
> SELECT objname, objtype
> FROM #refs
> FOR READ ONLY
> OPEN curs
>
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> WHILE @.@.fetch_status = 0
> BEGIN
> -- Add the proc code to the table (1 line of code = 1 record)
> INSERT #text
> EXEC sp_helptext @.objname
> --check for the presence of search string within the referencing
object's
> code
> SELECT @.i= count(*)
> FROM #text
> WHERE obj_text LIKE @.str
> IF @.i > 0
> BEGIN
> PRINT
>
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++'
> PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
> PRINT ''
> -- Select from the lines of code that have the string we are looking
for
> SELECT *
> FROM #text
> WHERE obj_text LIKE @.str
> --empty the table to get ready for another proc
> TRUNCATE TABLE #text
> END
> --increment the counter
> SET @.c=@.c+1
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> END
>
> CLOSE curs
> DEALLOCATE curs
>
>
>|||> be split across separate columns in syscomments.
Err, separate rows/tuples, sorry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||hello Dave,
I think the answer to your problem is simple. Build it. If
you do a complete build of all your source any errors that
your change could cause will be raised. Sounds easy when I
say it like this, however if you check out www.dbghost.com
it show how this can be made very simple.
All other code (exe,dll,asp) is commonly built to find
errors due to dependancy. Database code isn't (generally).
We would like to change this and take the guess work out
of making changes to your database code.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>I need to add a new column to a frequently used table in
Production.
>I want to make sure that the addition of the column does
not break any
>existing code in stored procs or triggers.
>I can run sp_depends to identify potential problems but I
believe this
>system procedure is not entirely reliable.
>Is the best approach a text search of
sysobjects/syscomments to identify
>dependencies? And other than "SELECT *," what are some
things that might
>break that I should be looking for?
>Thanks
>Dave
>
>.
>
I want to make sure that the addition of the column does not break any
existing code in stored procs or triggers.
I can run sp_depends to identify potential problems but I believe this
system procedure is not entirely reliable.
Is the best approach a text search of sysobjects/syscomments to identify
dependencies? And other than "SELECT *," what are some things that might
break that I should be looking for?
Thanks
Dave> And other than "SELECT *," what are some things that might
> break that I should be looking for?
Off the top of my head, there is at least one other thing. The following
will now fail with incorrect number of columns specified:
INSERT table VALUES(blah, blah, blah)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In trigger code, the function columns_updated() is positional.
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||Sp_depends only contains info about objects that existed when you created
the sp... So it is possible that sp_depends does NOT show all sp
dependencies... I generally script out all sps and do a find...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||> In trigger code, the function columns_updated() is positional.
True. Though for the benefit of the original poster, code using
columns_updated() would only be affected if you used Enterprise Manager to
inject the column in the middle of the table. If you use ALTER TABLE ...
ADD COLUMN, the ordinal_position is next, so that existing code for
columns_updated should not have any effect...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks to everyone who responded.
I created a stored proc to search the code for potential problems.
This approach helped me find some things that sp_depends did not.
For anyone else who may face this problem, I include the proc below.
Thanks again.
Dave
USE admin
GO
--sample call:
--EXEC checkdepends 'mls', 'select *'
--dbo.checkdepends.PRC
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[checkdepends]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[checkdepends]
GO
CREATE PROC checkdepends
@.objname varchar(128) --object on which to identify dependencies (e.g.,
table name)
,@.str varchar(60) -- string used to search for dependencies (e.g., SELECT
* )
AS
DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
,@.c int -- record counter for coursor
,@.i int -- affected records counter
SET @.c = 0
SET @.i = 0
SET @.objname = '%' + @.objname + '%'
SET @.str = '%' + @.str + '%'
SET NOCOUNT ON
--build a table to hold the list of referencing objects (i.e., any db
object that references the target object #objname)
IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
DROP TABLE #refs
CREATE TABLE #refs
(
objname varchar(128)
,objtype varchar(24)
)
--grab all of the objects that reference the target object in their code
INSERT INTO #refs
SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id WHERE
text LIKE @.objname ORDER BY xtype
SELECT @.i= count(*)
FROM #refs
PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
PRINT ''
--create a table to hold the referencing object's code
IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
DROP TABLE #text
CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
--create a cursor to examine each referencing object in detail
DECLARE curs CURSOR
FOR
SELECT objname, objtype
FROM #refs
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs
INTO @.objname, @.objtype
WHILE @.@.fetch_status = 0
BEGIN
-- Add the proc code to the table (1 line of code = 1 record)
INSERT #text
EXEC sp_helptext @.objname
--check for the presence of search string within the referencing object's
code
SELECT @.i= count(*)
FROM #text
WHERE obj_text LIKE @.str
IF @.i > 0
BEGIN
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++'
PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
PRINT ''
-- Select from the lines of code that have the string we are looking for
SELECT *
FROM #text
WHERE obj_text LIKE @.str
--empty the table to get ready for another proc
TRUNCATE TABLE #text
END
--increment the counter
SET @.c=@.c+1
FETCH NEXT FROM curs
INTO @.objname, @.objtype
END
CLOSE curs
DEALLOCATE curs|||Note that their still might be another problem, and that is if you have a
stored procedure > 8kb, a column or table name might actually start at
character 7995 and end at character 8012, which means that the result could
be split across separate columns in syscomments.
Might be safer to script the procedures, functions etc. and do a brute force
file-based search instead of using syscomments.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DaveF" <davef@.comcast.net> wrote in message
news:uG1MTR1wDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Thanks to everyone who responded.
> I created a stored proc to search the code for potential problems.
> This approach helped me find some things that sp_depends did not.
> For anyone else who may face this problem, I include the proc below.
> Thanks again.
> Dave
>
> USE admin
> GO
> --sample call:
> --EXEC checkdepends 'mls', 'select *'
> --dbo.checkdepends.PRC
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id => object_id(N'[dbo].[checkdepends]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[checkdepends]
> GO
> CREATE PROC checkdepends
> @.objname varchar(128) --object on which to identify dependencies (e.g.,
> table name)
> ,@.str varchar(60) -- string used to search for dependencies (e.g.,
SELECT
> * )
> AS
> DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
> ,@.c int -- record counter for coursor
> ,@.i int -- affected records counter
> SET @.c = 0
> SET @.i = 0
> SET @.objname = '%' + @.objname + '%'
> SET @.str = '%' + @.str + '%'
> SET NOCOUNT ON
> --build a table to hold the list of referencing objects (i.e., any db
> object that references the target object #objname)
> IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
> DROP TABLE #refs
> CREATE TABLE #refs
> (
> objname varchar(128)
> ,objtype varchar(24)
> )
>
> --grab all of the objects that reference the target object in their code
> INSERT INTO #refs
> SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id
WHERE
> text LIKE @.objname ORDER BY xtype
> SELECT @.i= count(*)
> FROM #refs
> PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
> PRINT ''
>
> --create a table to hold the referencing object's code
> IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
> DROP TABLE #text
> CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
>
> --create a cursor to examine each referencing object in detail
> DECLARE curs CURSOR
> FOR
> SELECT objname, objtype
> FROM #refs
> FOR READ ONLY
> OPEN curs
>
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> WHILE @.@.fetch_status = 0
> BEGIN
> -- Add the proc code to the table (1 line of code = 1 record)
> INSERT #text
> EXEC sp_helptext @.objname
> --check for the presence of search string within the referencing
object's
> code
> SELECT @.i= count(*)
> FROM #text
> WHERE obj_text LIKE @.str
> IF @.i > 0
> BEGIN
>
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++'
> PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
> PRINT ''
> -- Select from the lines of code that have the string we are looking
for
> SELECT *
> FROM #text
> WHERE obj_text LIKE @.str
> --empty the table to get ready for another proc
> TRUNCATE TABLE #text
> END
> --increment the counter
> SET @.c=@.c+1
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> END
>
> CLOSE curs
> DEALLOCATE curs
>
>
>|||> be split across separate columns in syscomments.
Err, separate rows/tuples, sorry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||hello Dave,
I think the answer to your problem is simple. Build it. If
you do a complete build of all your source any errors that
your change could cause will be raised. Sounds easy when I
say it like this, however if you check out www.dbghost.com
it show how this can be made very simple.
All other code (exe,dll,asp) is commonly built to find
errors due to dependancy. Database code isn't (generally).
We would like to change this and take the guess work out
of making changes to your database code.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>I need to add a new column to a frequently used table in
Production.
>I want to make sure that the addition of the column does
not break any
>existing code in stored procs or triggers.
>I can run sp_depends to identify potential problems but I
believe this
>system procedure is not entirely reliable.
>Is the best approach a text search of
sysobjects/syscomments to identify
>dependencies? And other than "SELECT *," what are some
things that might
>break that I should be looking for?
>Thanks
>Dave
>
>.
>
Sunday, February 19, 2012
How Do I distribute an existing DataBase - Please Help
I have a Systema that uses a DataBase an I Want to Install y other machines, I need to Install a Visual Studio Program that uses a Database, How do I Install The Visual Studio Program, the Sql Server Express, and the existing DataBase.
Thanks in Advance, Santiago
Visual Studio can be installed using the appropiate installer from the VS disks, SQL Server comes with the Visual Studio, so you either use the shipped version with Visual Studio and update it laterone with the SP2 Service Pack or leave the SQL Server Express edition out and install it seperately by using the most recent download from Microsoft. The database can be distributed by using SQL Backup and Restore (Backup the databases on the source server and restore them on the other clients)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Perhaps these resources will help:
SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648
How Do I distribute an existing DataBase - Please Help
I have a Systema that uses a DataBase an I Want to Install y other machines, I need to Install a Visual Studio Program that uses a Database, How do I Install The Visual Studio Program, the Sql Server Express, and the existing DataBase.
Thanks in Advance, Santiago
Visual Studio can be installed using the appropiate installer from the VS disks, SQL Server comes with the Visual Studio, so you either use the shipped version with Visual Studio and update it laterone with the SP2 Service Pack or leave the SQL Server Express edition out and install it seperately by using the most recent download from Microsoft. The database can be distributed by using SQL Backup and Restore (Backup the databases on the source server and restore them on the other clients)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Perhaps these resources will help:
SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648
Subscribe to:
Posts (Atom)