Thursday, March 29, 2012
Database Configuration Problems
Sunday, March 11, 2012
database and vb6
How can I attach mdf and ldf files using Visual Basic 6.0?
Help me
See SQL Server Books Online topic
Server.AttachDatabase Method (String, StringCollection)
http://msdn2.microsoft.com/en-us/library/ms210158.aspx
There are many code examples in many languages.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
Dim owner As String
Dim logstr as String
Dim datastr as String
owner = srv.Databases("AdventureWorks").Owner
'Detach the AdventureWorks database.
srv.DetachDatabase("AdventureWorks", False, False)
'Display information about the detached database.
Dim d As DataTable
Datastr = "C:\Program Files\Microsoft SQL Server"
Datastr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
Logstr = "C:\Program Files\Microsoft SQL Server"
Logstr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf"
d = srv.DetachedDatabaseInfo(datastr)
Dim r As DataRow
Dim c As DataColumn
For Each r In d.Rows
Console.WriteLine("==========================")
For Each c In r.Table.Columns
Console.WriteLine(c.ColumnName + " = " + r(c).ToString)
Next
Next
'Check whether the file is a detached primary file.
Console.WriteLine(srv.IsDetachedPrimaryFile(datastr))
'Attach the database
Dim sc As StringCollection
sc = New StringCollection
sc.Add(datastr)
sc.Add(logstr)
srv.AttachDatabase("AdventureWorks", sc, owner, AttachOptions.None)
|||i m making project in vb 6.
i need to make the database connectivity.
however the object used for it (database object) is not availabe in my version.
there is a data object instead having properties like database and connection.
can u please tell me another way to connect to the database.
also can u please tell me how to use the connection and command object.
thanks
database and vb6
How can I attach mdf and ldf files using Visual Basic 6.0?
Help me
See SQL Server Books Online topic
Server.AttachDatabase Method (String, StringCollection)
http://msdn2.microsoft.com/en-us/library/ms210158.aspx
There are many code examples in many languages.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
Dim owner As String
Dim logstr as String
Dim datastr as String
owner = srv.Databases("AdventureWorks").Owner
'Detach the AdventureWorks database.
srv.DetachDatabase("AdventureWorks", False, False)
'Display information about the detached database.
Dim d As DataTable
Datastr = "C:\Program Files\Microsoft SQL Server"
Datastr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
Logstr = "C:\Program Files\Microsoft SQL Server"
Logstr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf"
d = srv.DetachedDatabaseInfo(datastr)
Dim r As DataRow
Dim c As DataColumn
For Each r In d.Rows
Console.WriteLine("==========================")
For Each c In r.Table.Columns
Console.WriteLine(c.ColumnName + " = " + r(c).ToString)
Next
Next
'Check whether the file is a detached primary file.
Console.WriteLine(srv.IsDetachedPrimaryFile(datastr))
'Attach the database
Dim sc As StringCollection
sc = New StringCollection
sc.Add(datastr)
sc.Add(logstr)
srv.AttachDatabase("AdventureWorks", sc, owner, AttachOptions.None)
|||i m making project in vb 6.
i need to make the database connectivity.
however the object used for it (database object) is not availabe in my version.
there is a data object instead having properties like database and connection.
can u please tell me another way to connect to the database.
also can u please tell me how to use the connection and command object.
thanks
Database alterations while Publishing enabled
make a database change like adding a column?
- make the change to both databases
- just one and replicate the change
- disable publishing, drop subscriptions before, etc.
Thank you so much ( as I read the current posted subjects),
Bob
Robert,
sp_repladdcolumn and sp_repldropcolumn are the standard way of making schema
changes. Using these procedures, the dependant replication framework (sps,
triggers etc) will be updated as well. Changing existing columns can only be
done in a roundabout way (before SQL 2005). You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the original column (sp_repldropcolumn). Do this again
to create the column having the same original name.
HTH,
Paul Ibison
|||Paul,
After the new columns have been added does the Snapshot have to be run and the subscription reinitialized for the changes to be replicated to the Subscriber? What happens if the subscription is not reinitialised?
Thanks
Chris R
|||Chris,
you only need to synchronize - DON'T ReInitialize whatever you do! -
otherwise a new snapshot will need to be created and then all the articles
will have to go to the subscribers.
HTH,
Paul Ibison
Saturday, February 25, 2012
Data, data, who's got the data? (basic theory question)
I'm trying to understand aggregations, storage theory (MOLAP,ROLAP,HOLAP), and proactive caching and loading data into my cube.
We are currently building both an OLTP db and OLAP db 100% in SQL Server.
The OLTP users will add several hundred thousand rows a day.
I'm going to create an SSIS package to initially take data from the OLTP db and load it into the OLAP db and then build my cube.
Now my understanding becomes fuzzy. I've built partitions and created aggregations and enabled proactive caching, but I'm not sure why I am doing so. When talking about real time MOLAP and proactive caching and MOLAP to ROLAP to MOLAP, the various literature refers to "changes in the relational data" triggering various actions based on my settings. Are the authors referring to changes in the original OLTP db or changes in the OLAP db. If the latter, then changes will only result when I run another SSIS package to update my data.
Or does this discussion assume I am using a UDM db which seems to say I can build my star schema on top of the OLTP db. But then who has that data?
Where the heck is my data?
You want to update your cubes after data has been added to your relational OLAP database. Your relational OLAP database is the source of the data for your cubes so there really isn't any reason to process your cubes until they have been updated.
Though data will be added to your OLTP system throughout the day, how frequently will you be adding data to your OLAP database? If it's on a fixed schedule, you may want to do without the proactive caching and just start the processing yourself as part of your SSIS package.
Regarding the UDM, I would not recommend building your DSV using references to tables in the OLTP system. Though this can be done, it just seems like this is bound to cause you problems.
Good luck,
Bryan
|||My problem is my users want "near real time" data so I'm looking for solutions. Proactive caching seems like a piced of the puzzle but I wasn't sure what the data source was for the proactive caching.
If all the hubub is about detecting data that changes in the OLAP, I don't get it. Data will only change in the OLAP when I tell it (run an SSIS program) to change so I would think I'd know about it and go ahead and process the cube.
|||What's nice about the Proactive Caching is that you could have SSAS handle the processing on your behalf. You can configure it to detect a change but then wait for some period of time for things to settle down before processing. During that time, you might have 2 or 3 ETL cycles, some with changes and some without. Really just depends on your needs. If you can process quickly so that it doesn't interfere with your ETL cycles, then your suggested approach is probably the best for you.
Bryan
|||Does it "detect a change" in the OLAP db or the original OLTP db? I suspect OLAP, which is only going to change when I run an SSIS update so I don't see much value to detecting a change sincs I am causing the change and am fully aware of when it happens. As part of my SSIS package I suppose I could trigger the reprossing of my cube.|||Just so anyone else reading this is clear on what we are talking about, let me define a few things. OLTP and OLAP refer to how data is used. OLTP systems typically support entity-relationship models while OLAP systems typiclly support dimensional models (though others are available).
Both entity-relationship and dimensional models are be supported through relational database technologies. The SQL Server Database Engine is an example of a relational database technology. Dimensional models are also supported by multidimensional database technologies, such as SQL Server Analysis Services, which store data in a different kind of structure and allow access through languages like MDX.
So, when you say "OLAP db", I interpret that to mean the relational database containing your dimensional model supporting OLAP activities, and when you say "OLTP db", I interpret that to mean the relational database containing your entity-relationship model supporting OLTP activities.
Whew!
With that out of the way, the answer to your question is normally you are watching your OLAP db for changes. Watching isn't the most accurate way to describe this. You can certainly configure SSAS to evaluate the underlying tables for changes. However, you can also configure some relational database technologies to notify SSAS when a change occurs. And there are a ton of other options that make ProActive caching really useful in some scenarios.
Still, my general philosophy is to keep your system as simplistic as is possible while achieving your critical goals (one of which is supportablity!). If you can avoid using proactive caching by taking a more straightforward approach while still meeting your goals, I would recommend doing so.
Good luck,
Bryan
|||I'm sorry, I did leave out quite a bit, but yes, that is what I am talking about. In my case the only reason to have a "middle" OLAP db is to create some calculated fields and pull together some columns from different tables. I could probably do most everything during cube processing and build it right on my OLTP which is a single SQL Server db. But the main reason I'm using the OLAP "staging" db is so I am not placing an additional load on my OLTP db which is expected to receive up to a million new rows of data a day. I foresee huge latency and functionality issues and am trying to get ahead of the curve.
But, from your answer, it looks like I can use either db to trigger a cache update (if that's the proper SSAS term).
Thanks.
|||Just wanted to mention one thing.... You refer to your OLAP db as a "staging" database. We often implement a full-blown dimensional model in a relational database. Lots of reasons for this. (The Kimball books go into this in detail.)
This database + the OLAP cube serve as the data warehouse layer of our BI infrastructure. You could build a cube off a DSV that assembles data right out of your OLTP database, but we don't often recommend this.
Good Luck,
Bryan
Yes. The main reason I'm building an OLAP db is to offload data retrieval requests from the main db. So I tend to think of it as a "staging" db, which really refers to a real db one might require were one gathering data from diverse data sources.
We are currently building both the OLTP db and BI solution. In theory, I could build my cube right on top of the live db (OLTP) or a snapshot of it or some other replicated image. My "truth" lies in my OLTP db, changes in which I may be able to use to update caching. I was reading the Sam's book and it referred to changes in the relational database triggering updates to your cache and I just wondered WHICH relational db.
Given the constant updating that is going to be done on the OLTP source, I probably am better off to update caching and/or cube processing on a timed schedule. I realize there are a couple of configurable "windows" but my data updates probably will be constant throughout the day. Of course clients want terabyte size data cubes to generate instantaneous reports on data with nano-second latency.
I'm trying to get my arms around the basic theory of this new (to me) data model (multi-dimensional). I can recite Codd's 12 rules in my sleep (or is it 13...I think I need a nap), which is perhaps part of my problem: I am trying to force everything through a relational prism.
But you've been quite helpful.
Thanks.
John
Sunday, February 19, 2012
Data type question
Can some one give me a quick lesson in data types - What is the basic
difference between a char data type and varchar data type and when does one
use one over the other and advantages and disadvantages of one over the
other.
Here is the reason I asked.
Initially I had a table to which I was importing data from a text file. The
majority of the columns in this table was of datatype varchar ranging from
size of 3 to size of 100. THe columns also consisted of decimal data types
and datetime datatypes. It was suggested that changing the data type to char
from varchar would speed up the insertion process. The file the data is
being inserted from is a fixed length ASCII text file which
Would using a char datatype over a varchar make a difference in select
statement (Select *)
Please advise
Thanks in advance"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
I think that using varchar in your situation is better solution. Varchars
are generally space savers and when distribution of your data ranges from 3
to 100 characters it is better that you use varchar instead of char. If you
use char you'll need to set it to the size of the largest string (100) and
each and every time you enter the value into this field it will take up all
100 chars even if actual data takes only 3 (the rest will be filled with
blanks). Internaly SQL Server stores varchar and char data types a little
bit different, but that shouldn't affect the performance of select
statements. The problem might appear when you update or insert data because
of possible page splitting if new value doesn't fit on the page so SQL
Server has to split page in two to get the space for this new data. This
process is expensive and you want to avoid it. I guess you'll just need to
know your data if you want to select best data type for it. I suggest you
that you use char if the size of your data dont range much. If it does use
varchar and you'll save some space on your hard drive.
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr|||I don't see where using a char in this case would give you any performance
gain and might even decrease it if the majority of the actual data are
relatively small in the number of bytes. While there is a little overhead
in maintaining a varchar over a char it is minimal and usually not worth the
extra space the char will take up when storing and retrieving. If you want
to get the fastest data load then make sure you have the proper conditions
to get a minimally logged load. Lookup BCP in BooksOnLine and there is a
section that explains how to do this.
--
Andrew J. Kelly
SQL Server MVP
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>|||With the most important already stated, a further point:
in case the column is part of an index, the impact could be significant.
With the fixed length, the index size would be much larger, taking more
space and making traverse of the index longer. Then, checking the
individual index value mostly also takes much longer than needed.
Quentin
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>