copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, che
ck out some of the tools
at http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.
gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need t
o
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

copy of existing table and data

My query is very simple, I am new to SQL.
I want to create copy of existing table and data.
Pls suggest a command !
Thanks in advance
SanjayEverything in the database? If so, I suggest backup and restore. If not, check out some of the tools
at http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>|||Thanks for the prompt response.
I think, i have failed to pass on my message.
I want to create a new table using existing table with its structure and
records.
Sanjay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYKfdypeHHA.4300@.TK2MSFTNGP02.phx.gbl...
> Everything in the database? If so, I suggest backup and restore. If not,
> check out some of the tools at
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>> My query is very simple, I am new to SQL.
>> I want to create copy of existing table and data.
>> Pls suggest a command !
>> Thanks in advance
>> Sanjay
>|||Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:
> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> > My query is very simple, I am new to SQL.
> >
> > I want to create copy of existing table and data.
> > Pls suggest a command !
> >
> > Thanks in advance
> > Sanjay
> >
>
>

Copy Date Dimension

Hello Guys and Galls,

I have a fact table with two different keys referring to the same date dimension table. In a query you would use table aliassing to join the date dimension twice.

In AS I created a DSV with two aliasses of the date dimension table. Lets give um names:
- dim_ActivityDate
- dim_OrderDate

Now, I have created a dimension "Activity Date", based on the dim_ActivityDate dimension. I want to create a second dimension, "Order Date", which is a copy of the "Activity Date" dimension, but based on the dim_OrderDate alias from the DSV.

Is there an easy way to do this, or do I have to create the whole dimension using the wizard again?

Regards, Jeroen

Hi Jeroen,

No, you don't need to create two separate physical dimensions. You just need to create one dimension and add it to the cube more than once - a 'role playing dimension'. In Visual Studio just double click on your cube to edit it and assuming you have the dimension already added to the cube once, you just need to right-click on Dimensions box in the bottom-right-hand corner of the 'Cube Structure' tab and select 'Add Cube Dimension'. Add the date dimension again and you'll find that it gets added with a new name, which you can change to Order Date, and then if you go to the Dimension Usage tab you'll find you can join it to your measure group on the dim_OrderDate key column.

HTH,

Chris

|||Splendid! Thnx Chris.

copy datasets between reports

i have a lil query that gets the values for my parameters..
what is the easiest way to copy this whole dataset into another report?
I wish that there were 'shared datasets' so that we could reuse business
logici just ended up opening the XML and cutting and pasting..
it didn't work correctly.. said that the 'aaron' datasource wasn't available
(it is a shared datasource, so that confused me)
I just ended up doing it by hand.. it would just be a LOT nicer to be able
to copy datasets without going into the source
-aaron
<aaron_kempf@.hotmail.com> wrote in message
news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> i have a lil query that gets the values for my parameters..
> what is the easiest way to copy this whole dataset into another report?
> I wish that there were 'shared datasets' so that we could reuse business
> logic
>
>|||Thanks for the suggestion.
The new report controls in the upcoming VS2005 Beta 2 will just bind to
ADO.NET datasets, which would provide a client side solution to "sharing"
datasets. Shared datasets on the server side are on the wishlist for a
future release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<aaron_kempf@.hotmail.com> wrote in message
news:ekPLfWv5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> i just ended up opening the XML and cutting and pasting..
> it didn't work correctly.. said that the 'aaron' datasource wasn't
available
> (it is a shared datasource, so that confused me)
> I just ended up doing it by hand.. it would just be a LOT nicer to be able
> to copy datasets without going into the source
> -aaron
>
> <aaron_kempf@.hotmail.com> wrote in message
> news:%2378CAEv5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > i have a lil query that gets the values for my parameters..
> >
> > what is the easiest way to copy this whole dataset into another report?
> >
> > I wish that there were 'shared datasets' so that we could reuse business
> > logic
> >
> >
> >
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.
Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scri...tions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegr oups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegroups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
--
http://msdn.microsoft.com/library/d...r />
_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scr...utions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegroups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/d.../>
_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

copy data from one table to another with addition insert value

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

If you need the ID of the first insert, for the second query then you cannot do a batch insert. There are ways to be a little creative such as doing the 2 inserts separately and doing a batch update on the second query. It depends on your data though.

|||

Thanks Dinakar,

Yes i need the id of the first insert for the second query.

As I need to alter one field value in the select data I am copying from do you think I will need to do a batch update after the second query. Is there no way of doing the insert,select and batch update all on the second query?

Many thanks

Mark :)

|||

markbpriv:

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

Do your first insert, then get the identity, then do your second insert.

Example:

declare myIdentityintINSERT INTO Table1 (col1, col2)VALUES ('test1','test2')SET @.myIdentity =@.@.IDENTITYINSERT INTO Table2SELECT myIdentity ,'test','another test'

Good luck.

|||

There is a way you can trick the SQL Server. Do you have any column in the first table that has any column which has a column like "userid" or "username" that you hardcode from your source data? or any column that has a fixed value across all rows?

|||

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

|||

markbpriv:

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

Please chekout the seconf post here:http://www.aspspider.com/rss/Rss19894.aspx

If not help:
Make sure the stored procedure is exists in your database.
Execute the SP with the owner name: MyName.MyStoredProcedureName

I guess, you are concatenating a value to a SELECT statment maybe and that value is getting you False.

I found this link which support my guess:http://p2p.wrox.com/topic.asp?TOPIC_ID=1773

Please let me know if this help you or not.

Good luck.

Copy Data from one database to another

is there any query to copy data from one database to another replacing all data in the second database

There is not a single query that will move the data from one database to another. If the databases are identically constructed, you could create a script that truncated each table and then performed a Select * from matching tables in the two databases. This can be difficult if you have used identity columns and properly implemented referential integrity.

The Data import wizard (Sql 2000) and comparable tools will move make a copy of a database.

You can also use a backup and restore.

|||

Have you looked into any of the following solutions...

SQL Server Integration Services? - http://msdn.microsoft.com/sql/bi/integration/
Copy Database Wizard (will be vastly improved in SQL Server 2005 SP2) - http://msdn2.microsoft.com/en-us/library/ms188664.aspx
Backup and Restore?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

copy data from 2 tables

I need to copy the data in the last name table to the field2 table do i use the update query in sql 2005?I don't really know enough about your problem to answer your question. Depending on the table structure and the tools you are using the answer is probably either yes or no.

I'm going to move your question to the SQL Server forum, since that is the database engine that you're using. See the FAQ at the top of the forum postings for the entry on how to ask questions to get quick and correct answers, it should help a lot.

I'll be happy to help, once I know what your tables look like, and what client tool(s) you are using (Query Analyzer, Microsoft Access, etc).

-PatP|||I have two databases database1 and database2 i need to copy all the data in database1 field lastname to database2 field field2 but only if database1.cust_no matches database2.sourceid|||CREATE TABLE database1 (
cust_no VARCHAR(9)
, lastname VARCHAR(25)
)

CREATE TABLE database2 (
cust_no VARCHAR(9)
, field2 VARCHAR(100)
)

INSERT INTO database1 (cust_no, lastname)
SELECT 'doe', 'Doe' UNION
SELECT 'jones', 'Jones' UNION
SELECT 'freshfitz', 'Freshfitz' UNION
SELECT 'pope', 'Pope' UNION
SELECT 'smith', 'Smith'

INSERT INTO database2 (cust_no, field2)
SELECT 'doe', NULL UNION
SELECT 'jones', 'Jones' UNION
SELECT 'freshfitz', 'was here' UNION
SELECT 'popper', NULL UNION
SELECT 'smith', 'Smith'

UPDATE z
SET field2 = lastname
FROM database2 AS z
JOIN database1 AS x
ON (x.cust_no = z.cust_no)

SELECT *
FROM database2

DROP TABLE database1
DROP TABLE database2-PatP|||Thats awesome but the problem is database1 and database 2 already exist and the fields already exist i just need to copy all the records in the field of database 1 field last name to database2 field field2(which has no data). The records in database1 have a unique id Cust_no that match the same unique id in database2 sourceid.

So if cust_no = sourceid copy the last name field

select * from database1 field lastname
copy to database2 field field2
where database1.cust_no = database2_sourceid|||So just start with the UPDATE, and you'll be fine.

-PatP

Copy a BLOB field in a stored-procedure from

Hello
I try to run the following query:
Update DestTable
set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID = 123)
Where DestTableID = 321
I always get the error message that this is not possible with image data.
Does anyone know a solution on how to copy a BLOB from one table to another?
I tried with WRITETEXT but this does not work either.
Many thanks
Thomas (thomas.steiner@.novaesprit.ch)Can you post your WRITETEXT code please.
Rick
"Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> Hello
> I try to run the following query:
> Update DestTable
> set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID =123)
> Where DestTableID = 321
> I always get the error message that this is not possible with image data.
> Does anyone know a solution on how to copy a BLOB from one table to
another?
> I tried with WRITETEXT but this does not work either.
> Many thanks
> Thomas (thomas.steiner@.novaesprit.ch)|||Here it is:
Declare @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(content)
FROM SambaReports_Test.dbo.Documents
Where DocumentID = 171
WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
RequestDataBody from RequestData where RequestID = 2328)
This results in a 'missing datastream' error. When I put a text-string
instead of the select-statement it works.
Thomas
"Rick Sawtell" wrote:
> Can you post your WRITETEXT code please.
> Rick
>
> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> > Hello
> > I try to run the following query:
> >
> > Update DestTable
> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID => 123)
> > Where DestTableID = 321
> >
> > I always get the error message that this is not possible with image data.
> > Does anyone know a solution on how to copy a BLOB from one table to
> another?
> > I tried with WRITETEXT but this does not work either.
> >
> > Many thanks
> >
> > Thomas (thomas.steiner@.novaesprit.ch)
>
>|||AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
to copy text data between tables. If your data type is ntext, you'll need
to add calculations to allow 2 bytes per character.
DECLARE @.ptrval binary(16),
@.StartIndex int,
@.RequestDataBodyLength int,
@.RequestDataBody varchar(8000)
--clear any existing data and init text pointer
UPDATE Documents
SET content = NULL
WHERE DocumentID = 171
SET @.StartIndex = 0
--get length of data to copy
SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
FROM RequestData
WHERE RequestID = 2328
--get text pointer
SELECT @.ptrval = TEXTPTR(content)
FROM Documents
WHERE DocumentID = 171
--copy data in chunks of 8000
WHILE @.StartIndex < @.RequestDataBodyLength
BEGIN
SELECT @.RequestDataBody = SUBSTRING(RequestDataBody, @.StartIndex, 8000)
FROM RequestData
WHERE RequestID = 2328
UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> Here it is:
> Declare @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(content)
> FROM SambaReports_Test.dbo.Documents
> Where DocumentID = 171
> WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> RequestDataBody from RequestData where RequestID = 2328)
> This results in a 'missing datastream' error. When I put a text-string
> instead of the select-statement it works.
> Thomas
> "Rick Sawtell" wrote:
>> Can you post your WRITETEXT code please.
>> Rick
>>
>> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
>> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
>> > Hello
>> > I try to run the following query:
>> >
>> > Update DestTable
>> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
>> > =>> 123)
>> > Where DestTableID = 321
>> >
>> > I always get the error message that this is not possible with image
>> > data.
>> > Does anyone know a solution on how to copy a BLOB from one table to
>> another?
>> > I tried with WRITETEXT but this does not work either.
>> >
>> > Many thanks
>> >
>> > Thomas (thomas.steiner@.novaesprit.ch)
>>|||Hi Dan
It works fine! Thanks a lot.
However I had to change the line:
SUBSTRING(RequestDataBody, @.StartIndex, 8000)
to
SUBSTRING(RequestDataBody, @.StartIndex+1, 8000)
Best regards
Thomas
"Dan Guzman" wrote:
> AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
> to copy text data between tables. If your data type is ntext, you'll need
> to add calculations to allow 2 bytes per character.
>
> DECLARE @.ptrval binary(16),
> @.StartIndex int,
> @.RequestDataBodyLength int,
> @.RequestDataBody varchar(8000)
> --clear any existing data and init text pointer
> UPDATE Documents
> SET content = NULL
> WHERE DocumentID = 171
> SET @.StartIndex = 0
> --get length of data to copy
> SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
> FROM RequestData
> WHERE RequestID = 2328
> --get text pointer
> SELECT @.ptrval = TEXTPTR(content)
> FROM Documents
> WHERE DocumentID = 171
> --copy data in chunks of 8000
> WHILE @.StartIndex < @.RequestDataBodyLength
> BEGIN
> SELECT @.RequestDataBody => SUBSTRING(RequestDataBody, @.StartIndex, 8000)
> FROM RequestData
> WHERE RequestID = 2328
> UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
> SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
> news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> > Here it is:
> >
> > Declare @.ptrval binary(16)
> >
> > SELECT @.ptrval = TEXTPTR(content)
> > FROM SambaReports_Test.dbo.Documents
> > Where DocumentID = 171
> > WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> > RequestDataBody from RequestData where RequestID = 2328)
> >
> > This results in a 'missing datastream' error. When I put a text-string
> > instead of the select-statement it works.
> >
> > Thomas
> >
> > "Rick Sawtell" wrote:
> >
> >> Can you post your WRITETEXT code please.
> >>
> >> Rick
> >>
> >>
> >> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> >> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> >> > Hello
> >> > I try to run the following query:
> >> >
> >> > Update DestTable
> >> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
> >> > => >> 123)
> >> > Where DestTableID = 321
> >> >
> >> > I always get the error message that this is not possible with image
> >> > data.
> >> > Does anyone know a solution on how to copy a BLOB from one table to
> >> another?
> >> > I tried with WRITETEXT but this does not work either.
> >> >
> >> > Many thanks
> >> >
> >> > Thomas (thomas.steiner@.novaesprit.ch)
> >>
> >>
> >>
>
>

coosing more than 1 value in QUERY PARAMETERS Dialogue Box

You know how there is a Query Parameter Dialogue Box in the Data Tab in Reporting services. In other words, if you have a query with a parameter and want to run your query, this dialogue box appears and wants you to enter the value for the parameter. How can I choose more than 1 parameter in Query parameter dialogue box. I mean, I have a SalesPerson parameter in my query, and whenever I enter John as the value for SalesPerson Parameter I am OK. Whenever I enter Bob as the value I am OK too. How can I see the results for both John and Bob?

John, Bob seems not to work

IN (John, Bob) Seems not to work either.

What is the correct syntax.

Pleaseee.(I'm going crazy)

Thank you

This problem would better be solved by viewing these scenarios in the preview tab.

Cool Query Analyzer Trick

In Query Analyzer you can save a lot of time by using this trick instead of
typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
I work with people who are certified, have 10 years experience and none of
them knew this trick
Also if you know of any other not well known QA tricks let me know and I
will update my blog
http://sqlservercode.blogspot.com/
Here are some other nice shortcuts (at least in SQL2k and below)
Ctrl-R
Ctrl-E
F6
Highlight some text and hit Ctrl-U or Ctrl-L
Highlight some text and hit Shift+Ctrl+C
now try Shift+Ctrl+R
Keith
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||On Thu, 22 Sep 2005 13:28:03 -0500, Keith Kratochvil wrote:

>Here are some other nice shortcuts (at least in SQL2k and below)
>Ctrl-R
>Ctrl-E
>F6
>Highlight some text and hit Ctrl-U or Ctrl-L
Hi Keith,
I assume that you meeant Ctrl-Shift-U and Ctrl-Shift-L?
(Ctrl-U is change database and Ctrl-L is display estimated execution
plan).

>Highlight some text and hit Shift+Ctrl+C
>now try Shift+Ctrl+R
And to get to know all keyboard shortcuts, study the menu's. All
shortcuts are listed in the menu's.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You can also right click on the table and generate a Select, insert , Update
or Delete statement as well.
Andrew J. Kelly SQL MVP
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||As a follow on to Andrews tip, after generating an insert, delete or
update statement, press Ctrl-Shift-M...
Jacko

Convertion VarChar Error

Ive got a small problem at the moment

I have ran a query that has been used for a while now and have recieved this error

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'N' to a column of data type int.

Ive searched the data and the only value of N that i can find is currently sitting in a field where the field type is varchar

is there a workaround for this, Ive tried running a case statement to set the N to 0 and also tried casting

Cheers in advance
Dave...the only value of N that i can find is currently sitting in a field where the field type is varcharso why is it trying to convert this value to an integer?

i have no idea, because i can't see your query from here

:)|||rudy man i thought you were psychic

-- 5302 MH consultant OP first attendances

SELECT CdsType,
NHSTrust,
AttendedOrDNACode, --N apears here varchar(1) column
FirstAttendanceCode,
SpecialtyCode,
Specialty,
PCG,
PurchCode,
datepart (year, ActivityDate) as yearAct,
datepart (month, ActivityDate) as monthAct

FROM dbo.VIEW_Outpatient2000_Analysis

WHERE PurchCode like '5KW%'
--pcg like 'Chelt%'
and NHSTrust not like 'Glou%'
and FirstAttendanceCode = 1
and AttendedOrDNACode in ('5', '6', '1','N')
and SpecialtyCode between '710' and '715'
and ((datepart (year, ActivityDate) = 2004
and datepart (month, ActivityDate) > 03)
or (datepart (year, ActivityDate) = 2005
and datepart (month, ActivityDate) < 04))

So im pretty much confused, cant see anything there that would cause a problem|||the 'N' may not necessarily be where you think it is

FirstAttendanceCode = 1|||Ah Blind as a Bat i am
Cheers rudy

Converting varchar to int

Hi,
I have a varchar(255) column where I may have data like this:
43294430949
adkk3400
1056
ff1d
10
302
15000043
I would like to write a SQL query that returns values between
10 and 500 (numeric)
If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect
I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500
but it fails when I have characters in the column.
Do you guys know how I can do that?
ThanksFirst you need a solid function that can determine if the value is numeric.
For
that go here: http://www.aspfaq.com/show.asp?id=2390.
Select *
From #Test As T
Where IsNumeric(T.Data) = 1
And dbo.IsReallyInteger(T.Data) = 1
And Cast(T.Data As BigInt) Between 10 And 50
Why the two checks? If you only use IsReallyNumeric, SQL cannot determine wh
at
that function actually does and more specifically, whether it filters for va
lues
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int, colum
n)
> <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks|||Try,
Select *
from table
where
case
when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
else null
end between 10 and 500
AMB
"Star" wrote:

> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks
>|||You can write your query with a WHERE clause like:
WHERE CASE WHEN ISNUMERIC( col ) = 1
THEN CAST( col AS BIGINT )
END BETWEEN 10 AND 500 ;
Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.
Anith|||We need more information:
1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?
2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?
So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):
SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500
If this is not what you are looking for, you will need to provide better
specifications.
P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks

Converting User SIDS from SQL 2000 to SQL 2005

WE have a query that we use whenever we transfer a database between servers
that resyns the SIDS in the database to match those in the new installation.
I am trying to convert a databse from 2000 to run on 2005 and the query no
longer works because 2005 does not store the user information in the same wa
y
as 2000. Can someone point me in the right direction to update this query
for 2005. Or, perhaps there is another approach I should be taking?
Here is the query:
/ ****************************************
*****************
* This script is used to synch the SIDs between
* the login and the database user. This should
* be executed after a database is copied to a new
* server and attached to keep the permissions as
* they were on the server where the database originated.
****************************************
******************/
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @.LoginSID VARBINARY(85)
-- Update the SID for the SEMS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'SEMS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'SEMS'
-- Update the SID for the RptWriters user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'RptWriters'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'RptWriters'
-- Update the SID for the TSEQALS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'TSEQALS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'TSEQALS'
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE
GO
Thanks,
JohnJohn
I did it by using two stored procedures that MS have provided. I have not
played with it on SQL Server 2005 since I re-created logins on the new
server , however it worth to try.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
> WE have a query that we use whenever we transfer a database between
> servers
> that resyns the SIDS in the database to match those in the new
> installation.
> I am trying to convert a databse from 2000 to run on 2005 and the query no
> longer works because 2005 does not store the user information in the same
> way
> as 2000. Can someone point me in the right direction to update this query
> for 2005. Or, perhaps there is another approach I should be taking?
> Here is the query:
> / ****************************************
*****************
> * This script is used to synch the SIDs between
> * the login and the database user. This should
> * be executed after a database is copied to a new
> * server and attached to keep the permissions as
> * they were on the server where the database originated.
> ****************************************
******************/
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> DECLARE @.LoginSID VARBINARY(85)
> -- Update the SID for the SEMS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'SEMS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'SEMS'
> -- Update the SID for the RptWriters user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'RptWriters'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'RptWriters'
> -- Update the SID for the TSEQALS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'TSEQALS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'TSEQALS'
> GO
> sp_configure 'allow updates', 0
> GO
> RECONFIGURE
> GO
>
> Thanks,
> John
>|||Uri,
Thanks for your suggestion; however, apparently, the sysxlogins table no
longer exists in 2005. That's the point my script was failing as well.
Thanks,
"Uri Dimant" wrote:

> John
> I did it by using two stored procedures that MS have provided. I have not
> played with it on SQL Server 2005 since I re-created logins on the new
> server , however it worth to try.
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
> "John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
> news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
>
>|||Hi John.
Take a look at this link http://support.microsoft.com/kb/246133/en-us. I
change this script to generate new DDL sintax for SQL Server 2005. You can
get it from
http://solidqualitylearning.com/blo...01/28/1515.aspx
(comments are in Spanish, sorry :-) )
"John Beschler" <JohnBeschler@.discussions.microsoft.com> escribi en el
mensaje news:D002F332-27CD-422A-B26C-BA267F5C77C3@.microsoft.com...[vbcol=seagreen]
> Uri,
> Thanks for your suggestion; however, apparently, the sysxlogins table no
> longer exists in 2005. That's the point my script was failing as well.
> Thanks,
>
> "Uri Dimant" wrote:
>

Converting T-SQL *= OUTER JOINS to ANSI-92 syntax

Hello all. I have an application that has worked smoothly using the following query syntax:
FROM tbl_participation, tbl_adult, tbl_month, tbl_school_year
WHERE tbl_participation.adult_ID =* tbl_adult.ID

AND tbl_participation.month_ID =* tbl_month.ID
AND tbl_participation.year_id =* tbl_school_year.ID

AND tbl_adult.ID = 8
AND tbl_school_year.ID = 5
It works just fine, as I want the results to include a table with one column containing the month name, the table headed by the adult's name/school year. It needs to still return a table even if there have yet been no records in tbl_participation for that adult/month/year.
However, the following is my best shot at making the query ANSI-92 compliant, as when I implement SQL Server 2005 I don't want to have to go back and change compatibility modes:
FROM ((tbl_adult

LEFT OUTER JOIN tbl_participation ON tbl_participation.adult_ID = tbl_adult.ID)

RIGHT OUTER JOIN tbl_month ON tbl_participation.month_ID = tbl_month.ID)

RIGHT OUTER JOIN tbl_school_year ON tbl_participation.year_id = tbl_school_year.ID

WHERE tbl_adult.ID = 8
AND tbl_school_year.ID = 5
This query works fine if there is any data in tbl_participation for adult 8 and school year 5. But if nothing has yet been entered, it returns nothing. I need it to work like the older T-SQL iteration, and still return a list of the 12 months and the adult's name even if no participation data has been entered.
Thanks - this one has got me pulling my hair out.

With the JOIN clause, you should logically think of the ON clause as being evaluated first followed by WHERE clause, GROUP BY and HAVING clause. Note that with inner joins the optimizer can evaluate predicates in the WHERE and ON clause together. But when you outer joins the WHERE clause is always evaluated after the ON clause. So this will essentially prevent non-matching rows from being produced based on your example. Additionally, it also depends on whether you filter the rows before the outer join. So in your example, you need to move the tbl_adult.ID check to FROM clause using a derived table like:

FROM (( (select * from tbl_adult where ID = 8) as a
LEFT OUTER JOIN tbl_participation as p ON p.adult_ID = a.ID)
RIGHT OUTER JOIN tbl_month as m ON p.month_ID = m.ID)
RIGHT OUTER JOIN tbl_school_year as y ON p.year_id = y.ID
WHERE y.ID = 5

Converting to ANSI joins

Is it possible to convert the following query to use ANSI join syntax?
It may be already but I'm suspecting the "WHERE t2.table_id =
t1.table_id" means it isn't. I tried things like the bottom query but
the number of records returned doesn't match. Any help would be
appreciated. I can't give the DDL or test date cause this is a
simplification of the actual query. Thanks a lot!
SELECT DISTINCT t1.table_id
FROM mytable t1
WHERE (t1.username = 'john')
AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
t1.table_id AND t2.username <> 'john'))
-- my attempt --
SELECT DISTINCT t1.table_id
FROM mytable t1
INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
'john'
WHERE (t1.username = 'john')It's not a JOIN but a correlated subquery. It is possible to replace this
clause with a LEFT JOIN and testing for the presence of the null value at
the right side; something like:
SELECT DISTINCT t1.table_id
FROM mytable t1 LEFT JOIN mytable t2 on (t2.table_id = t1.table_id AND
t2.username <> 'john')
WHERE (t1.username = 'john')
And t2.table_id is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149545664.941792.259700@.h76g2000cwa.googlegroups.com...
> Is it possible to convert the following query to use ANSI join syntax?
> It may be already but I'm suspecting the "WHERE t2.table_id =
> t1.table_id" means it isn't. I tried things like the bottom query but
> the number of records returned doesn't match. Any help would be
> appreciated. I can't give the DDL or test date cause this is a
> simplification of the actual query. Thanks a lot!
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> WHERE (t1.username = 'john')
> AND (NOT EXISTS (SELECT * FROM mytable t2 WHERE t2.table_id =
> t1.table_id AND t2.username <> 'john'))
>
> -- my attempt --
> SELECT DISTINCT t1.table_id
> FROM mytable t1
> INNER JOIN mytable t2 ON t2.table_id = t1.table_id AND t2.username =
> 'john'
> WHERE (t1.username = 'john')
>|||Thank you Sylvain I will try that.
Phil|||It works! Thank you Sylvain !!!|||The advantage of the LEFT JOIN is that on many occasions it will be faster
then the use of an Exists statement (not sure if it's still true but it was
some years ago).
However, it's much more easier to write some complex filterings with an
Exists() function than with the LEFT JOIN.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"phils" <jlee@.transfuture.net> wrote in message
news:1149603901.621194.200240@.h76g2000cwa.googlegroups.com...
> It works! Thank you Sylvain !!!
>

Converting text to bit issues

I need to convert a text column to a bit in a select query.
Backing up, in case there's a better way to do this:
I have a varchar column that contains one of six letters. There are two
that interest me (Q and A). I want my vb.net application to update changes
back to this column.
For my application, Q=0 and A=1
The name of the column is 'Status'
I tried:
Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
.. didn't work. Any help?
UsarianTry,
Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
also:
convert(bit, ascii(upper(colA)) - ascii('Q'))
AMB
"Usarian Skiff" wrote:

> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> ... didn't work. Any help?
> Usarian
>
>|||Usarian Skiff wrote:
> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> .. didn't work. Any help?
> Usarian
>
cast((case when status = 'q' then 0 else 1 end) as bit)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Stinkin Yeah!
Thanks!
Usarian Skiff
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87BB3703-E7B8-4946-B0BF-681FBF548A61@.microsoft.com...
> Try,
> Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
> also:
> convert(bit, ascii(upper(colA)) - ascii('Q'))
>
> AMB
>
> "Usarian Skiff" wrote:
>
changes

Converting Select query to Update

Hi,
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:

> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP