about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
serge
Hello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id'@.position'
, c.name'name'
, y.name'dataType'
, c.max_length'maxLength'
, c.precision'precision'
, c.scale'scale'
, c.collation_name'collationName'
, c.is_nullable'nullable'
, c.is_rowguidcol'isRowGUID'
, c.is_identity'isIdentity'
, c.is_computed'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collection_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
sergeHello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id '@.position'
, c.name 'name'
, y.name 'dataType'
, c.max_length 'maxLength'
, c.precision 'precision'
, c.scale 'scale'
, c.collation_name 'collationName'
, c.is_nullable 'nullable'
, c.is_rowguidcol 'isRowGUID'
, c.is_identity 'isIdentity'
, c.is_computed 'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collec
tion_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name 'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

A story of incorrect syntax

Dear Group

Just can't see what I'm doing wrong. Any Ideas?

SELECT Address1, Address2, Suburb FROM i2b_address
IsNull (NullIf (Address1 + '', ''), '') +
IsNull (NullIf('','' + Address2,''),'') +
IsNull (NullIf('', '' + Suburb, ''), '') AS PropertyAddress

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'NullIf'.

Thanks for your help & efforts!

MartinSELECT Address1, Address2, Suburb,
IsNull (NullIf (Address1 + '', ''), '') +
IsNull (NullIf('','' + Address2,''),'') +
IsNull (NullIf('', '' + Suburb, ''), '') AS PropertyAddress
-- -> from clause goes here
FROM I2B_ADDRESS|||Hi! Well, something we don't notice the obvious! Thanks very much! I
think I need a break!
But still getting an error... Argh!!! What's wrong?

SELECT Address1, Address2, Suburb,
IsNull (NullIf (Address1 + '', ''), '') +
IsNull (NullIf('','' + Address2,''),'') +
IsNull (NullIf('', '' + Suburb, ''), '') AS PropertyAddress
FROM i2b_address

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

Thanks for your help & efforts!

M|||Solved It!

IsNull (NullIf (Address1 + '', ''), '') +
IsNull (NullIf (', ' + Address2, ''), '') +
IsNull (NullIf (', ' + Suburb, ''), '') AS PropertyAddress|||If you use the Northwind database, Customers table, you'll see that
this also works...
I don't have your data structures and therefore need to use something I
do have...

SELECT ContactName, ContactTitle, Fax,
IsNull(NullIf (ContactName + ' ', ''), ' ') +
IsNull(NullIf(ContactTitle + ' ', ''), ' ') +
IsNull(NullIf(Fax + ' ', ''), ' ') AS PropertyAddress
FROM Customers

A severe error occurred on the current command

Dear fellow sufferers...
I get the error noticed in the subjectline, while trying to put a 150MB
ZipFile into a SQL-2000-SP4-DB. This is the way I do it:
'***************************************
************************************
***********************
Public Function CYCLE__GisData_ZIP_Add(ByRef lCycleID As Long, _
ByVal sZipFile As String, _
ByRef sRetErrMsg As String) As Boolean
' Local Variables
Dim bError As Boolean
Dim sRetErrMsgSub As String
Dim fiZipFile As IO.FileInfo
Dim sSQL As String
Dim lFileLength As Long
Dim fileStream As IO.FileStream
Dim binReader As IO.BinaryReader
Dim binaryData() As Byte
Dim cmdSQL As SqlCommand
Dim paraSQL As SqlParameter
'-> reset return value
CYCLE__GisData_ZIP_Add = False
bError = False
sRetErrMsg = "Function: " & mc_sClassName & ".CYCLE__GisData_ZIP_Add"
Try
fiZipFile = New IO.FileInfo(sZipFile)
'-> save to Stream
fileStream = fiZipFile.OpenRead
binReader = New IO.BinaryReader(fileStream)
binaryData = binReader.ReadBytes(fileStream.Length)
lFileLength = fileStream.Length
binReader.Close()
fileStream.Close()
sSQL = "UPDATE tab_Cycle " + _
"SET GIS_Data = @.GIS_Data " + _
"WHERE (id = " & lCycleID & ")"
cmdSQL = New SqlCommand(sSQL, m_con_eRM_RFC)
paraSQL = New SqlParameter("@.GIS_Data", SqlDbType.Image)
paraSQL.Direction = ParameterDirection.Input
paraSQL.Value = binaryData
cmdSQL.Parameters.Add(paraSQL)
cmdSQL.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
Dim strSQLErr As String
Dim sqlErr As SqlError
For Each sqlErr In ex.Errors
strSQLErr = strSQLErr & sqlErr.ToString & vbNewLine
Next sqlErr
Throw New Exception(strSQLErr)
Catch ex As Exception
Throw New Exception(sRetErrMsg & sRetErrMsgSub & vbCrLf &
ex.ToString & vbCrLf)
Finally
'-> set return value
CYCLE__GisData_ZIP_Add = Not bError
End Try
End Function
'***************************************
************************************
***********************
All objects used are filled and the error occures on the
cmdSQL.ExecuteNonQuery(). There is no SQL-Errornumber or something like
that...
Any ideas or hints are welcome!
Cheers,
ChristophFor large binary streams, use a chunking method rather than sending the
entire value at once. See MSKB 317043
<http://support.microsoft.com/defaul...kb;en-us;317043> for an
examples.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cheffe" <Cheffe@.discussions.microsoft.com> wrote in message
news:054F78D7-C8E9-405E-A67B-BB856074E090@.microsoft.com...
> Dear fellow sufferers...
> I get the error noticed in the subjectline, while trying to put a 150MB
> ZipFile into a SQL-2000-SP4-DB. This is the way I do it:
> '***************************************
**********************************
*************************
> Public Function CYCLE__GisData_ZIP_Add(ByRef lCycleID As Long, _
> ByVal sZipFile As String, _
> ByRef sRetErrMsg As String) As Boolean
> ' Local Variables
> Dim bError As Boolean
> Dim sRetErrMsgSub As String
> Dim fiZipFile As IO.FileInfo
> Dim sSQL As String
> Dim lFileLength As Long
> Dim fileStream As IO.FileStream
> Dim binReader As IO.BinaryReader
> Dim binaryData() As Byte
> Dim cmdSQL As SqlCommand
> Dim paraSQL As SqlParameter
>
> '-> reset return value
> CYCLE__GisData_ZIP_Add = False
> bError = False
> sRetErrMsg = "Function: " & mc_sClassName &
> ".CYCLE__GisData_ZIP_Add"
> Try
> fiZipFile = New IO.FileInfo(sZipFile)
> '-> save to Stream
> fileStream = fiZipFile.OpenRead
> binReader = New IO.BinaryReader(fileStream)
> binaryData = binReader.ReadBytes(fileStream.Length)
> lFileLength = fileStream.Length
> binReader.Close()
> fileStream.Close()
> sSQL = "UPDATE tab_Cycle " + _
> "SET GIS_Data = @.GIS_Data " + _
> "WHERE (id = " & lCycleID & ")"
> cmdSQL = New SqlCommand(sSQL, m_con_eRM_RFC)
> paraSQL = New SqlParameter("@.GIS_Data", SqlDbType.Image)
> paraSQL.Direction = ParameterDirection.Input
> paraSQL.Value = binaryData
> cmdSQL.Parameters.Add(paraSQL)
> cmdSQL.ExecuteNonQuery()
>
> Catch ex As System.Data.SqlClient.SqlException
> Dim strSQLErr As String
> Dim sqlErr As SqlError
> For Each sqlErr In ex.Errors
> strSQLErr = strSQLErr & sqlErr.ToString & vbNewLine
> Next sqlErr
> Throw New Exception(strSQLErr)
> Catch ex As Exception
> Throw New Exception(sRetErrMsg & sRetErrMsgSub & vbCrLf &
> ex.ToString & vbCrLf)
> Finally
> '-> set return value
> CYCLE__GisData_ZIP_Add = Not bError
> End Try
> End Function
> '***************************************
**********************************
*************************
> All objects used are filled and the error occures on the
> cmdSQL.ExecuteNonQuery(). There is no SQL-Errornumber or something like
> that...
> Any ideas or hints are welcome!
> Cheers,
> Christoph
>

A Question Regarding MSDE Desktop Engine

Dear all,

This week I was told to setup MSDE Desktop Engine on my pc bcz my Access back end db is getting bigger in size. And as a little advance step, I decided to put my Access back end on SQL server.

To be frank, I have no experience with SQL server but after reading the instructions, I have installed MSDE (latest version) on my pc successfully. A small icon of SQL server appeared nearby clock icon of my pc showing the SQL server is running on ASHFAQUEPC.

I shifted a Trial db onto this server in following way.

Considering the opened Trial db would be my BE, from the Tools--Database Utility--Upsize wizard, I followed below steps according to Upsize wizard.

1. I selected Local server and check marked to Use Trusted Connection.
2. And then I selected some tables to export
3. Kept all default attributes of db as it is
4. Selected Let the wizard decide under data option to include.
5. At the next step What application change do you want to make?
a. Create a new Access client/Server application.
b. Link SQL Server tables to existing application.
c. No application changes. (I selected this option)

Then it showed me the report of my db description of changes that were made after deploying it. The report I did not print bcz it was a trial db with one table (2 fields) and a simple query.

My idea was to connect my Trial BE with another one of another Access mdb file so FE would be Access and BE would be from MSDE.

1. Now I dont know how to link my BE to my FE?
2. From where I can directly see my BE? Because SQL Server Service Manager does
not have any menu?
3. Where I was wrong in following steps?

Guidance in this regards is highly appreciated.

With kind regards,
AshfaqueI got it.

The tables of db from MSDE2000A I can get connected thru database project file.

Thanks.
Ashfaque

A Question on Database Restore

Dear All
I have a question on database restore in SQL server 2000.
I would like to restore a BAK file which is stored in another server. But
when i try to restore the BAK file through the restore wizard, i cannot find
the network drive and it can only show the local drive of the server.
Would you please kindly help to solve my problem, thanks a lot.
Kit
Try entering the UNC name of the shared drive and backup file directly in
the file name input box, assuming you have granted the appropriate
permissions. Alternatively, you could use the TSQL syntax directly e.g.
RESTORE DATABASE xx FROM DISK = '\\serverx\sharename'
(for a full database restore).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Kit" <yuck@.ha.org.hk> wrote in message
news:%23ukLxGBXEHA.3120@.TK2MSFTNGP12.phx.gbl...
> Dear All
> I have a question on database restore in SQL server 2000.
> I would like to restore a BAK file which is stored in another server. But
> when i try to restore the BAK file through the restore wizard, i cannot
find
> the network drive and it can only show the local drive of the server.
> Would you please kindly help to solve my problem, thanks a lot.
> Kit
>
|||To add to Peter's response, it is the SQL Server service account that needs
permissions to the share.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kit" <yuck@.ha.org.hk> wrote in message
news:%23ukLxGBXEHA.3120@.TK2MSFTNGP12.phx.gbl...
> Dear All
> I have a question on database restore in SQL server 2000.
> I would like to restore a BAK file which is stored in another server. But
> when i try to restore the BAK file through the restore wizard, i cannot
find
> the network drive and it can only show the local drive of the server.
> Would you please kindly help to solve my problem, thanks a lot.
> Kit
>

A Question on Database Restore

Dear All
I have a question on database restore in SQL server 2000.
I would like to restore a BAK file which is stored in another server. But
when i try to restore the BAK file through the restore wizard, i cannot find
the network drive and it can only show the local drive of the server.
Would you please kindly help to solve my problem, thanks a lot.
KitTry entering the UNC name of the shared drive and backup file directly in
the file name input box, assuming you have granted the appropriate
permissions. Alternatively, you could use the TSQL syntax directly e.g.
RESTORE DATABASE xx FROM DISK = '\\serverx\sharename'
(for a full database restore).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Kit" <yuck@.ha.org.hk> wrote in message
news:%23ukLxGBXEHA.3120@.TK2MSFTNGP12.phx.gbl...
> Dear All
> I have a question on database restore in SQL server 2000.
> I would like to restore a BAK file which is stored in another server. But
> when i try to restore the BAK file through the restore wizard, i cannot
find
> the network drive and it can only show the local drive of the server.
> Would you please kindly help to solve my problem, thanks a lot.
> Kit
>|||To add to Peter's response, it is the SQL Server service account that needs
permissions to the share.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kit" <yuck@.ha.org.hk> wrote in message
news:%23ukLxGBXEHA.3120@.TK2MSFTNGP12.phx.gbl...
> Dear All
> I have a question on database restore in SQL server 2000.
> I would like to restore a BAK file which is stored in another server. But
> when i try to restore the BAK file through the restore wizard, i cannot
find
> the network drive and it can only show the local drive of the server.
> Would you please kindly help to solve my problem, thanks a lot.
> Kit
>

A problem with a chart

Dear all,

I'm kinda new to reporting services and I have a small problem. Currently I'm using RS 2000, and I have a bar chart on my report. It displays names on the x-axis (I group by the name) and total number of hours on the y-axis (the value). I need to change that total number of hours with a percentage from the whole number of hours worked. My problem is I'm not capable of getting the total number of hours worked per all users since I'm grouping by users. How could I do that?

Any help regarding that issue is appreciated.You can calculate the total number of hours by using an aggregate function with an explicit scope name of the name of the chart reportitem. E.g.:
=Sum(Fields!Hours.Value, "chart1")

The percentage can be calculated as =Sum(Fields!Hours.Value) / Sum(Fields!Hours.Value, "chart1")

-- Robert

A problem with a chart

Dear all,

I'm kinda new to reporting services and I have a small problem. Currently I'm using RS 2000, and I have a bar chart on my report. It displays names on the x-axis (I group by the name) and total number of hours on the y-axis (the value). I need to change that total number of hours with a percentage from the whole number of hours worked. My problem is I'm not capable of getting the total number of hours worked per all users since I'm grouping by users. How could I do that?

Any help regarding that issue is appreciated.You can calculate the total number of hours by using an aggregate function with an explicit scope name of the name of the chart reportitem. E.g.:
=Sum(Fields!Hours.Value, "chart1")

The percentage can be calculated as =Sum(Fields!Hours.Value) / Sum(Fields!Hours.Value, "chart1")

-- Robert

A probably over and over again asked question of Syntax - Help please

Dear Group

I'd be grateful if you can provide me with a hint for the following:

Fields Table Contact
ContactID
Firstname
Lastname

Fields Table ContactMethod
ContactMethodID
ContactMethodTxt
ContactMethodTypeID

Linked by Tables:

Fields Table LkTbl_Contact_ContactMethod
LkTblID
ContactID
ContactMethodTypeID

Fields Table ContactMethodType
ContactMethodTypeID
ContactMethod

The purpose of this construct is that I keep all contactmethods such
as Email, Mobile, Home Work, Web in table ContactMethods. Whether it's
e.g. an Email or Mobile is identified through the ContactMethodType.
That enables me that a Contact can have 3 Mobile Numbers, 2 Email
addresses.

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

I must note that the contents of table ContactMethodType for column
Contactmethod looks like this:
Email
Mobile
Home
Work
Web

I do:

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

The question is, how do I map the result from table ContactmethodType
to my fields Home, Work and Mobile in the resultset? The Type is
identified in ContactMethodType but I can't figure out how to do this
best.

Your help and suggestions or any other better solution is very
appreciated.

Thanks in advance for your help and efforts,

MartinIs Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

theintrepidfox wrote:

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

create view v_FirstContactMethodIDs as
selectlcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
fromLkTbl_Contact_ContactMethod lcm
joinContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

selectc.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
fromContact c
left joinv_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left joinContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left joinContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left joinContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID|||On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).
>
theintrepidfox wrote:
>

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

>
"First" in what sense? Lowest ContactMethodID value among the
candidate rows?
>

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

>
create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go
>
select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

Hi Ed

Thanks for your message.

'Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'

Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.

I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.

SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername

Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---- ----
-------
------- ---
1 0 JSmith007
John Smith 777 123

Thank you for your help and efforts,

Martin|||On 15 Oct, 07:29, theintrepidfox <theintrepid...@.hotmail.comwrote:

Quote:

Originally Posted by

On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

>

Quote:

Originally Posted by

theintrepidfox wrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

>

Quote:

Originally Posted by

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

>

Quote:

Originally Posted by

create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

>

Quote:

Originally Posted by

select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

>
Hi Ed
>
Thanks for your message.
>
'Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'
>
Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.
>
I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.
>
SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername
>
Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---- ----
-------
------- ---
1 0 JSmith007
John Smith 777 123
>
Thank you for your help and efforts,
>
Martin- Hide quoted text -
>
- Show quoted text -

Got It working:

LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactID = i2b_contact.ContactID)
Instead of
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)

Still, if you have any suggestions for improvement, please let me
know.

Thanks again for your great help and time.

All the best,

Martin

A new report history from a report history

Dear Anyone,

I want to create a new report history but with A different parameter selection from an existing report history. Is this possible?

Thanks,
JosephSorry this is not possible.

-Daniel