can not drop user from database

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Can not Backups on SQL2005

Can not Backups.No Error!

The Code:

Code Snippet

static void Main(string[] args)
{
try
{
SqlConnection Connection = new SqlConnection(@."Data Source=SUPER-9ZT4OE2OQ;Initial Catalog=DataBase;Persist Security Info=True;User ID=tj;password=tj");
Server server = new Server(new ServerConnection(Connection));
Backup bak = new Backup();
bak.Action = BackupActionType.Database;
bak.Database = "DataBase";

BackupDevice backupDevice = new BackupDevice(server, "Full_Backup");
backupDevice.PhysicalLocation = @."\\192.168.2.13\DatabaseBak\over.remoteBak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;

bak.Devices.AddDevice("Full_Backup", DeviceType.File);
bak.Incremental = false;
bak.SqlBackup(server);

}
catch (Exception)
{

throw;
}
}

Any help is highly appreciated

Thanks.

Try changing the line

bak.Devices.AddDevice(xxx)

to

bak.Devices.Add(xxx)

Here's the example (in VB.Net) from BOL:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Backup object variable.
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.Database = "AdventureWorks"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)

Can not Backups on SQL2005

Can not Backups.No Error!

The Code:

Code Snippet

static void Main(string[] args)
{
try
{
SqlConnection Connection = new SqlConnection(@."Data Source=SUPER-9ZT4OE2OQ;Initial Catalog=DataBase;Persist Security Info=True;User ID=tj;password=tj");
Server server = new Server(new ServerConnection(Connection));
Backup bak = new Backup();
bak.Action = BackupActionType.Database;
bak.Database = "DataBase";

BackupDevice backupDevice = new BackupDevice(server, "Full_Backup");
backupDevice.PhysicalLocation = @."\\192.168.2.13\DatabaseBak\over.remoteBak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;

bak.Devices.AddDevice("Full_Backup", DeviceType.File);
bak.Incremental = false;
bak.SqlBackup(server);

}
catch (Exception)
{

throw;
}
}

Any help is highly appreciated

Thanks.

Try changing the line

bak.Devices.AddDevice(xxx)

to

bak.Devices.Add(xxx)

Here's the example (in VB.Net) from BOL:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Backup object variable.
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.Database = "AdventureWorks"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)

Can multiple updates in a transaction interfere?

I have some code like:
begin transaction
update mytable
set a = x.a
from mytable m inner join xtable x on m.id = x.id
where m.a <> x.a
update mytable
set b = x.b
from mytable m inner join xtable x on m.id = x.id
where m.b <> x.b
...
commit transaction
It runs without error, but afterwards there were a few rows where a
should have been updated and was not, and a few were b should have
been updated and was not.
I am embarrased to say I did not check to see if any of these were
obviously the same rows.
But I *thought* that the code above should work cleanly, and if it did
not it would hang or complain or something, not just misfire quietly,
if indeed it did misfire.
SQL2000 sp2, fwiw.
No, I don't have to run it quite this way, or in an overall tranaction
at all, I suppose, I just though it nice and harmless. Should it have
been?
Thanks.
Joshjxstern wrote:
> I have some code like:
> begin transaction
> update mytable
> set a = x.a
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
> update mytable
> set b = x.b
> from mytable m inner join xtable x on m.id = x.id
> where m.b <> x.b
> ...
> commit transaction
>
> It runs without error, but afterwards there were a few rows where a
> should have been updated and was not, and a few were b should have
> been updated and was not.
> I am embarrased to say I did not check to see if any of these were
> obviously the same rows.
> But I *thought* that the code above should work cleanly, and if it did
> not it would hang or complain or something, not just misfire quietly,
> if indeed it did misfire.
> SQL2000 sp2, fwiw.
> No, I don't have to run it quite this way, or in an overall tranaction
> at all, I suppose, I just though it nice and harmless. Should it have
> been?
> Thanks.
> Josh
Doing these updates within a single transaction would not have allowed
some records to update but not others. A conflict in a transaction
would manifest as some sort of blocking, resulting in an error.
A "trick" that you can use to test updates like this is to not COMMIT,
but ROLLBACK. Immediately before the ROLLBACK, do a select against the
affected records to make sure the changes you expect were actually made.
Don't commit until you are sure the update is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C6A1F0.A88836C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If the code below is your actual code, then you are not verifying =successful completion of each of the UPDATE queries. It is necessary to =verify each step and execute a ROLLBACK if there is a problem. And this =code will not have an @.@.Error if there are no rows that meet the =criterai to update. If it is important to abort if no rows are found, =then this has to be modified. (In SQL 2005, there is a more robust =structured error handling capability that doesn't require the constant =checking.)
Transaction code should be more like:
BEGIN TRANSACTION
UPDATE MyTable
SET a =3D x.a
FROM MyTable m
JOIN xTable x
ON m.ID =3D x.ID
WHERE m.a <> x.a
IF @.@.Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
UPDATE MyTable
SET b =3D x.b
FROM MyTable m
JOIN xTable x
ON x.ID =3D m.ID
WHERE m.b <> x.b
IF @.@.Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
END TRANSACTION
You may be able to revise the query to UPDATE in a single query.
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"jxstern" <jxstern@.wherever.com> wrote in message =news:530ua21cafjbodm71j0f7pht79luofu1p8@.4ax.com...
>I have some code like:
> > begin transaction
> > update mytable
> set a =3D x.a
> from mytable m inner join xtable x on m.id =3D x.id
> where m.a <> x.a > > update mytable
> set b =3D x.b
> from mytable m inner join xtable x on m.id =3D x.id
> where m.b <> x.b > > ...
> > commit transaction
> > > It runs without error, but afterwards there were a few rows where a
> should have been updated and was not, and a few were b should have
> been updated and was not.
> > I am embarrased to say I did not check to see if any of these were
> obviously the same rows.
> > But I *thought* that the code above should work cleanly, and if it did
> not it would hang or complain or something, not just misfire quietly,
> if indeed it did misfire.
> > SQL2000 sp2, fwiw.
> > No, I don't have to run it quite this way, or in an overall tranaction
> at all, I suppose, I just though it nice and harmless. Should it have
> been?
> > Thanks.
> > Josh
--=_NextPart_000_0110_01C6A1F0.A88836C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If the code below is your actual code, =then you are not verifying successful completion of each of the UPDATE queries. It is =necessary to verify each step and execute a ROLLBACK if there is a problem. And this code will not have an @.@.Error if there are no =rows that meet the criterai to update. If it is important to abort if no rows are =found, then this has to be modified. (In SQL 2005, there is a more robust =structured error handling capability that doesn't require the constant checking.)
Transaction code should be more =like:
BEGIN TRANSACTION
UPDATE =MyTable
=SET a =3D x.a
=FROM MyTable m
JOIN xTable x
&nbs=p; ON m.ID =3D x.ID
=WHERE m.a x.a
IF @.@.Error = 0
BEGIN
ROLLBACK TRANSACTION
=RETURN
END
UPDATE =MyTable
=SET b =3D x.b
=FROM MyTable m
JOIN xTable x
&nbs=p; ON x.ID =3D m.ID
=WHERE m.b x.b
IF @.@.Error = 0
BEGIN
=ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
END TRANSACTION
You may be able to revise the query to UPDATE in =a single query.
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"jxstern" =wrote in message news:530ua21cafjbodm71j0f7pht79luofu1p8@.4ax.com...>I =have some code like:> > begin transaction> > update mytable> set a =3D x.a> from mytable m inner join =xtable x on m.id =3D x.id> where m.a x.a > > update mytable> set b =3D x.b> from mytable m inner join =xtable x on m.id =3D x.id> where m.b x.b > > =...> > commit transaction> > > It runs without =error, but afterwards there were a few rows where a> should have been =updated and was not, and a few were b should have> been updated and was =not.> > I am embarrased to say I did not check to see if any of these were> obviously the same rows.> > But I *thought* =that the code above should work cleanly, and if it did> not it would hang =or complain or something, not just misfire quietly,> if indeed it =did misfire.> > SQL2000 sp2, fwiw.> > No, I =don't have to run it quite this way, or in an overall tranaction> at all, I =suppose, I just though it nice and harmless. Should it have> =been?> > Thanks.> > Josh

---

--=_NextPart_000_0110_01C6A1F0.A88836C0--|||On Fri, 7 Jul 2006 18:10:41 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>If the code below is your actual code, then you are not
>verifying successful completion of each of the UPDATE queries.
Thanks you for your concern!
Actual code uses
select @.myerr = @.@.error, @.mycnt = @.@.rowcount
after each update, does test @.myerr and rollback if anything is wrong,
why else use a transaction? But that part works fine, the mystery is
why some rows seem to be missed in the completed and apparently
error-free transaction.
Josh|||On Fri, 07 Jul 2006 19:59:27 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Doing these updates within a single transaction would not have allowed
>some records to update but not others. A conflict in a transaction
>would manifest as some sort of blocking, resulting in an error.
That's what I'd expect.
>A "trick" that you can use to test updates like this is to not COMMIT,
>but ROLLBACK. Immediately before the ROLLBACK, do a select against the
>affected records to make sure the changes you expect were actually made.
> Don't commit until you are sure the update is correct.
Oh, it's much worse than that!
:)
I actually run a pre-check report on what needs to be fixed, so I
already have a count of records that the same logic picks out as a
pure select, so if I'm running it manually, I can see right away that
the count is wrong. And when I run it as a script, I have print
statements (nicer than nocount off!) that reports the number of rows
affected by each statement, that I can compare to the pre-check. And
afterwards I run a post-check that should report zero - and doesn't.
But freakily enough, simply rerunning the same script as second time,
seemed to pick up the missed rows.
(worse yet, I actually ran the same script against three related
databases, and it worked 100% on the first two, it was only the third
database where it acted weird)
It's as if someone wrote some new rows in immediately after the update
script ran, but nobody was, and indeed the rows have create date and
modified date fields, the later kept by trigger, and they haven't been
touched in a month.
--
But I gather from both responses so far that at least I haven't
overlooked some basic kind of known interference (feature or bug) that
might allow some rows to be skipped in this kind of code structure.
Thanks for the reality checks.
Josh|||JXStern wrote:
> On Fri, 07 Jul 2006 19:59:27 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> Doing these updates within a single transaction would not have allowed
>> some records to update but not others. A conflict in a transaction
>> would manifest as some sort of blocking, resulting in an error.
> That's what I'd expect.
>> A "trick" that you can use to test updates like this is to not COMMIT,
>> but ROLLBACK. Immediately before the ROLLBACK, do a select against the
>> affected records to make sure the changes you expect were actually made.
>> Don't commit until you are sure the update is correct.
> Oh, it's much worse than that!
> :)
> I actually run a pre-check report on what needs to be fixed, so I
> already have a count of records that the same logic picks out as a
> pure select, so if I'm running it manually, I can see right away that
> the count is wrong. And when I run it as a script, I have print
> statements (nicer than nocount off!) that reports the number of rows
> affected by each statement, that I can compare to the pre-check. And
> afterwards I run a post-check that should report zero - and doesn't.
> But freakily enough, simply rerunning the same script as second time,
> seemed to pick up the missed rows.
> (worse yet, I actually ran the same script against three related
> databases, and it worked 100% on the first two, it was only the third
> database where it acted weird)
> It's as if someone wrote some new rows in immediately after the update
> script ran, but nobody was, and indeed the rows have create date and
> modified date fields, the later kept by trigger, and they haven't been
> touched in a month.
> --
> But I gather from both responses so far that at least I haven't
> overlooked some basic kind of known interference (feature or bug) that
> might allow some rows to be skipped in this kind of code structure.
> Thanks for the reality checks.
> Josh
>
Could you possibly have dupes that are confusing things? Just guessing...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||jxstern wrote:
> I have some code like:
> begin transaction
> update mytable
> set a = x.a
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
> update mytable
> set b = x.b
> from mytable m inner join xtable x on m.id = x.id
> where m.b <> x.b
>
Josh,
I would first of all combine these 2 updates into one:
update mytable
set a = case when m.a <> x.a then x.a else m.a end,
b = case when m.b <> x.b then x.b else m.b end
from mytable m inner join xtable x on m.id = x.id
where m.a <> x.a
This under most circumstances performs better.
Next, because you did not post DDL, I can only make a wild guess.
Suppose m.a is null and x.a = 12. Note that m.a <> x.a is FALSE, and
m.a will not get updated. If m.a and x.a are nullable, instead of m.a
<> x.a you should use:
(m.a <> x.a ) or (m.a is null and x.a is not null) or (m.a is not null
and x.a is null)|||On 8 Jul 2006 13:16:25 -0700, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:
>Josh,
>I would first of all combine these 2 updates into one:
>update mytable
> set a = case when m.a <> x.a then x.a else m.a end,
> b = case when m.b <> x.b then x.b else m.b end
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
>This under most circumstances performs better.
>Next, because you did not post DDL, I can only make a wild guess.
>Suppose m.a is null and x.a = 12. Note that m.a <> x.a is FALSE, and
>m.a will not get updated. If m.a and x.a are nullable, instead of m.a
><> x.a you should use:
>(m.a <> x.a ) or (m.a is null and x.a is not null) or (m.a is not null
>and x.a is null)
The actual code is rather more complicated (!), sometimes it does
involve nulls but I take care of those properly, and there would be a
lot of tables involved in different joins for the different cases, so
I'm guessing it might not be all that much faster due to caching and
such. It's a batch process that runs only on special occassions, so
efficiency isn't the major concern, anyway. FWIW, takes about twenty
minutes on the dev box for six to ten update statements, probably run
2x faster on the production box with more processors and more RAM.
(actually, I though I should probably break up the transaction anyway
to make the script more production-friendly, now that you mention it!)
But I remain mystified by the behavior seen.
Josh|||On Sat, 08 Jul 2006 13:06:30 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Could you possibly have dupes that are confusing things? Just guessing...
It's a thought, I know eliminate-dups scripts act like that, don't
they? The only way that would happen here is if I failed to fully
define my joins ... hey, that's something to look at on Monday,
thanks!
Josh

Can I write a dll(or share the same code) that works on both Mobile device and pc?

hi,

Can I write a dll(or share the same code) that works on both Mobile device and pc? Since we are using compact edition, we are hoping we can write some common module with the same code that could works on both mobile device and pc platform. I noticed the reference is the same, System.Data.SqlServerCe.dll 3.0.

Another question is, we already have a module for SQLCE2.0, with .net CF1.0. Now, we will start to use compact edition, should we just update on the 1.0 one, or we have to write a different one based on .net CF2.0? Can I use compact edition in CF1.0 dll?

Thanks.

The code in terms of SQL CE is exactly the same, apart from the connection string. So yes, you can easily share code, you will have to use 2 different solutions to target the different platforms, however. SQL CE/SQL Mobile is only available in Visual Studio 2005.

Can i use the OleDbDataAdapter with SQL SERVER EXPRESS

i have a large amount of code to convert (over 100 asp ver. 2 pages)

Microsoft Access to SQL SERVER EXPRESS

the original code uses the OleDbDataAdapter

example: =========

Dim Connect As OleDbConnection = New OleDbConnection
Dim MyAdapter As OleDbDataAdapter = New OleDbDataAdapter
Dim MyCmdbuilder As OleDbCommandBuilder
Dim Mydataset As DataSet = New DataSet
Dim SelectStatement as string = "select * from tbl_weblog"
Dim connectString as string = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\web\users\data\abc.mdb"

Connect.ConnectionString = ConnectString
MyAdapter.SelectCommand = new OleDbCommand(SelectStatement, Connect)
MyCmdbuilder = New OleDbCommandBuilder(MyAdapter)
MyAdapter.Fill(Mydataset,"tbl_weblog")

================

Can i just change the connection string to point to the new database, assuming the field names and structures are all the same.

Yes, you can, but it's not optimal. The native SqlClient is much better. You could just replace every occurance (search & replace in VS) of "OleDb" with "Sql", and take it from there.

|||

Thanks for the reply

Are there big differences between the Oledb and SQL Client?

You say "much better", in what areas?

reliabilty, scalability or performance?

Does anyone know where i could research this further?

|||

Sorry for being too brief...

The performance difference can be huge in some occasions, and you get nice features such as named parameters.

can i use single function for a big project ??...

Hi,

I wnat to write a single function in code behind file for all DML operation, and i just want to pass query string in its parameter. but when ever my whole project will use this function to insert, update and delete. what will be the performance.

bool DMLoperation(string str){obj.conn =new SqlConnection(obj.connstring);obj.cmd =new SqlCommand(str,obj.conn);obj.conn.Open();try{if(obj.cmd.ExecuteNonQuery() > 0){obj.conn.Close();return true;}else{obj.conn.Close();return false;}}catch{obj.conn.Close();return false;}}

I am totally confused about performance, is it good idea or worst ?.

Are you going to create a SQL manager for your project?
seems it's better for you to overload SQL object other then just pass-in the sql string and execute (just my opinion)

What i can only share is my company did the same things, ie. build a customized SQL manager for SQL execution,
although performace is a key issue, you should also consider other benfit that come from this design; such as you
can run extra SQL within each SQL call, such as SQL logging;

if you build the Manager in clean and well design, i believe performance is not a problem.

Hope this help

|||

My dear, can you send some snippet of code, to use by overloading SQL object, It may be help me

Can I use IIF statement in the RS query?

I tried to insert this line of code to the Column in the query design but it
doesn't work. Similar command works in MS-ACCESS.
IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE), 0)
Can someone help, thanks in advance.You can put this kind of formula in the textbox that displays the data. The
query design is limited to the back-end functionality, and Access has some
extra VB capabilities most databases don't support. But the reporting front
end does support this.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"LV" <LV@.discussions.microsoft.com> wrote in message
news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
>I tried to insert this line of code to the Column in the query design but
>it
> doesn't work. Similar command works in MS-ACCESS.
> IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> 0)
> Can someone help, thanks in advance.|||In addition to what Jeff says. What database are you going against? If it is
against Access MDB then you might be able to use the generic query window
(versus the graphical). This is basically passthrough window. It depends on
how the Access OLEDB provider handles it. If it is against SQL Server data
then this will definitely not work since it is not SQL Server SQL format. If
going against SQL Server then you can always test out your SQL using the
Query Analyzer.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> You can put this kind of formula in the textbox that displays the data.
The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting
front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>|||Thank you,
Yes I am using Access data, as you suggested I will try the textbox method.
"Jeff A. Stucker" wrote:
> You can put this kind of formula in the textbox that displays the data. The
> query design is limited to the back-end functionality, and Access has some
> extra VB capabilities most databases don't support. But the reporting front
> end does support this.
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "LV" <LV@.discussions.microsoft.com> wrote in message
> news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> >I tried to insert this line of code to the Column in the query design but
> >it
> > doesn't work. Similar command works in MS-ACCESS.
> >
> > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate, GETDATE),
> > 0)
> >
> > Can someone help, thanks in advance.
>
>|||Thank you,
I did try the generic SQL but did not get the correct results somehow it
return just he true side of the IIF statement. Here is how I get around my
problem, I created the query with the IIF statemet in Access, on RS report I
connect to the query and it works. I don't know if this is the correct way
to do it but for now at least I can get it to work.
"Bruce L-C [MVP]" wrote:
> In addition to what Jeff says. What database are you going against? If it is
> against Access MDB then you might be able to use the generic query window
> (versus the graphical). This is basically passthrough window. It depends on
> how the Access OLEDB provider handles it. If it is against SQL Server data
> then this will definitely not work since it is not SQL Server SQL format. If
> going against SQL Server then you can always test out your SQL using the
> Query Analyzer.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eyBkf%2391EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > You can put this kind of formula in the textbox that displays the data.
> The
> > query design is limited to the back-end functionality, and Access has some
> > extra VB capabilities most databases don't support. But the reporting
> front
> > end does support this.
> >
> > Cheers,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "LV" <LV@.discussions.microsoft.com> wrote in message
> > news:3210CAF1-91B7-401F-B1EF-E6B1D29161FC@.microsoft.com...
> > >I tried to insert this line of code to the Column in the query design but
> > >it
> > > doesn't work. Similar command works in MS-ACCESS.
> > >
> > > IIf('[Policy Received?]=No', DateDiff('y', tblFileInfo.EffDate,
> GETDATE),
> > > 0)
> > >
> > > Can someone help, thanks in advance.
> >
> >
>
>

can i use "translate" in sql server,is it a keyword

im trying to find the equivalent of the below oracle code in sql server.But to my knowledge i feel "translate "is not supported by sql server. so please send me the equivalent sql server query for this.

SELECT 'http://ploft.com/ploft?y='||translate(comp,' ','+')||',+'||translate(california,'
','+')||',+'||ca||'+'||pz
INTO :ls_MAP FROM project WHERE portno=:dbno;I believe the REPLACE function is what you are after in SQL Server.|||thank you crowley

how to use 'replace' function to change the characters in a given column.In oracle we can do that (its in above query)|||http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_76lh.asp?frame=true

replace (query, 'TRANSLATE', 'REPLACE')

Can I set expressions programmatically?

I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.

For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:

"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @.[User:omeStringVariable] + "')"

What would the code look like?

TaskHost has a SetExpression method:

TaskHost.SetExpression Method

http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.taskhost.setexpression.aspx

I presume all of the other container types (Package, Sequence etc..) will do too.

-Jamie

|||AAAGGGGHHH!! I was expecting a property, not a method! So then in order to retrieve all expressions on a task, you'd have to iterate the Properties collection and call GetExpression() with the name of each one to see if it's non-null. Doesn't seem like a particularly good design to me. Why not a simple collection? Oh well, thanks for the help.|||

JeffJohnsonMVPVB wrote:

AAAGGGGHHH!! I was expecting a property, not a method! So then in order to retrieve all expressions on a task, you'd have to iterate the Properties collection and call GetExpression() with the name of each one to see if it's non-null. Doesn't seem like a particularly good design to me. Why not a simple collection? Oh well, thanks for the help.

Yep. I was expecting a Collection as well.

Maybe a read-only collection would be nice that is populated by SetExpression(). Are there such things as read-only collections? I've no idea - I'm no developer.

I too would be interested in seeing the rationale for this. I hope someone from MSFT chimes in.

Regards

-Jamie

Can i Send query string to reporting services 2000 ?

I would like to build the query on my code behind and send it as is

not to send params and use a build query on the R.S

Can i do it? how can i do it?

Hi,

what do you mean by "build query" ? If you just want to open a report you can go with the URL Access syntax which can be found in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I Mean that i build my quey on run time

the user picks something and by that i decide which query to build

this is the reason i want to send the whole query and not only params

p.s

i use sql 2000 not 2005

|||

Hi,

you would probably use a procedure then to redirect the flow to a specifc query.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Reporting Services 2000 allows the usage of an expression-based command text. However, keep in mind that once you use an expression you can no longer directly execute the query in the query designer. I recommend to first build the expression in a report textbox and try it with various parameter combinations to visually verify the generated queries are correct and then as a last step replace the original command text with an expression, such as
="select a, b from table1"

Also make sure that if you use string concatenation to build your query based on parameter values, you have to very careful to prevent SQL injection attacks in your query.

Alternatively to an expression-based command text, you could use a parameterized query to avoid the risk of SQL injections (e.g. select a, b from table1 where country = @.Country)

-- Robert

can I read some rows from the middle of rows in DataReader?

helo..
I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader.
I wrote this code to do this but its too slow:

Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'"

Dim ReadCom As New SqlClient.SqlCommand

ReadCom.CommandText = SlctStr

ReadCom.Connection = MainLib.MyConnection

Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader()

Dim StartTNum As Long = 5000

For IR As Long = 0 To StartTNum - 1

MyReader.Read()

Next

Do While MyReader.Read

StartTNum += 1

If StartTNum > 5500 Then Exit Do

'''''''''''''''''''

Loop

MyReader.Close()

is there another way to do the same thing better off than this code?

If you are using SQL 2005, use Row_Number() to create rownumbering, and then in your query, add the WHERE clause:

WHERE MyRowNumber BETWEEN 5000 AND 5050

|||thank you..

Can I pass '=' or '>' as parameters?

Hi
I'm moving SQL in my app to Stored Procedures.

My SQL string is currently created within my app, and I have two check boxes and this C# code...

if(chkPartTime.Checked&&!chkFullTime.Checked) { sql+=" AND P0_Hours = 0"; }

if(!chkPartTime.Checked&&chkFullTime.Checked) { sql+=" AND P0_Hours > 0"; }

where sql is my query string

If both boxes are checked I don't care what value P0_Hours is, but at least one of them will be checked.

What I want to know is how I transfer such logic into a Stored Procedure, or whether I will need to creat 2 similar sp's and choose which to call in my app.

It will combined with a query (currently 60 lines) comprising of 3 UNIONs

TIA

Sure why not...

you could literally pass a char(1) of '>' or '=' and use it to build the sql string

However wouldn't it be better to pass 'bit' fields like this:

SP paremeters:

@.chkPartTime bit

@.chkFullTime bit

then in the code:

in the Where clause:

Code Snippet

and ((p0_hours = 0 and @.chkPartTime = 1 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1 and @.chkPartTime = 0))

or better yet... just have one parameter:

@.chkFullTime bit and then 0 = Parttime 1 = FullTime

so then the code would look like:

Code Snippet

and ((p0_hours = 0 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1))

-Robert

|||

use the flag parameter (new param) & pass the appropriate flag value to the sp.

for example on your code,

Code Snippet

int iFlag = 0;

if(chkPartTime.Checked && !chkFullTime.Checked) { iFlag = 1; }

if(!chkPartTime.Checked && chkFullTime.Checked) { iFlag = 2; }

//Pass this flag variable as parameter while calling the SP

on your SP,

Check the flag using IF condition are on where condition to get the required result.

Code Snippet

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

@.Flag = 0

Or

(@.Flag = 1 And P0_Hours =0)

Or

(@.Flag = 2 And P0_Hours >0)

--or

If @.Flag = 0

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

If @.Flag = 1

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours =0

If @.Flag = 2

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours > 0

|||

I don't know, I usually would just suggest a dynamic SQL stored procedure in these cases, particularly if there is any real heft to your data. The other solutions suggested are a good possibilities too, but the complexities of coding tend to turn things like this into a nightmare both to maintain, and to get a decent query plan anyhow.

Louis

|||

Thank you all

It was difficult to choose which to mark as the answer!

I had not heard of dynamic sp's (I come from a DB2 background, now using SQL Server) and I have learnt from all 3 answers, still not made my mind up which way to go, but thanks.

Can I make this code better using SQLParamater object?

I wrote a class to handle my sqlDataReaders:

Namespace CommonFunctions

PublicClass DataAccess

PublicSubNew()

EndSub

Private ConnStrAsString ="connectionString"

PublicFunction returnDR(ByVal strSQLAsString)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)

Dim myCmdAsNew SqlClient.SqlCommand(strSQL, myCn)

myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

EndClass' Public Class DataAccess

EndNamespace' Namespace CommonFunction

The way I invoke this is:

Imports CommonFunctions

Dim daAsNew DataAccess

Dim sqlDataReaderAs SqlDataReader

Dim strSQLAsNew StringBuilder

strSQL.Append("Some SQL Query")

sqlDataReader = da.returnDR(strSQL.ToString())

DoWhile sqlDataReader.Read()

strVar = sqlDataReader("columnName")

Loop' Do While sqlDataReader.Read()

sqlDataReader.Close()

What I would like to do, is use the SQL Paramater Object instead of passing my method a String. One so that I dont have to explicitly test for SQL Injection and two, cause I have never worked with the SQL Paramater Object before. :)

J

Sure, put this in your class:

PublicFunction returnDR(ByVal cmd AS SqlCommand)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)
mycmd.connection=myCn
myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

Then you can pass returnDR either a SQLString, or a SqlCommand object.

like this:

dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE ID=@.ID")
cmd.parameters.add("@.ID",sqdbtype.int).value={Some ID}
sqlDataReader = da.returnDR(cmd)

and of course:
sqlDataReader=da.returnDR("SELECT * FROM MyTable WHERE ID=" & {Some ID})

will continue to work as well. That way you can move forward with using SqlCommand's for new queries, and covert the old queries when you have time.

You can then also change your old function to:

PublicFunction returnDR(ByVal sqlcmd AS String)As SqlClient.SqlDataReader
dim cmd as new sqlCommand(sqlcmd)
return returnDR(cmd)
End Function

Can I Join Table Variables? It doesnt seem to compile

I want to join two (or more) table variables

DECLARE @.A TABLE (x int...)
DECLARE @.B TABLE (y int...)

INSERT INTO @.A

INSERT INTO @.B

--this code doesnt work
SELECT * FROM
@.A LEFT JOIN @.B
ON @.A.x = @.B.y

Are these things even possible with variable tables?

thanks.USE Northwind
GO

DECLARE @.A TABLE (x int IDENTITY(1,1), z char(1) )
DECLARE @.B TABLE (y int IDENTITY(1,1), z char(1))

INSERT INTO @.A(z)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'

INSERT INTO @.B(z)
SELECT 'W' UNION ALL
SELECT 'X' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'Z'

SELECT *
FROM @.A a LEFT JOIN @.B b
ON a.x = b.y

...pronoun trouble...B.B.|||You must use aliases when referencing table variables. Hence Brett's use of:

SELECT *
FROM @.A a LEFT JOIN @.B b
ON a.x = b.y

where this would fail:

SELECT *
FROM @.A LEFT JOIN @.B
ON @.A.x = @.B.y

blindman