Friday, March 30, 2012
How to control DB size growth?
My database is 1gig big and it has so far one table with 1 melion records.
Once I tried to create the primary key in this table but it was rejected by
MS SQL since it was not unique. That's fine with me but I noticed that my
database doubled in size to just over 2 gigs. Then I created clastered index
and the size of the file was around 3 gigs. When I deleted newly created
index the size of the file stayed the same almost reaching the limites that
I set up before. I would never expect that the my database would grow so
fast since the same data in the Access database is just 450 MB.
Why the data grows when the key or index fails to be created?
How to control the size of the Database?
Any help is greatly appreciated,
Les
> Why the data grows when the key or index fails to be created?
It is growing because sql is creating index to the point of failure. Of
course creation takes some disk space. But after failure it cleans the data
in failure but by default it does not shrink the database file. So the file
stays as big as it was on failure.
> How to control the size of the Database?
the best way to control it is to set a maximm size, but the you should set
an alert to monitor the size and notify you database size is near full.
Best way to see how much space your data is taking is to set a TaskPad view
in Enterprise Manager.
(Select Database, go to menu View -> TaskPad).
Danijel
"Tom" <tom@.killspam.com> wrote in message
news:UjSFd.48178$TN6.1797082@.news20.bellglobal.com ...
> Hello,
> My database is 1gig big and it has so far one table with 1 melion records.
> Once I tried to create the primary key in this table but it was rejected
> by MS SQL since it was not unique. That's fine with me but I noticed that
> my database doubled in size to just over 2 gigs. Then I created clastered
> index and the size of the file was around 3 gigs. When I deleted newly
> created index the size of the file stayed the same almost reaching the
> limites that I set up before. I would never expect that the my database
> would grow so fast since the same data in the Access database is just 450
> MB.
> Why the data grows when the key or index fails to be created?
> How to control the size of the Database?
> Any help is greatly appreciated,
> Les
>
how to construct dynamic sql in stored procedure
Hi Guys.
I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this
CREATE PROCEDURE P_SAMPLE
(
@.P_ONE NVARCHAR(240)
)
AS
BEGIN
DECLARE
@.TMP_CNT INT
BEGIN
EXEC ('SELECT @.TMP_CNT = 1')
-- PRINT @.TMP_CNT
END
END
Regards
Mani
Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.
Code Snippet
DECLARE @.TMP_CNT INT
DECLARE @.SQL as NVarchar(4000)
DECLARE @.Params as NVarchar(4000)
Set @.SQL = N'SELECT @.TMP_CNT = 1'
Set @.Params = N'@.TMP_CNT as Int OUTPUT'
Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT
Select @.TMP_CNT
|||
here is one of the best resource in Dynamic sql
http://www.sommarskog.se/dynamic_sql.html
Madhu
Wednesday, March 28, 2012
How to connection MS Access Porject (.adp) with SQL server?
I got the question, how can i connection MS Access Project to a SQL Server
in vba in runtime.
The situation is i create a new MS Access Project, it's didn't have any
connection with any SQL-server. It's just consist a couple of form. What i
want is that user can via an inlog form to get in the database. The
informations about the server...etc is save in the code. The user just need
to give the SQL-login and password. My code to make the connection is as
follow.
'----
--
Function GetADPConnection(strServername, strDBName As String, Optional strUN
As String, _ Optional strPW As String)
Dim strConnect as string
strConnect = "Provider=SQLOLEDB" & _
";Data Source = \10.0.0.4\" & strServername & _
";Initial Catalog =" & strDBName
strConnect = strConnect & ";UID=" & strUN
strConnect = strConnect & ";PWD=" & strPW
Application.CurrentProject.OpenConnection strConnect
End Function
'----
--
It doesn't seems to work and i get the following error messagge:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
Can anyone help?
Best Regards,
PatData source should be in the form of:
Servername (or IP - 10.0.0.20)
For a named instance, you can use:
Servername\InstanceName
You can find sample connection strings at:
http://www.carlprothman.net/Default.aspx?tabid=81
-Sue
On Thu, 6 Apr 2006 07:49:02 -0700, Pat
<Pat@.discussions.microsoft.com> wrote:
>Hi all,
>I got the question, how can i connection MS Access Project to a SQL Server
>in vba in runtime.
>The situation is i create a new MS Access Project, it's didn't have any
>connection with any SQL-server. It's just consist a couple of form. What i
>want is that user can via an inlog form to get in the database. The
>informations about the server...etc is save in the code. The user just need
>to give the SQL-login and password. My code to make the connection is as
>follow.
>'----
--
>Function GetADPConnection(strServername, strDBName As String, Optional strU
N
>As String, _ Optional strPW As String)
>Dim strConnect as string
> strConnect = "Provider=SQLOLEDB" & _
> ";Data Source = \10.0.0.4\" & strServername & _
> ";Initial Catalog =" & strDBName
> strConnect = strConnect & ";UID=" & strUN
> strConnect = strConnect & ";PWD=" & strPW
> Application.CurrentProject.OpenConnection strConnect
>End Function
>'----
--
>It doesn't seems to work and i get the following error messagge:
>[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
>denied.
>Can anyone help?
>Best Regards,
>Pat
>sql
Monday, March 26, 2012
How to connect to SQL Database, Create tables
I am new at asp.net. I want to design a websiteusing asp.net as frontend and sql database as backend. I am able toconnect and add,update as well delete records when I use MsAcess andAsp.net using the following connection strings...
**********
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
**********
But when I try to connect to sql database using the following connection strings I am unable to do so...
**************
SqlConnection myConnection = new SqlConnection("server=PLATINUM\VSdotNET;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();
**************
I have written the servername as "PLATINUM\VSdotNET" because when Iinstalled SQL SERVER 2000 I found a tray icon where the server name wasdisplaying the same (my computer name is PLATINUM).
When I used webmatrix I enterd the same server name and windowsauthentication I was able to create a database but How to createtable...
Please help me out
Thanks in advance...Please explain "I am unable to do so". Are you receiving an error message?|||Actually I don't know whether the connection string I am using is correct or not...
Because I am unable to create tables using the connection strigns what I told before...
when I try running this page I get the error message like
Compiler Error Message:CS1009: Unrecognized escape sequence
and the compiler shows a error in the connection string , So as far I guess there is problem with the connection string only.
If I get a sample page for what I am trying to do i.e connecting to SQLdatabase, Creating tables as well as accessing table my problem will besolved.
Thanks for your time.
|||Well, I always use this resource for building connection strings:http://www.connectionstrings.com/ Check out the SqlConnection (.NET) options under SQL Server. Yourproblem *might* be that you should use True, not Yes, forTrusted_Connection. In any case, I would try to stick as closelyas possible to the advice listed there.|||* from Authors is not a valid SQL Statement. Try SELECT * FROM Authors
Friday, March 23, 2012
how to connect from visual studio to a ms sql 2005 database on a server
Hello,
I have visual studio 2005 installed on my PC. I want to create a new website and I want the site to be connected to a MS SQL 2005 database that resides on my hosting account. I am thinking this can be done but I have watched videos, spent time looking for a site to help and I have hit a brick wall.
I have no idea how to do this.
Can anyone please help me with some instructions or a webpage that shows you how to do this.
Thank You
Joe
Hello,
I have found the solution and would like to share it with anyone that needs this info.
I have found you can connect to a ms sql 2005 database that resides on your hosting account either by
using the Visual Web Developer Express Edition which is free or Microsoft Visual Studio.
The first thing you need is asp 2.0 hosting account and a ms sql 2005 database activated on your hosting account.
The below will work for both versions of software mentioned above.
1. OPen your desired software program and Create a New Website
2. In the database explorer right mouse click on Data Connections and add a new connection.
Datasource = Microsoft SQL Server (SQL Client)
Server Name = This should of been supplied by your hosting provider eg sql26565.aspprovider.net
Select - Use SQL Authorisation
User Name = your username for your database (this should be provider by your provider)
Password = your password for the database (this should be provider by your provider)
Select the database from within the connect to database tab
Click ok and the database should be connected and appears under the Data Connections Folder.
If you need to know how to add grids etc there are lots of very handy videos which with what you learn will work on both versions of teh software programs mentioned here.
I Hope this helps anyone that had faced the same issue that I have. So many learning videos out there but an important step that has been missed due to the focus on the express editions.
Thanks
Joe
|||If you need to know how to add grids etc pls visit which applies to both versions of thesoftware mentioned. --> http://msdn.microsoft.com/vstudio/express/vwd/learning/default.aspx
Monday, March 19, 2012
How to configure Report Server in SQL Server 2005?
I hope my question is clear.
I have SQl Server 2005 installed in local computer. I am planning to create report by Report Service so a report server is needed by which I can deploy report from my local computer to that server. How to intall the report server in another computer/
Thanks
ZYTZYT,
Your question is clear, but it doesn't look like you've done any research on the subject. There are plenty of KB articles and technical support documents regarding Reporting Services on the Microsoft website and numerous books exist on the subject.
If you encounter a specific problem when trying to implement Reporting Services having done the necessary research, people are more likely to help you, but they won't generally do all your work for you!
Lempster :S
Monday, March 12, 2012
How to configure connection string for remote server?
Sorry for the newbie question, but...
I have developed a website on my local development machine. When I create the connections string VS automatically creates a path to my local hard drive inside the string.
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Mike\My Documents\Visual Studio 2005\WebSites\PostAlertz\App_Data\PADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True
However, when I deploy this website to a remote server, SQLExpress tries to attach to the file using the wrong path. How do I fix this?
Your patience is appreciated...
Hi
Here is a sample connection string,and you can find more onhttp://www.connectionstrings.com/:
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
HOW-TO: Database from VWD to Shared Host tells you how to copy database to share host.
Hope this helps.
|||If you have SQL Express instance installed on the remote server, just set the correct path for the mdf file like:data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|PADatabase.mdf;User Instance=true
How to conditionally create Flat File Destination for Lookup Redirects
Hi all,
I am redirecting the critical lookup errors to the flat files. These flat files got created regardless if there are lookup errors or not.
Is there a better way to conditionally create them only when there are lookup errors?
Thanks.
Its not really possible, no.
What you could do is count the number of records (using a ROWCOUNT component). Then, probably in the OnPostExecute eventhandler for the data-flow, delete the file if the rowcount is zero.
Or you could push everything into a recordset destination and then, in the eventhandler, push the stuff into a flat file IF the rowcount is greater than zero.
-Jamie
Friday, March 9, 2012
How to concatenate two text columns
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatenate two text fields?
Thanks
Raju
maybe you can export the 2 columns into excel
eg. first column is in cell A1, second column is in cell B1
at C1 you type this formula =A1&B1, then copy this formula till the end of
the row, then you import it back to your table.
Susanna
"Raju" wrote:
> Hi all,
>
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 + text
> column 2.
>
> Is there an easy way to concatenate two text fields?
>
> Thanks
> Raju
>
>
|||If you created a third column in your table you use an UPDATE statement
to do this like:
UPDATE Sometable
SET col3 = ISNULL(col1,'') + ISNULL(col2,'')
or in a view you could use the almost same syntax like:
SELECT ISNULL(col1,'') + ISNULL(col2,'') as col3
THE ISNULL(col1,'') syntax is related to the issue that in some cases
the attributes could be NULL rather than just an empty or regular
string and this would lead to a NULL result concatenating the two
values together.
HTH, Jens Suessmeyer.
How to complete Trigger on Table
Having fired, the same record with a few altered fields wil be inserted to a table on a linked server.
I need help to understand the requirements to make this work.
What I have found out and configured so-far:
I have linked the remote server to the server with the trigger.
I have successfully added the trigger to the local Server
What I need to know:
How do I know the trigger is running ?
Do I have to start it manually, or will it always be active ?
How and where is it saved ?
Once saved, can it be altered ?
I apologize for the basic nature of my questions on triggers.
GrahamTo test your trigger, just insert three rows into your base table and when your statment has finished running look on your linked server to see that the rows have been added.
Once the trigger has been "compiled" it is in effect and continuously running. The only exception to this is if you BCP data into the table, by default BCP does NOT fire triggers.
A record of the trigger is placed in the sysobjects table and the text of the trigger is placed in the syscomments table.
Yes, trigger can be modified or dropped.
Books Online has an excelent write up on triggers.
How to compare value pair of one table with value pair of another table
--------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)
insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)
insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)
What is the SQL query to find out the pairs existing in B, but not in A ?
I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.
Thanks in advance.
Regards,
s99shah.pairs in B but not in A? left outer join, check for unmatched rows --
select b1, b2
from B
left outer
join A
on b1 = a1
and b2 = a2
where a1 is null
rudy
http://r937.com/|||Hi,
TRY THIS
Select B.* from a,b
where (+) a1 = b1;
Wednesday, March 7, 2012
How to compare structure of two database ?
I have one database
and I create new database by copy everything from first db
I then alter some column on new database
and now I would like to compare two database
How can I do it ?
There is the manul way which I am sure you know of because like most of use we don't keep track of the changes that we make until time comes to roll the thing out.
There is a great good called RedGate SQL Compare (http://red-gate.com/products/SQL_Compare/index.htm) the tools are sort of expensive but well worth the money. Also if you are interested Microsoft is developing a product call Visual Studio for Database Developers that can be found on the MSDN site. Which esentially does the same thing and can be found on MSDN subscription download too.
|||I would write a script looking at syscolumns and sysobjects from both DB's and look where they do not match, this should get you changes to the DB.|||That is basically what Red Hat does, but it takes it a step furthor and generate the change script for you. I personally think that is worth 250.00|||FYI, here is a simple script I'm using:
/*--Compare the differences in table schemas between 2 databases
--*/
/*--sample to call this stored procedure
exec p_comparestructure 'pubs','northwind'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO
create proc p_comparestructure
@.dbname1 varchar(250), --the name of the database to be comapred
@.dbname2 varchar(250) --the name of the database to be comapred
as
create table #tb1(TableName1 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
create table #tb2(TableName2 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
--Get the schema of database1
exec('insert into #tb1 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname1+'..syscolumns a
left join'+@.dbname1+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname1+'..syscomments e on a.cdefault=e.id
left join'+@.dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--Get the schema of database2
exec('insert into #tb2 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname2+'..syscolumns a
left join'+@.dbname2+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname2+'..syscomments e on a.cdefault=e.id
left join'+@.dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where TableName2=a.TableName1)
select Result=case when a.TableName1 is null and b.Ordinal=1 then 'Table:'+b.TableName2+' absent in'+@.dbname1
when b.TableName2 is null and a.Ordinal=1 then 'Table :'+a.TableName1+' absent in'+@.dbname2
when a.ColumnName is null and exists(select 1 from #tb1 where TableName1=b.TableName2) then @.dbname1+' ['+b.TableName2+'] doesn''t have column:'+b.ColumnName
when b.ColumnName is null and exists(select 1 from #tb2 where TableName2=a.TableName1) then @.dbname2+' ['+a.TableName1+'] doesn''t have column:'+a.ColumnName
when a.Iden<>b.Iden then 'Different identities'
when a.PrimaryKey<>b.PrimaryKey then 'Different Primary Key constraints'
when a.Type<>b.Type then 'Different column data types'
when a.Bytes<>b.Bytes then 'Bytes'
when a.Length<>b.Length then 'Different Lengths'
when a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint then 'Different in BitsAfterDecimalPoint'
when a.AllowNulls<>b.AllowNulls then 'Different in AllowNulls options'
when a.DefaultValue<>b.DefaultValue then 'Different default values'
when a.ColumnDescription<>b.ColumnDescription then 'Different Column Descriptions'
else '' end,
*
from #tb1 a
full join #tb2 b on a.TableName1=b.TableName2 and a.ColumnName=b.ColumnName
where a.TableName1 is null or a.ColumnName is null or b.TableName2 is null or b.ColumnName is null
or a.Iden<>b.Iden or a.PrimaryKey<>b.PrimaryKey or a.Type<>b.Type
or a.Bytes<>b.Bytes or a.Length<>b.Length or a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint
or a.AllowNulls<>b.AllowNulls or a.DefaultValue<>b.DefaultValue or a.ColumnDescription<>b.ColumnDescription
order by isnull(a.TableName1,b.TableName2),isnull(a.Ordinal,b.Ordinal)--isnull(a.ColumnName,b.ColumnName)
go
How to compare database structures?
sql-script upgrating one database structure to another. For example, these
databases are from different versions of some software, first is from early
version, next is from current, and second one contains several new tables,
sevelal new fields in old tables, several new or changed stored procedures,
UDFs and so on.
How to solve this problem using standard tools?http://www.red-gate.com/SQL_Compare.htm
"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message
news:ce42is$c8r$1@.news.rol.ru...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from
> early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored
> procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message news:<ce42is$c8r$1@.news.rol.ru>...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored procedures,
> UDFs and so on.
> How to solve this problem using standard tools?
With the tools supplied with MSSQL, there's really no way to do this
easily. Most people buy a third-party tool (Red Gate, Embarcadero
etc.) to do comparisons and migrations. There are some good, cheap
tools, and unless you have lots of time and you want to re-invent the
wheel, you should probably investigate what's available.
Simon|||> > How to solve this problem using standard tools?
> With the tools supplied with MSSQL, there's really no way to do this
> easily. Most people buy a third-party tool (Red Gate, Embarcadero
> etc.) to do comparisons and migrations. There are some good, cheap
> tools, and unless you have lots of time and you want to re-invent the
> wheel, you should probably investigate what's available.
Thank you! Now I guess why I have found nothing in the BOL :)|||The latest issue of SQL Magazine has a tool from www.apexsql.com that may
help you out.
Oscar
"Evgeny Gopengauz" <evgop@.ucs.ru> wrote in message
news:ce42is$c8r$1@.news.rol.ru...
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from
early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored
procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||In a pinch, you can always dump the structures of the two databases into
two text files and use a DIFF utility to identify the differences. No
very sexy but it gets the job done.
Farid
Evgeny Gopengauz wrote:
> Let us suppose that I have two similar databases and need to create an
> sql-script upgrating one database structure to another. For example, these
> databases are from different versions of some software, first is from early
> version, next is from current, and second one contains several new tables,
> sevelal new fields in old tables, several new or changed stored procedures,
> UDFs and so on.
> How to solve this problem using standard tools?|||Hi Evgeny,
Try dbMaestro. It's a product that allows comparison, migration and
archiving of database schema and data. it is generate migration script
for different between schema and data , and you can run this script on
sql server
You can find it here:
http://www.extreme.co.il
fn <f_n_a_c_e_r_removeunderlines@.hotmail.com> wrote in message news:<R7OdnW0-MNboz5rc4p2dnA@.britsys.net>...
> In a pinch, you can always dump the structures of the two databases into
> two text files and use a DIFF utility to identify the differences. No
> very sexy but it gets the job done.
> Farid
>
> Evgeny Gopengauz wrote:
> > Let us suppose that I have two similar databases and need to create an
> > sql-script upgrating one database structure to another. For example, these
> > databases are from different versions of some software, first is from early
> > version, next is from current, and second one contains several new tables,
> > sevelal new fields in old tables, several new or changed stored procedures,
> > UDFs and so on.
> > How to solve this problem using standard tools?
Friday, February 24, 2012
How to combine different data regions in a single report?
Hi, all here,
I am trying to use report designer to create a single report with chart data region and table data region combined within the same report. Would anyone here please guide me how to work it out? Thanks a lot.
With best regards,
What do you mean by combined? You can put several data regions in a report and each can have their own datasource or share the same. If you want to have a chart data region inside a table data region, then I believe that cannot be done. If you could be a little more specific as to what you want to accomplish that would help to answer your question.|||Hi,Itzeld,thanks, got it done.How to close all open "handles" to a SQL Server 2005 express DB?
I'm trying to create an application which makes a copy of the database file it is using on application start and can restore it at any time, if needed (little project about SQL injection, so I need the copy ;-) ). This works fine, but as soon as I make my first connection to the db (connection_object.open()) and close it again I can not access the file anymore, because it is "in use by another process". How can I release all handles from the database, so that I can replace the DB file with it's backup copy?
Thank you
KoljaYou have to kill connection instead of just closing.
Shot two bulets in his head and call GC to finish him.
|||Ahm...
cal me a noob, but I disposed it and set the variable to null... doesn't work
What do you mean : Call GC?
Sunday, February 19, 2012
How to choose file structure on SQL database?
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,
Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,
|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,
How to choose file structure on SQL database?
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,|||Mike Torry wrote:
> I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this datanas
e.
> All these logical drives are located in SAN storage configured with RAID10
.
> Should I create one NDF on each logical drive OR create multiple DNF on ea
ch
> logical drive?
> Which way is better for performance?
> Thanks,
Hi,
If it's logical drives rather than physical drives, it won't help you a
lot to split the files on several drives. If you want to increase
performance, the data will have to placed on different physical spindles
and also preferably on different controllers (in most cases you'll just
have your MDF files disks on one controller and your ldf files disks on
a second controller). If you have any influence on hos your SAN is being
configured, you should also consider have more spindles in an array
rather than having many arrays. Depending on the usage of your database
it will give a better performance to have more spindles to read from
rather than splitting the load on several arrays/disks.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,
How to choose file structure on SQL database?
I need to create more DNF files on other 5 logical drives for this datanase.
All these logical drives are located in SAN storage configured with RAID10.
Should I create one NDF on each logical drive OR create multiple DNF on each
logical drive?
Which way is better for performance?
Thanks,Mike
What is DNF files? Is it NDF? Have you monitor your SQL Server to make such
decision?
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,|||Mike Torry wrote:
> I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this datanase.
> All these logical drives are located in SAN storage configured with RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on each
> logical drive?
> Which way is better for performance?
> Thanks,
Hi,
If it's logical drives rather than physical drives, it won't help you a
lot to split the files on several drives. If you want to increase
performance, the data will have to placed on different physical spindles
and also preferably on different controllers (in most cases you'll just
have your MDF files disks on one controller and your ldf files disks on
a second controller). If you have any influence on hos your SAN is being
configured, you should also consider have more spindles in an array
rather than having many arrays. Depending on the usage of your database
it will give a better performance to have more spindles to read from
rather than splitting the load on several arrays/disks.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hello,
Probably you should be talking about NDF files. Since it is SAN just try
creating the database in a Single Logical drive (1 logical drive for MDF
and another for LDF) and see how database performs. See the I/O queue length
counter in perfmon and seee how well it goes.
If it quue length is always <=1 then you are good. I have the similar
environemnt and I have created database based on this strategy.
I will recommend you to keep the files in multiple drives if you have
multiple controllers running. Otherwise even if you create files
in multiple logical drives you will not get any performance improvements.
Thanks
Hari
"Mike Torry" <MikeTorry@.discussions.microsoft.com> wrote in message
news:FDBA14B3-18FC-4723-8D73-08A46ECD921C@.microsoft.com...
>I created MDF file and LDF for one database on SQL server 2000.
> I need to create more DNF files on other 5 logical drives for this
> datanase.
> All these logical drives are located in SAN storage configured with
> RAID10.
> Should I create one NDF on each logical drive OR create multiple DNF on
> each
> logical drive?
> Which way is better for performance?
> Thanks,
How to check whether SSIS is installed
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - inSsrc_txt" (1).
(SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (43).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [BayadaPortal].[dbo].[inSsrc] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Please see this thread:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=110020
SSIS ships as part of SQL 2005, but the setup give you a choice of whether to install it or not.
Please rerun the setup. In one of the first screens the setup program asks which components to installs, there are 5 checkboxes total: SQL/SSAS/SSRS/SSIS and Workstation components. Please make sure the SSIS check box is checked.
|||"Integration Services" is greyed-out as an installation option, I'm guessing, because I'm running XP. It seems odd that I can install Management Studio but cannot use the import-export wizard. Is this right? IS is not considered to be a "client tool" which the documentation says can be installed on XP?|||Thanks for reporting this problem, it seems like we have a bug here.
Some components of SSIS (e.g. designer) are installed and work with 'Workstation Components' install. Other components of SSIS (e.g. service, dtexec, and dtexecui) require installation of 'Integration Services'.
In current build the wizard can't execute the package it created without 'Integration Services' installation, which for Enterprise Edition means you need Server OS. You can still save the package and run or schedule it on machine with SSIS installed.
We will fix this problem for SP1 release, the wizard should be able to run the package with just 'Workstation Components' install. Unfortunately, we missed this problem.
Thanks,
Michael.
I ran into the same thing in September CTP with an attempted import of an Excel 2003 spreadsheet. I had installed SSIS and was consistently running numerous fairly sophisticated ETL packages between MSSQL Server 2005, MSSQL Server 2000, Oracle and other sources. It does not appear that the error is related to SSIS not being installed.
Thanks.
|||And BTW - I immediately got it to work after importing the sheet into MS Access, and then using MS Access as the import source for MSSQL Server 2005 ...Thanks.|||
I'm getting this same error when using the RTM release of the SQL Management Studio Import wizard to import data from Excel into SQL 2000. Does anyone know how to fix this?
TITLE: SQL Server Import and Export Wizard
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - AgentDirectoryDownload$" (1).
|||I also get this error with the RTM release of Management Studio. It seems ridiculous that the Import/Export wizard can't be run on a machine with just the workstation tools installed. I really don't want to install Developer Edition on all of the machines here. Is there no other workaround? Import/Export seems to be a natural function of a client-only installation.Sean Carpenter|||
We've reconsidered this issue, and will be providing a fix for SP1 release.
For now, you need to install SSIS to run the package in the wizard.
|||I would really like to see this available as a hotfix before a service pack. Our company does a lot of data loading utilizing the Import/Export wizard and this issue will prevent us from upgrading to SQL Server 2005.Sean Carpenter|||
If this is blocking your move to 2005, you should consider opening a case with customer support, reviewing with them the content of this thread, and pressing for the hotfix.
With few exceptions, hotfixes are issued in response to escalated customer support calls.
jkh
|||I too am experiencing this same problem. I am using the standard version of SQL Server Management Studio that I obtained for free at the SQL Server 2005 launch party in San Francisco. I am running Windows XP. I have tried to migrate tables from my remote SQL Server 2000 DB without success. Exporting to Access was successful, but when I import to the new DB I get the following errors:- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - flo_layouts" (46) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
I just wanted to weigh in and say that it would seem like this would be important enough for a quick 'hotfix'. Ease of migration using these tools is by far the most important reason I switched from MySQL to SQL Server and with these functions not working it is a real bummer.
Can anyone suggest a temporary work-aruond?
-Damien
|||Damien, your problem is completely unrelated to "product level" issue. I would suggest starting another thread specific to your issue, so that someone who knows this area might notice and help.|||We are having the same problem, only we get it on the server that SQL is installed on. We are running the 64bit RTM version downloaded from MSDN, with all options installed. I get the same errors, if I try to import from Excel, csv and sometimes from other tables on the same 2005 database. This last one is only sometimes.
CJ
How to check whether SSIS is installed
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - inSsrc_txt" (1).
(SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (43).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [BayadaPortal].[dbo].[inSsrc] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
Please see this thread:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=110020
SSIS ships as part of SQL 2005, but the setup give you a choice of whether to install it or not.
Please rerun the setup. In one of the first screens the setup program asks which components to installs, there are 5 checkboxes total: SQL/SSAS/SSRS/SSIS and Workstation components. Please make sure the SSIS check box is checked.
|||"Integration Services" is greyed-out as an installation option, I'm guessing, because I'm running XP. It seems odd that I can install Management Studio but cannot use the import-export wizard. Is this right? IS is not considered to be a "client tool" which the documentation says can be installed on XP?|||Thanks for reporting this problem, it seems like we have a bug here.
Some components of SSIS (e.g. designer) are installed and work with 'Workstation Components' install. Other components of SSIS (e.g. service, dtexec, and dtexecui) require installation of 'Integration Services'.
In current build the wizard can't execute the package it created without 'Integration Services' installation, which for Enterprise Edition means you need Server OS. You can still save the package and run or schedule it on machine with SSIS installed.
We will fix this problem for SP1 release, the wizard should be able to run the package with just 'Workstation Components' install. Unfortunately, we missed this problem.
Thanks,
Michael.
I ran into the same thing in September CTP with an attempted import of an Excel 2003 spreadsheet. I had installed SSIS and was consistently running numerous fairly sophisticated ETL packages between MSSQL Server 2005, MSSQL Server 2000, Oracle and other sources. It does not appear that the error is related to SSIS not being installed.
Thanks.
|||And BTW - I immediately got it to work after importing the sheet into MS Access, and then using MS Access as the import source for MSSQL Server 2005 ...Thanks.|||
I'm getting this same error when using the RTM release of the SQL Management Studio Import wizard to import data from Excel into SQL 2000. Does anyone know how to fix this?
TITLE: SQL Server Import and Export Wizard
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - AgentDirectoryDownload$" (1).
|||I also get this error with the RTM release of Management Studio. It seems ridiculous that the Import/Export wizard can't be run on a machine with just the workstation tools installed. I really don't want to install Developer Edition on all of the machines here. Is there no other workaround? Import/Export seems to be a natural function of a client-only installation.Sean Carpenter|||
We've reconsidered this issue, and will be providing a fix for SP1 release.
For now, you need to install SSIS to run the package in the wizard.
|||I would really like to see this available as a hotfix before a service pack. Our company does a lot of data loading utilizing the Import/Export wizard and this issue will prevent us from upgrading to SQL Server 2005.Sean Carpenter|||
If this is blocking your move to 2005, you should consider opening a case with customer support, reviewing with them the content of this thread, and pressing for the hotfix.
With few exceptions, hotfixes are issued in response to escalated customer support calls.
jkh
|||I too am experiencing this same problem. I am using the standard version of SQL Server Management Studio that I obtained for free at the SQL Server 2005 launch party in San Francisco. I am running Windows XP. I have tried to migrate tables from my remote SQL Server 2000 DB without success. Exporting to Access was successful, but when I import to the new DB I get the following errors:- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - flo_layouts" (46) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)
I just wanted to weigh in and say that it would seem like this would be important enough for a quick 'hotfix'. Ease of migration using these tools is by far the most important reason I switched from MySQL to SQL Server and with these functions not working it is a real bummer.
Can anyone suggest a temporary work-aruond?
-Damien
|||Damien, your problem is completely unrelated to "product level" issue. I would suggest starting another thread specific to your issue, so that someone who knows this area might notice and help.|||We are having the same problem, only we get it on the server that SQL is installed on. We are running the 64bit RTM version downloaded from MSDN, with all options installed. I get the same errors, if I try to import from Excel, csv and sometimes from other tables on the same 2005 database. This last one is only sometimes.
CJ