HELP HELP varchar

I have a field type of "varchar"

It can not accept to Store Arabic Characters!!! plus i can't change it into ntext or nvarchar my system which goin to export the data into SQL server by suing insert command will hang. so im stucked with varchar!!!

here some sample of my data stored currently in My SQL SERVER 2000

" /   "

Plzzzz help me if there any other way to do it

Do you have the right collation set in your database?

Have a look in BOL (Books on Line) at COLLATE - there's a lot of info in there about how to deal with charachters from different languages and alphabets.

/Kenneth

|||If you want to store arabic characters you will have to change your column data types to nvarchar. So you will have to investigate the problem you are describing while using the nvarchar column. What is the actual problem here ? I am not sure / doubt that this is related to collation problems, but further investigation might help us to solve the problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Help getting error when using operation rename a file in the File system task editor?

Does anyone know how to do this using variables? Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001.

I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:

@.[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:\Documents and Settings\mroush\Desktop\OSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

I am not sure how you are doing this, were is the expression and which variable is selected in the File System Task editor.

How about this-

Create a variable called Filename.

Then go to the Properties grid for the Filename variable and set the EvaluateAsExpression to true. Now set the Expression property to your expression.

Now go back to the File System Task, and select variable Filename for the operation you want.

Is this what you have done or not?

Do you really need to rename the file? Normally I would use an Expression, like you have above, but on the ConnectionString property for my File/FlatFile connection, such that when I use the connection it automatically gets the "current" filename.

|||

Darren, thanks for the reply and

yes I done this in the expression property of the variable and set it to true, that works except it adds the file name twice. The result is below.

C:\Documents and Settings\mroush\Desktop\Cnet Apps File\Apps0821.txt0821.txt

How do I get it to only evaluate once and and it looks like everyday that it runs, it is going to just keep adding to the string? Is there away to make it clear the old filename before renaming the current one everyday or is there another way to do this? I have to rename the file with the current date everyday, instead of using 1 filename and setting the overwrite destination to true. Maybe this can't be done using this task?

Mike

|||

I don't get why there appears to be two filename parts. You have got two variables, one for the base path an then one for the full filename, upon which you set the expression?

Creating a new file everyday should be fine using an expression. If you are exporting thsi file, then why not set the expression on the flat file connection manager's connection string property, and save the extra step of the rename.

|||

Hi Darren, thanks again for responding.

Here are the two files that I am working with:

The file comes in everyday and is zipped as AAA20060822 with no extension. The numbers are the date and it changes everyday with the current date so I need the expression to get that date everyday when a new file appears. Like tomorrows file will be AAA20060823

The filename that I need to rename it as is BBBB0822.txt. It has to be renamed this way with the date format this way. I'm using 2 variables so that the dates are updated everyday automatically so that the task can be scheduled nightly. I know this is confusing but this is how the file comes in everday and this is how it needs to be renamed so the data can be processed correctly through our mainframe.

I've tried setting the isdestination and issourcepath variables to true and using the expressions in the source and destination properties through the expressions option but it errors telling me that it can't lock the variables or the variables have been renamed or do not exist in the container. Error 0xC00100001

So then I tried putting the expressions in the variables expression property and setting the evaluateasexpression property to true but it evaluates the expression twice each time I run the task adding the dates twice to the path causing it not to find the source file. I'm stupmed! I don't know what else to do? I works either way without an expression hard coding the filenames but not using variables that will change everday with the date?

Thanks,

mike

Help for date in insert query

i want to save date using inert query like insert into tablname(field1,f2) values('jan',"& format(system.date.now,"dd/MM/yyyy hh:mm ") so to give error that char will not be converted to date and time.plz help its urgent.the same problem is with select query toooooo.

Hi,

You should elaborate more about the problem here. Post your code and let us know the way you are inserting data into the database and the fields types

Regards

|||

i want to insert in a data base some thing using this query:

insert into tble1(field1,field2) values('this some thing or data','"& syatem.now &"');

which kind of date it will store like this 24/05/2007 11:16 AM or 24/05/2007 .i want to store 24/05/2007 11:16 AM this one.i using format function of vb.net like format(system.date,"dd/MM/yyyy hh:mm tt") but it give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".in the select statement also this problem occure. i also use thhe select query as

select * from tbl1 where pdate='"& format(variabledate,"dd/MM/yyyy hh:mm tt") &"'" and it also give me error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".this in acess work fine but in sql server give the above error.

|||

Hi,

please try something like following

Datetime dt = newDateTime(2007,6,18,13,56,30)); //year, month, day, hour, minute, seconds

thanks,

satish.

|||

Hi there,

Use this command:

INSERT INTO TABLENAME (field1,field2) VALUES ('jan',getdate())

hope it helps you out,

gonzzas

|||

This is one of those difficult to answer questions because what you are trying to do is inheritly wrong. Datetime fields do not have a format. They represent a particular point in time. You can convert a datetime to and from a string with a specific format, but then it's no longer a datetime. You are trying to store a string with a format that sql server doesn't understand into a datetime field. SQL Server is attempting to covert your string into a real datetime so it can be stored, but the format isn't one it understands. Regardless of any of the correct formats you feed it, so long as it means the same point in time, ultimately it will get converted to the exact same number (Datetimes are internally represented by the number of days since the epoch, with the time portion being a fraction of a day).

|||

Hi again,

Here is another example of a sql command that inserts a row in a table. One of the row fields has DATETIME datatype:

Dim strAsString ="insert into users (username,password,date) values ('username1','password1',convert(datetime,'18-06-2007 23:21:00',105))"

Dim commandAsNew SqlCommand(str, conn)

Dim result = command.ExecuteScalar()

Where you see 105 it's the format chosen by me for this test. This format stores the data as dd-mm-yyyy hh:mm:ss

You can use another formats (do some light research on the subject, it should be enough) to meet your requirements: dd/mm/yyyy hh:mm:ss

gonzzas

|||

actually i solve the insert by using the query like

"insert into table tblname(f1,datef1) values('abc','"& system.datetime.now.tostring("MM/dd/yyyy hh:mm:ss") &"')"

but i still receiveing errors in select query that char conversion.

this stuff is doing in vb.net and sqlserver.

Help ! Procedure to delete files from operating system

Hi all,

Can anyone help me with a script which would delete files or
move them to a different folder at some scheduled time..!
Please....!!!

Thanks in advance...Raj (rjdave@.indiatimes.com) writes:
> Can anyone help me with a script which would delete files or
> move them to a different folder at some scheduled time..!

Which files? MDF, LDF and NDF files? Or just any random selected files?

In any case, you could use SQL Server Agent to create a CmdExec job for
the task. You can access SQL Server Agent from Enterprise Manager.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

The check for the date in your cursor declaration is probably the cause of
the problem!

Declare mycursor cursor for
select name from #Filenames
where convert(datetime,left(name,10)) <= getdate()-@.duration

Although you can add a style to the convert function, this is not guaranteed
to give the correct results.

From BOL:
style
Is the style of date format used to convert datetime or smalldatetime data
to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data
types), or the string format when converting float, real, money, or
smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types).

i.e no mention of use of style when converting from character to datetime

A less risky option would be to convert both to strings and then re-arrange
them into an alphabetically comparable format such as YYYYMMDD. Overall I
think the DTS/Filesystemobject may well be the faster and safer solution.

John

"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307120602.4fe8b9c7@.posting.google.c om...
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6B78068CAYazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Can anyone help me with a script which would delete files or
> > > move them to a different folder at some scheduled time..!
> > Which files? MDF, LDF and NDF files? Or just any random selected files?
> > In any case, you could use SQL Server Agent to create a CmdExec job for
> > the task. You can access SQL Server Agent from Enterprise Manager.
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?
> Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
> as
> --Objective: To delete files older than certain period from a folder
> --Usage example:
> --Exec USP_DelOldFiles 'c:\test',30 -- which deletes files older than
> todaydate-30
> --Created by :MAK
> --Created date: Jan 7,2003
> --OS: windows 2000
> declare @.myquery varchar(1000)
> declare @.query varchar(1000)
> declare @.name varchar(100)
> set @.myquery = "exec master.dbo.xp_cmdshell 'dir "+
> ltrim(rtrim(@.path)) + "\*.* /a/od'"
> print @.query
> create table #Filenames (id int identity(1,1) ,name varchar(100))
> insert #Filenames(name)
> exec (@.Myquery)
> delete from #Filenames where substring(name,3,1) <> '/' or name is
> null or
> substring(name,25,1) ='<'
> Declare mycursor cursor for
> select name from #Filenames where
> convert(datetime,left(name,10)) <= getdate()-@.duration
> open mycursor
> fetch next from mycursor into @.name
> while (@.@.fetch_status =0! )
> begin
> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'
> --print @.query
> exec (@.query)
> fetch next from mycursor into @.name
> end
> close mycursor
> deallocate mycursor
> drop table #Filenames|||Raj (rjdave@.indiatimes.com) writes:
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?

Hey, what about *you* tell us what is wrong? I mean, you say that you
are not able to run it, but rather let us guess what you mean with that,
why not specify what you. Do you get an error message? Do the script
end without an files being deleted? Did the script work from Query
Analyzer?

What I can see at a glance is:

> while (@.@.fetch_status =0! )

Syntax error.

> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'

Should have a /F to force deletion of read-only files.

Really why you make the entire call to xp_cmdshell in dynamic SQL, I
don't know, neither I have cared to check that you get the right statement.
It should be sufficient to have only the DOS command in a variable.

Then again, why SQL at all? Isn't this a symptom of that when all you
have is hammer, everything looks like nails to you. Personally I would
implement this in Perl, but if you VB script or C++, these languages
work equally well for the task. SQL is probably one of more complicated
options you can go for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Check out the method in my previous posts, your procedure should work if you
corrected the date formats and typos.
Note the escaped quotes in the command string, rather than using double
quotes.

Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
as
--Objective: To delete files older than certain period from a folder
--Usage example:
--Exec USP_DelOldFiles 'c:\test',30
-- which deletes files older than todaydate-30
--Created by :MAK
--Created date: Jan 7,2003
--OS: windows 2000
declare @.myquery varchar(1000)
declare @.query varchar(1000)
declare @.name varchar(100)
set @.myquery = 'exec master.dbo.xp_cmdshell ''dir '+ ltrim(rtrim(@.path)) +
'\*.* /a/od'''
print @.query

create table #Filenames (id int identity(1,1) ,name varchar(100))

insert #Filenames(name)
exec (@.Myquery)
delete from #Filenames
where substring(name,3,1) <> '/'
or name is null
or substring(name,25,1) ='<'

select name, SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2)
from #Filenames

/* Make sure dates are in comparable formats */
Declare mycursor cursor for
select name from #Filenames
where SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2) <=
CONVERT(char(8),DATEADD(d,@.duration,getdate()),112 )

open mycursor

fetch next from mycursor into @.name
while @.@.fetch_status = 0
begin
set @.query = 'exec master.dbo.xp_cmdshell ''del '+ @.path + '\'+
ltrim(rtrim(substring(@.name,40,59)))+''''
print @.query
exec (@.query)
fetch next from mycursor into @.name
end
close mycursor
deallocate mycursor

drop table #Filenames

This may also be useful for other things.
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://msdn.microsoft.com/library/d...asp?frame=true

John

"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307130220.6fae6dc3@.posting.google.c om...
> Hi ... Sorry but I have to clarify a few things about me first...I am
> a newbie in database administration . I am familiar with many things
> which are Oracle related but for a particular task our company is
> using SQLSERVER 2000. I am lost in this SQL SERVER world . I have only
> one task to be finished with SQLSERVER 2000 . I am slowly making "new
> discoveries" of this database software. Lots of thanks specially to
> YOU Mr.Erland Sommarskog.
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .
> Thanks for the help so far and best wishes.
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6F1164E422Yazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Thanks ...I didn't know about it ... It was great help. But still it
> > > didn't work . I wanted to run a job and the script is below . I am not
> > > able to run this script using sql server agent. Can anyone suggest
> > > whats wrong ...and whats the trick i am missing?
> > Hey, what about *you* tell us what is wrong? I mean, you say that you
> > are not able to run it, but rather let us guess what you mean with that,
> > why not specify what you. Do you get an error message? Do the script
> > end without an files being deleted? Did the script work from Query
> > Analyzer?
> > What I can see at a glance is:
> > > while (@.@.fetch_status =0! )
> > Syntax error.
> > > set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> > > ltrim(rtrim(substring(@.name,40,59)))+'"'
> > Should have a /F to force deletion of read-only files.
> > Really why you make the entire call to xp_cmdshell in dynamic SQL, I
> > don't know, neither I have cared to check that you get the right
statement.
> > It should be sufficient to have only the DOS command in a variable.
> > Then again, why SQL at all? Isn't this a symptom of that when all you
> > have is hammer, everything looks like nails to you. Personally I would
> > implement this in Perl, but if you VB script or C++, these languages
> > work equally well for the task. SQL is probably one of more complicated
> > options you can go for.|||Raj (rjdave@.indiatimes.com) writes:
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .

Still makes more sense to write a program in VBscript, Perl or
whatever that reads the required information from the database, and
then deletes the files. Running that program could possible be scheduled
from SQL Server Agent.

But running the whole thing from T-SQL with xp_cmdshell is not very
robust, becuase you need handle dateformats that that kind of thing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help - SQLExecDirect

I could really use some help, I have a system dsn and am
using the Sql Server 2000 odbc driver. I am getting an
error S1000: [Microsoft][ODBC SQL Server Driver]
Connection is busy with results for another hstmt.
When I look at the dsn settings, there is a check box to
turn off SQLPrepare. The problem is it is checked and the
box is greyed out and will not let me turn this off. Does
anyone know why this is like this, and how can I turn it
off?
Will I have to write my own driver?This has nothing to do with SQLPrepare. By default MS SQL Server and
Sybase allow only one statement per connection at a time. It requires
that you close one statement before you open another one. If you try to
open another statement, you get this error. You can change this default
behavior by using Server Side Cursors. Here is some sample code.
nResult = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
if(nResult != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO){
// Handle error
}
nResult = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)10, 0);
if(nResult != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO){
//Handle error
}
Hope this helps.
Pete.
anonymous@.discussions.microsoft.com wrote:

quote:

---

>I could really use some help, I have a system dsn and am
>using the Sql Server 2000 odbc driver. I am getting an
>error S1000: [Microsoft][ODBC SQL Server Driver]
>Connection is busy with results for another hstmt.
>When I look at the dsn settings, there is a check box to
>turn off SQLPrepare. The problem is it is checked and the
>box is greyed out and will not let me turn this off. Does
>anyone know why this is like this, and how can I turn it
>off?
>Will I have to write my own driver?
>

---

You don't pay to get spam, why pay to clean it?
Visit http://www.spammarshall.com to create an account for free
<http://www.spammarshall.com>|||This does not answer the question. I am not programming a
sql statement. I am setting up a DNS and I am unable to
uncheck the Prepare box when setting up the data source.
But thanks, anyway

quote:

---

>--Original Message--
>This has nothing to do with SQLPrepare. By default MS SQL

---

Server and

quote:

---

>Sybase allow only one statement per connection at a time.

---

It requires

quote:

---

>that you close one statement before you open another one.

---

If you try to

quote:

---

>open another statement, you get this error. You can

---

change this default

quote:

---

>behavior by using Server Side Cursors. Here is some

---

sample code.

quote:

---

>
> nResult = SQLSetStmtAttr(hstmt,

---

SQL_ATTR_CURSOR_TYPE,

quote:

---

ed">
>(SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);
> if(nResult != SQL_SUCCESS && nResult !=

---

SQL_SUCCESS_WITH_INFO){

quote:

---

kred">
> // Handle error
> }
> nResult = SQLSetStmtAttr(hstmt,

---

SQL_ATTR_ROW_ARRAY_SIZE,

quote:

---

rkred">
>(SQLPOINTER)10, 0);
> if(nResult != SQL_SUCCESS && nResult !=

---

SQL_SUCCESS_WITH_INFO){

quote:

---

kred">
> //Handle error
> }
>Hope this helps.
>Pete.
>
>
>anonymous@.discussions.microsoft.com wrote:
>
the[QUOTE]
Does[QUOTE]
>--
>You don't pay to get spam, why pay to clean it?
>Visit http://www.spammarshall.com to create an account

---

for free

quote:

---

><http://www.spammarshall.com>
>
>

---

|||Exactly what option are you referring to? There is an option with the text:
"Create temporary stored procedures for prepared SQL statements and drop the
stored procedures: ...". Why do you want to uncheck this option?
If you are not programming a SQL statement, then how are you getting that
error you reported? ("S1000: [Microsoft][ODBC SQL Server Driver]Connection
is busy with results for another hstmt.")
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:080a01c3be5f$e8847030$a001280a@.phx.gbl...[QUOTE]
> This does not answer the question. I am not programming a
> sql statement. I am setting up a DNS and I am unable to
> uncheck the Prepare box when setting up the data source.
> But thanks, anyway
> Server and
> It requires
> If you try to
> change this default
> sample code.
> SQL_ATTR_CURSOR_TYPE,
> SQL_SUCCESS_WITH_INFO){
> SQL_ATTR_ROW_ARRAY_SIZE,
> SQL_SUCCESS_WITH_INFO){
> the
> Does
> for free|||This is the option I am speaking of, if this option is
unchecked then it will execute directly and not use
prepare and execute. I am getting the error when I use the
dsn connection.

quote:

---

>--Original Message--
>Exactly what option are you referring to? There is an

---

option with the text:

quote:

---

>"Create temporary stored procedures for prepared SQL

---

statements and drop the

quote:

---

>stored procedures: ...". Why do you want to uncheck this

---

option?

quote:

---

>If you are not programming a SQL statement, then how are

---

you getting that

quote:

---

>error you reported? ("S1000: [Microsoft][ODBC SQL Server

---

Driver]Connection

quote:

---

>is busy with results for another hstmt.")
>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
><anonymous@.discussions.microsoft.com> wrote in message
>news:080a01c3be5f$e8847030$a001280a@.phx.gbl...
a[QUOTE]
SQL[QUOTE]
time.[QUOTE]
one.[QUOTE]
am[QUOTE]
to[QUOTE]
it[QUOTE]
>
>.
>

---

|||If you use SQLExecDirect() then the driver will not prepare the statement,
the driver will send the query directly to the server.
That option only applies when you are calling SQLPrepare() and SQLExecute().
As for the error, you are getting the error because you are not consuming
all of the results from an execution, before trying to perform another
execution. The easiest way to ensure you have consumed all of the results
is to call SQLMoreResults() until it returns SQL_NO_DATA. You can create as
many statements per connection as you want, but you need to make sure you
are completely done with a statement, before using another statement that
shares the same connection. If you need to use multiple statements on a
connection at the same time, then you need to use server-side cursors.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:066801c3bf2e$7d1e6290$a001280a@.phx.gbl...[QUOTE]
> This is the option I am speaking of, if this option is
> unchecked then it will execute directly and not use
> prepare and execute. I am getting the error when I use the
> dsn connection.
>
> option with the text:
> statements and drop the
> option?
> you getting that
> Driver]Connection
> confers no rights.
> a
> SQL
> time.
> one.
> am
> to
> it|||I know why I'm getting the error, I cannot get it to go
away. I am using a System DSN connection. This is my only
option, I am a developer stuck in the middle. Lotus LEI
tells me it's an MS problem. I do not have control over
the statements being executed. The only control I have is
with the ODBC Data Source. That option I am refering, if I
uncheck the box then it will use SQLExecDirect and my
problem will go away from what I am told.
I suppose I found a bug with ODBC Data Sources.
Thanks any way

quote:

---

>--Original Message--
>If you use SQLExecDirect() then the driver will not

---

prepare the statement,

quote:

---

>the driver will send the query directly to the server.
>That option only applies when you are calling SQLPrepare

---

() and SQLExecute().

quote:

---

>As for the error, you are getting the error because you

---

are not consuming

quote:

---

>all of the results from an execution, before trying to

---

perform another

quote:

---

>execution. The easiest way to ensure you have consumed

---

all of the results

quote:

---

>is to call SQLMoreResults() until it returns

---

SQL_NO_DATA. You can create as

quote:

---

>many statements per connection as you want, but you need

---

to make sure you

quote:

---

>are completely done with a statement, before using

---

another statement that

quote:

---

>shares the same connection. If you need to use multiple

---

statements on a

quote:

---

>connection at the same time, then you need to use server-

---

side cursors.

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
><anonymous@.discussions.microsoft.com> wrote in message
>news:066801c3bf2e$7d1e6290$a001280a@.phx.gbl...
the[QUOTE]
this[QUOTE]
are[QUOTE]
Server[QUOTE]
programming[QUOTE]
to[QUOTE]
source.[QUOTE]
MS[QUOTE]
and[QUOTE]
getting an[QUOTE]
box[QUOTE]
and[QUOTE]
off.[QUOTE]
turn[QUOTE]
account[QUOTE]
>
>.
>

---

|||That option is for SQL 6.5 servers and older. If you are connecting to a
SQL 6.5 server, then you should have the option to disable it. With SQL 7.0
and newer we use a completely different way to prepare your statements. The
option is disabled, because it is not applicable to newer SQL Servers.
I want to help you, but I need to understand exactly what the problem is
that you are reporting. If you are getting an error that the connection is
busy, then this is a problem with the application that is using the driver.
If you still believe this is a problem with the driver, then you can contact
Microsoft Product Support and they will try to reproduce your problem and
figure out what is going on.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:1531501c3c41c$ac765370$a601280a@.phx
.gbl...[QUOTE]
> I know why I'm getting the error, I cannot get it to go
> away. I am using a System DSN connection. This is my only
> option, I am a developer stuck in the middle. Lotus LEI
> tells me it's an MS problem. I do not have control over
> the statements being executed. The only control I have is
> with the ODBC Data Source. That option I am refering, if I
> uncheck the box then it will use SQLExecDirect and my
> problem will go away from what I am told.
> I suppose I found a bug with ODBC Data Sources.
>
> Thanks any way
>
>
> prepare the statement,
> () and SQLExecute().
> are not consuming
> perform another
> all of the results
> SQL_NO_DATA. You can create as
> to make sure you
> another statement that
> statements on a
> side cursors.
> confers no rights.
> the
> this
> are
> Server
> programming
> to
> source.
> MS
> and
> getting an
> box
> and
> off.
> turn
> account

Help - Rebuild System Databases on a clustered instance

I am in a DR senario and I am having trouble rebuilding the system databases on my SQL 2005 cluster.

I run the following string:

start /wait setup.exe /qn VS=Vname INSTANCENAME=instname REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=password SQLACCOUNT=domain\acct SQLPASSWORD=password AGTACCOUNT=domain\acct AGTPASSWORD=password REINSTALLMODE=vomus

The setup fails with the following error:

Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows Server 2003 family, Service Pack 1 (Build 3790)
Time : Wed Dec 13 15:41:44 2006
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Machine : CLUSTERSVR1
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Error Number : 5897

SQL 2005 was originally installed in May '06 as RTM. SP1 applied in September '06.

I have the original RTM install disk but obviously SP1 is causing rebuild to fail.

Can anyone advise on this please.

D.

Can you apply SP1 and then retry the procedure?

I mean doing the setup of 2005, applying SP1 and then retrying?

|||

JohDas,

Unfortunately I can't.

Don't forget I do not have any databases - system or user. Therefore SP1 cannot authenticate to SQL Server to process the upgrade.

I have a bad feeling that my only option is to uninstall/reinstall SQL 2005.

But this is a last resort and I want to avaoid it if I can.

D.

HELP - Designing a Subscripion System for a Group of Reports

We're developing an ASP.NET 2.0 (C#) application that will act as a
front-end to SQL Server 2005 Reporting Services. We have an interesting
requirement that [we believe] can't be solved using RS out-of-the-box. So,
I'm writing today to get some ideas on possible solutions.
The requirement is that the app. needs to support, what we're calling, a
report group. A report group is simply a group of similar reports that can
be executed as a single unit (instead of having to execute each report
individually). This was not too bad to solve. We simply created our own
database tables that track which report group contains which reports.
The tricky part is that we now have to figure out a way to create a
subscription that executes the report group (thereby executing each
individual report in the report group automatically). Creating a
subscription for a single report is fairly straightforward, but it appears
this concept of a report group is not a part of RS.
Does anyone out there have any good ideas/suggestions on how we can
implement such functionality?
The best thought we have so far is to write another application that manages
the execution of each report in a report group. So, the SQL Server 2005 job
would call this additional little application, this additional app. would
lookup which reports are in the report group, and then it would loop through
all the reports and execute them one-by-one. But, this has some issues-like
how do we handle the output type (PDF, Excel, etc.) and how do we handle the
destination (e-mail, printer, etc.) of each report. Plus, it seems like we
would be somewhat reinventing the wheel as far as subscriptions go.
Thanks.Why aren't you using one master report with all the other reports as
sub-reports?
Tom Bizannes
oiduts wrote:
> We're developing an ASP.NET 2.0 (C#) application that will act as a
> front-end to SQL Server 2005 Reporting Services. We have an interesting
> requirement that [we believe] can't be solved using RS out-of-the-box. So,
> I'm writing today to get some ideas on possible solutions.
> The requirement is that the app. needs to support, what we're calling, a
> report group. A report group is simply a group of similar reports that can
> be executed as a single unit (instead of having to execute each report
> individually). This was not too bad to solve. We simply created our own
> database tables that track which report group contains which reports.
> The tricky part is that we now have to figure out a way to create a
> subscription that executes the report group (thereby executing each
> individual report in the report group automatically). Creating a
> subscription for a single report is fairly straightforward, but it appears
> this concept of a report group is not a part of RS.
> Does anyone out there have any good ideas/suggestions on how we can
> implement such functionality?
> The best thought we have so far is to write another application that manages
> the execution of each report in a report group. So, the SQL Server 2005 job
> would call this additional little application, this additional app. would
> lookup which reports are in the report group, and then it would loop through
> all the reports and execute them one-by-one. But, this has some issues-like
> how do we handle the output type (PDF, Excel, etc.) and how do we handle the
> destination (e-mail, printer, etc.) of each report. Plus, it seems like we
> would be somewhat reinventing the wheel as far as subscriptions go.
> Thanks.|||You could try setting up a shared schedule. It would still require
individual subscriptions for each report but they would all start
firing at the same scheduled time.

Help

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this queryYou can get the total number of people who logged in by doing something like:
SELECT COUNT(*) FROM LoginTable WHERE LoginDate = '2003/09/15'

I'm not sure what you mean by (2), (3), and (4). Do you mean the mininum number of people who were logged in simultaneously at a given moment in time during the day in question? (etc)

Cheers
Ken|||Thanks mate .
I will try to explain

DataBase fields are

id
date

So when a user is logged in I add a line to this table (id is identity field).

I am creating a report to show how many users logged on
between two dates (start date and end date (between date) ).

I will provide two Dates text boxes for the report i.e
Start Date and End Date and then search button
so for example if

say on these dates the logged users are as
On Date 01/08/2003 Logged users are 10
On Date 02/08/2003 Logged users are 8
On Date 03/08/2003 Logged users are 3
On Date 04/08/2003 Logged users are 5

So query should return
Count of user-->26 on these dates
Minimum users-->3 on these dates
Maximum users-->10
Average users-->sum of users/Count of users

I hope this makes some sense.