binding input parameters leads to SQL_ERROR

I am trying to read the results from a query like
select a, b
from mytbl
where col0 = ?
The first time I run SQLExecute, everything works fine, but when I call
SQLExecute a second time with a modified input parameter, it returns
SQL_ERROR. Below is the code I'm running with the error checking removed and
the names simplified.
SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&where_param, 0, 0);
SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
SQLExecute(m_hstmt) //works ok
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
where_param = 10
SQLExecute(m_hstmt) //fails
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
My goal is to run execute many times with a variety of parameters. I was
trying to have the odbc driver read from the address specified in
SQLBindParameter to set the value of the input parameter. I'm further
confused by the problem because when I try to retrieve error information by
calling SQLGetDiagRec, nothing is returned. Thanks for your help.
Scott
The problem goes away when I call SQLCloseCursor after completing the fetch.
The MSDN SQLBindParameter documentation at
http://msdn.microsoft.com/library/de...dparameter.asp
gives no indication that SQLCloseCursor should be needed.
I suppose that I am now just concerned that I am introducing extra
processing overhead by calling SQLCloseCursor.
Scott
"ScottD" wrote:

> I am trying to read the results from a query like
> select a, b
> from mytbl
> where col0 = ?
> The first time I run SQLExecute, everything works fine, but when I call
> SQLExecute a second time with a modified input parameter, it returns
> SQL_ERROR. Below is the code I'm running with the error checking removed and
> the names simplified.
> SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
> SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
> SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
> &where_param, 0, 0);
> SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
> SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
> SQLExecute(m_hstmt) //works ok
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
> where_param = 10
> SQLExecute(m_hstmt) //fails
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
>
> My goal is to run execute many times with a variety of parameters. I was
> trying to have the odbc driver read from the address specified in
> SQLBindParameter to set the value of the input parameter. I'm further
> confused by the problem because when I try to retrieve error information by
> calling SQLGetDiagRec, nothing is returned. Thanks for your help.
> Scott
|||Are you making sure that you've fetch all of the rows? Even if only
one row is returned by the query, you should call SQLFetchXXX() until
it returns SQL_NO_DATA. Otherwise the driver doesn't necessarily know
that you've finished fetching rows on the current resultset/cursor.
Alternatively, you can use SQLMoreResults() to flush the remaining data
on the wire and position on the next resultset (if there is one,
otherwise it cleans up the connection).
SQLCloseCursor shouldn't add too much overhead. I'm assuming you're
using the default (firehose) cursor. If so, SQLCloseCursor just makes
sure that all of the data from the previous statement has been
consumed, and the connection is ready for the next statement.
Brannon

Bind to multiple tables from stored procedure

I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?

Isthis article of any use?|||no fortunately not :(

Big Problems

I've come across an issue which i believe is related to Windows 2003 populating database elements, notably date/time types, and having those fields read on another platform (eg. winXP, win98) and, although everything would seem to match, comparissons fail.

For example, if i populate a datetime field with the value 22:22 (eg. 10:22 pm) from a Windows 2003 pc, then read the value of the field on another pc (eg. WinXP), and perform a comparisson in code (vb6sp5), the values are not equal.

I have observed this against Access database using DAO 2.5, 3.51 and 3.6 on both sides. Also observed against SQL Server 2000.

All i can figure is that windows 2003 is causing the problem, but i don't know how to resolve it.

I understand this is not a forum for operating system questions, but my intent would be to generate a routine which would correctly populate the database elements on the win2003 side, for proper execution on the other pcs in use.

Any help would be greatly appreciated.Do you perform your comparison in code or visually? I had to use my 2003 with client tools against 2K SQL Server and had no prob. If you're doing visual comparison then you need to also account for regional settings.|||visually everything looks the same
in code, i've compared stored results vs expected results. here's a sample of what i found:

If i stored, from the Win2003 pc, the value 0:01 (12:01am, no date) in either an access or sql server field, then attempted from a winxp pro machine to compare the stored number to winxp's interpretation of 12:01 am, the values would look to be the same, but vb would not claim them to be equal. if i check the difference between the two, surprisingly, the difference is 1.0842021724855E-19, and that's enough to cause this symptom. the difference fluctuates, with 12:23 am working and being equal when the above steps are followed. the pattern of equal/not equal is very strange indeed.

i have verified regional settings on both pcs. no problems.

this all works fine with all pc's being win2003. it seems that 2003 is treating something different, so comparisons to a value stored from the win2003, then compared on another os, it all fails.|||This is not a SQL issue. It's a VB issue. I dont get any problems like this in VB.NET. It's been so long for VB6 and I dont really feel like installing it so I wont be able to do any VB6 testing. Have you tried using VB.NET instead? I'm sure you'll be cursing at VB6 in no time once you switch to .NET. :D

Also what version of Access? 97, 2k?|||hahahaha

oh yeah, i've used vb.net and it's a much better development platform for sure. i will test this scenario in vb.net and see what the results are.

unfortunetly, i am supporting legacy applications and bumping up to vb.net does not give me the immediate solution i require.

has nobody else observed this behaviour?

access 97 and 2000
sql server 2000 (msde and enterprise) v8.00.760

Big Database

Hello all, i have a problem with a big database, the problem is that any query executed on that database, takes much much time, but i think that the db is over sized (5 gb aprox), because the table that contains most records has only 650.000, and i worked with db containing tables with more records and that not pass 1 gb.

Question 1: Why the db has grow so big?
Question 2: What can i do to speed up the querys?

Thanks for your help, and sorry for my english!I see that the database file has 650 mb (that's ok) but the log file has 5350 mb (that's absolutly wrong), i tried to truncate or shrink log file, but does'nt work.|||If you are not doing transaction log backups, you need to set the recovery mode to simple. After this, shrink the files and they will disappear to almost nothing. You only need full recovery mode, which is what you are probably using if your log files are that big.|||hey, i have reading a bit about that question of the recovery mode, but thanks for your suggestion, now my DB has 560 mb only!, that's great, but, the querys are a little bit slow yet!

Remember, i'm a programmer, not bilingual! jejeje|||Hello parmaia,
If you interested in making the queries to execute faster - I would start with doing a performance audit of your box . Check out sql-server-performance.com , Brad M. McGehee did a very nice series of articles on how to audit sql db server

http://www.sql-server-performance.com/sql_server_performance_audit.asp

If you run into questions, ask (either here or within sql-server-performance forums)

simas

BIDS interface question

Hi,

When I open a project in BIDS, for some reason it automatically opens a new tab for each and every package in the project, which is both time consuming and a bit annoying.

I don't want to open a tab for a package unless I do it myself.

Is there a way to turn off this behavior?

Thanks

BIDS is trying to open every single package you left open the last time closed it. I always try to open only packages that I am working on and close them before closing BIDS. One shortcut is to choose 'Close Solution (or project)' from File menu before exiting.

BI Dev Studio won't install with June CTP reinstallation

I am attempting to install the BI Development Studio, from the SQL Server 2005 June CTP, on an XP machine. It installed successfully the first time I installed it, along with all other Workstation Components and BOL.

Subsequently I have uninstalled and reinstalled the Workstation Components, but the installation process does not fully install BI Development Studio. It creates the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE directory (where devenv.exe should be located), but this directory is empty other than the PrivateAssemblies and profiles subdirectories. After an installation , the Start Programs menu contains an option for “SQL Server Business Intelligence Development Studio” under "MS SQL Server 2005 CTP", but this shortcut points to the non-existent C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe

There are no installation errors, and everything else seems to install OK, but BI Dev Studio is simply not there..

I have used both Add/Remove Programs, and the Build Uninstall Wizard, to remove the installation before trying the reinstalls.

This machine also has Visual Studio 2003 and a SQL Server 2000 instance, but they were already in place prior to my first successful installation.

Thanks in advance!

This is not a very satisfying answer, but here's how we fixed it.

After running the Build Uninstall Wizard once again, we manually removed all folders and all files throughout the hard drive related to SQL Server 2005 (i.e. 9) and Visual Studio 2005 (i.e. 8). Also all registry entries. After manually removing all of this, the installation worked successfully.

Bottom line: the Build Uninstall Wizard fails to remove quite a few files and registry settings on your machine, some of which prevented the successful reinstall of the BI Dev Studio.

BI Crashing - Untrapped error

Visual Studio is just disappearing without displaying any messages in the cube editor for a fairly complex cube every time I switch to the Calculations, Perspectives, or Translations tabs. The Cube Structure, Dimension Usage, KPIs, Actions, Partitions, and Browser tabs are fine. I recently applied SP2 but I don't think that is the issue as a simple cube works correctly. More likely, the problem was caused by a change in the dimension key for our time dimension. I was testing changing the grain of the time dimension because we have measure groups at different grains of the time dimension (day and month) and this design decision was rendering Report Builder essentially useless. Modifying all of our measure groups to associate at the month grain resolved the Report Builder problem but now I can't create perspectives to simplify the user presentation.

What's unique about these three tabs and what can I do to fix the problem? Any ideas?

Not sure if this is an actual coolprint in your case, but once I saw similar problem when switching to the Calculation tab when I had mismatched versions of the following files: msmdlocal.dll and msmgdsrv.dll. You might want to search for those files on your drive, since setup installs a few copies of them.|||I looked at that but that was not the cause. It doesn't happen on all cubes. I uninstalled SQL Server and reinstalled and the same strange error is still occurring. It would be nice if the error was trapped so I could give you more information.|||

The symptoms described in the following KB article describe exactly what I'm seeing.

http://support.microsoft.com/default.aspx/kb/926421

I do not have Office 2007 installed and have applied SP2. Any ideas?

BI Accelerator experiences...?

Hi,
I'm just embarking on a data warehousing project for the first time. I have
a good understanding of the general principles - dimensional modelling, fact
tables, ETL, data staging, cubes, etc.
I am interested to get feedback from people who have used BI Accelerator for
SQL Server 2000 to automate the task of building a data warehouse and
analytical applications. Does it work as advertised? How productive can you
be compared to 'hand coding' the process?
Also, I would like to know why the production deployment of BI Accelerator
applications requires SQL Server Enterprise edition?
Thanks, in anticipation.
Chrisamong other reasons - partitioning
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/

between dates and times

Hello,
I am new to SQL and am having a problem with dates and times.
I have a table with a 4 columns. Date, Time, Material, Qty
example;
01/12/05 04:00 steel 35
01/12/05 06:00 steel 60
01/12/05 23:40 steel 40
01/12/05 08:00 iron 25
02/12/05 05:00 steel 20
02/12/05 06:00 steel 50
I want to be able to look for matching rows that have a start & end date and
start & end time.
i.e start date 01/12/05 time >= 06:00
end date 02/12/05 time <= 05:59
The times are aways static. I ideally would like to see one row returned tha
t
summed the last column and combined the different dates like;
01/12/05 steel 120
01/12/05 iron 25
however if I searched for 01/12/05 and 03/12/05 I would see;
01/12/05 steel 120
01/12/05 iron 25
02/12/05 steel 50
Any help or guidance will be appreciated.What are the datatypes of the date and time columns. The ideal solution
would be to represent them as a single datetime value.
In any case, as a short term solution, check out the CONVERT function in SQL
Server Books Onlne. There are arguments that can help you convert a datetime
value to date-only string and a time-only string.
Anith

BETWEEN DATES (TIME) HELP

Hi,

I need to display the employees from a current work shift. These are the following work shift:

4 AM – 12 PM

12 PM – 8 PM

8 PM – 4 AM

I’m having problems in third one “8 PM – 4 AM”. The next code is the one that I’m working on. If somebody knows a better way to do this let me know or if somebody can help me with this go ahead.

begin

declare @.from_time varchar(25);

set @.from_time = '8:00:00.000 PM';

declare @.to_time varchar(25);

set @.to_time = '4:00:00.000 AM';

declare @.current_date VARCHAR(25);

set @.current_date = getdate();

--select convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time),

--convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time),

--getdate();

if getdate() BETWEEN convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time) AND

convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time)

print 'OK';

else

print 'NO';

end

That's because of midnight crossover - the dates are changed also. Try this, it's far more simple and reliable:

Code Snippet

declare @.From int, @.To int, @.DT int

select @.From = 20, @.To = 4, @.DT = datepart(hh, getdate())

if @.DT >= @.From or @.DT < @.To
print 'OK'
else
print 'No'

|||Yeah. Thanks.

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>

Better query?

I have a table from a 3rd party application that stores the time that
events occurred as the number of seconds since Jan 1 1970. I want to
perform an aggregate function on the values stored in each event, grouped
by date and present the sorted results. Here's what I have:
SELECT
DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
Avg(Value)
FROM Data
GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
Is there an easier way to write this without duplicating the DateAdd
calculation three times' I can't use Stored Procedures (I'm not allowed
to modify the database in any way.)
-mdb>> have a table from a 3rd party application that stores the time that even
ts occurred as the number of seconds since 1970-01-01 <<
Oh yeah, that makes sense :) !! I always hated the term "legacy data"
when what we meant was "family curse from Hell!" instead.
Build a table like this:
CREATE TABLE FoobarDates
(foobar_date CHAR (10) NOT NULL,
start_second INTEGER NOT NULL,
finish_second INTEGER NOT NULL
PRIMARY KEY(start_second, finish_second ));
1) Avoid math in SQL-- it is designed to do JOINs. not algebra.
2) An ORDER BY on a SELECT is **meaningless** -- read any basic SQL
book. Indexes matter.
3) Use a spreadsheet to do the computations for the table. Easy and
faster than code.|||you can use a derived table Michael...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>I have a table from a 3rd party application that stores the time that
> events occurred as the number of seconds since Jan 1 1970. I want to
> perform an aggregate function on the values stored in each event, grouped
> by date and present the sorted results. Here's what I have:
> SELECT
> DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
> Avg(Value)
> FROM Data
> GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
> ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
> Is there an easier way to write this without duplicating the DateAdd
> calculation three times' I can't use Stored Procedures (I'm not allowed
> to modify the database in any way.)
> -mdb|||Sorry, that should be...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate = DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl...
> you can use a derived table Michael...
> SELECT PointDate, Avg_Value = Avg( Value )
> FROM (
> SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
> Value
> FROM Data ) AS dt
> GROUP BY PointDate
> ORDER BY PointDate ASC
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
> news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1135732983.997588.94360@.o13g2000cwo.googlegroups.com:
> Oh yeah, that makes sense :) !! I always hated the term "legacy data"
> when what we meant was "family curse from Hell!" instead.
As a matter of fact, this is NOT legacy Data. In fact, it is an
application that would not have existed 10 years ago, because the
technology that is represented by the data in the database didn't exist.
(I'm not going to mention the vendor, but it is an IP Telephony monitoring
system.)
The rest of your post sounds like a troll, so I'm not going to comment,
except for this comment.
-mdb|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl:

> you can use a derived table Michael...
>
Wow... you know I had always tried to do something similar, but I never
could get it to work because I didn't know that I had to include the 'AS
<alias>'. Thanks! Now I get to go back and re-write a bunch of stored
procedures in other databases I've worked on that I used memory tables in!
:)
-mdb

Best way to worj with replication

Hello there
I have subscription who work perfectly
In order to see all the chages at the time it being done I set it to work as
soon as the publicion is done.
But this way cost in working of comouter. the computers are working mutch
more sloly.
So i set it to work at 00:00 every day.
It seems that on 00:00 it do update the subsriber. But is there a way to run
it maually?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Sorry Roy - I didn't really follow what your scenario is. However to answer
your last question, running the subscription agent (distribution/merge)
manually can be achieved by running sp_start_job, or windows synchronization
manager (pull) out of the box or by programming the activeX controls if you
want a custom solution.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||What Version of SQL Server are you using? 2000, 2005?
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Roy Goldhammer" wrote:

> Hello there
> I have subscription who work perfectly
> In order to see all the chages at the time it being done I set it to work as
> soon as the publicion is done.
> But this way cost in working of comouter. the computers are working mutch
> more sloly.
> So i set it to work at 00:00 every day.
> It seems that on 00:00 it do update the subsriber. But is there a way to run
> it maually?
> --
> ??òé a?ì????
> òúé? ?e??ú ú??e?
> èì' 03-5611606
> ?ìX' 050-7709399
> Xé?ééì: roy@.atidsm.co.il
>
>
|||Whell Scott: 2000
When i sed the push subscription to work immediatly when the chages are done
be the publisher, the changes are being done on the subsciber in 3 seconds,
bet the performance of the disbributor computer becomes mutch more slower.
and that for only one table on my huge database (Approx 1.5Gb)
My main question is: if i will run the replication on the entire database,
how mutch it will cost on the performance of the computer? and if i will run
it at 00:00
and the changes that are being made on single day can be more then 10000
records a day, what will happen?
My second question: As i see on the wizart, when i create Transactional
replication, it said that at first time he run Snapshot replication.
Is that at first time on the subsciber i need only an empty database with
the same structure of the publisher database?
"Scott Hunley" <ScottHunley@.discussions.microsoft.com> wrote in message
news:E90C578A-8256-454F-AEE5-D744F529B760@.microsoft.com...[vbcol=seagreen]
> What Version of SQL Server are you using? 2000, 2005?
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once...
>
> "Roy Goldhammer" wrote:

Best way to store information

Hi,

I am using MSDE in my applications. Sometime the tables are large and it takes a time for reading the data by query.

1. If I split the tables it can help me? or not?
2. May be exist another way to read the data?

My application wrote in C# (dot.Net 1.1) using ADO (SQLClient).

Thank's
Alexei

there are a lot of things u can do to reduce the time taken...eg..tune ur queries, create indexes on tables..etc... but comming to ur specific questions
1. spliting the tables...
as ur using sql server 2000 msde , horizontal partition is not too well supported..and it helps most if u keep the partitions on different drives , having seperate headers.....this article gives some info
http://www.sqlteam.com/Item.ASP?ItemID=684

2. other ways...as i said earlier there r a lot of things u can do...identify the bottelnecks and then work towards improving them...|||

Alexei:

Can you present the queries that are giving you problems? Please include some short example data and a brief listing of the composition of the target table in question. It is easier if we know the target problem.

Dave

|||

You are seriously going to have to give way more information for anyone to answer this question.

You state you are using MSDE, is this as a server? How many users? If you have > 8 simultaneous queries it will start to punish you and seriously degrate performance.

And what do you mean large? Can you post table creations, some sample data, and most importantly, how many rows you have? It might be indexing, it could be poorly written queries, it could be a server/ram issue, but without the table create scripts, it would all be an uneducated guess...

Best Way to 'Program' for SRS

My background is as a Java programmer.
I am using SRS to create some reports. I am having a hard time getting used
to only dealing with the output from the database server. I'm used to having
access to objects, methods, etc...
I am trying to determine if I am better off just writing stored procedures
or if there is some way to use a .Net language (perhaps C#).
My data needs a fair amount of processing after it comes out of the database
before it is ready for display.
For instance, I need to get a row, perform some calculations, update another
row and then display... I think I could get it done in a stored proc, but it
still seems like a limited environment and a lot of what I need seems to
require cursors, which I am told are expensive.
So... Any comments on the best way to attack this?
Can I use C# to sit between my database and SRS? What kind of output would
my C# classes send to SRS?Hunter,
I would gravitate toward doing as much as possible data massaging at the
data source level. It will be less expensive than doing it in code. RS
allows you to use expressions where the field values can be changed before
displayed on the report but in your case this may not be enough. Another way
you can tackle this is to create a custom data extension which will get the
dataset, pre-process it, and return it to the report. Mine ADO.NET dataset
extension could get you started.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?|||I agree with Leo... If there are common functions, etc, do these in views
or in the sql for your data source. Especially when you do views, these
expressions can be used by many reports, ( like formatting a name, or date,
or taking several values and making some business calculation). You can
write code in each report using VB, and that is OK for small things, but the
code is not sharable across many reports except though copy/paste. Which
leaves writing a customer assembly, and referencing it in reports. This
allows for sharing a single code base of common functions across many
reports, but increases the level of complexity and management...
hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:7D8F6391-A8BB-49A8-B279-7EFF622398B9@.microsoft.com...
> My background is as a Java programmer.
> I am using SRS to create some reports. I am having a hard time getting
used
> to only dealing with the output from the database server. I'm used to
having
> access to objects, methods, etc...
> I am trying to determine if I am better off just writing stored procedures
> or if there is some way to use a .Net language (perhaps C#).
> My data needs a fair amount of processing after it comes out of the
database
> before it is ready for display.
> For instance, I need to get a row, perform some calculations, update
another
> row and then display... I think I could get it done in a stored proc, but
it
> still seems like a limited environment and a lot of what I need seems to
> require cursors, which I am told are expensive.
> So... Any comments on the best way to attack this?
> Can I use C# to sit between my database and SRS? What kind of output would
> my C# classes send to SRS?

Best way to generate script

Hi!

I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:

ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")

For Each uneTable In BDConfig.Tables

If Not uneTable.IsSystemObject Then

NouvelleBD.ExecuteNonQuery(uneTable.Script)

End If

Next

We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?

Thank and sorry about my bad English ^_^

I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||

I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:

Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure

db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.

|||

Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.

Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.

|||

I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.

Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size

dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent

dbCopy.Create() 'Create the database

'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()

|||Thanks but i have the same problem. When i tried to copy all objects it failed sometime (not all the time). I've got a message saying that it cannot created a view because the view use in the SELECT do not existe. In fact the problem is beacause it's trying to create the "child" view befor the "parent" view.

I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||

Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.

For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.

I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.

Best way to generate script

Hi!

I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:

ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")

For Each uneTable In BDConfig.Tables

If Not uneTable.IsSystemObject Then

NouvelleBD.ExecuteNonQuery(uneTable.Script)

End If

Next

We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?

Thank and sorry about my bad English ^_^

I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||

I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:

Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure

db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.

|||

Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.

Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.

|||

I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.

Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size

dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent

dbCopy.Create() 'Create the database

'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()

|||Thanks but i have the same problem. When i tried to copy all objects it failed sometime (not all the time). I've got a message saying that it cannot created a view because the view use in the SELECT do not existe. In fact the problem is beacause it's trying to create the "child" view befor the "parent" view.

I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||

Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.

For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.

I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.