Within a SQL Server Job, I am using the following vbscript to connect to a
FTP server and FTP a file:
strLocalFolderName = "My Folder Name where we put the file to be FTPed"
strFTPServerName = "FTP Server Name"
strLoginID = "FTP Server Login ID"
strPassword = "FTP Login ID Password"
strFTPServerFolder = "Folder Name on FTP server where the file resides"
strFile2Get = "This file"
strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile (strFTPScriptFileName)
End If
Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine ("open " & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine ("cd " & strFTPServerFolder)
objMyFile.WriteLine ("ascii")
objMyFile.WriteLine ("lcd " & strLocalFolderName)
objMyFile.WriteLine ("get " & strFile2Get)
objMyFile.WriteLine ("bye")
objMyFile.Close
Set objFSO = Nothing
Set objMyFile = Nothing
Before I FTP the file, I need to check to see if that file exist. What is
the best way for me to check to see if that file exist? Or how can I
capture the code from the FTP command if it can't find the file?Oops, I left this out of my code example. Add this to the end of the code:
Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run ("ftp -s:" & chr(34) & strFTPScriptFileName & chr(34))
Set objShell = Nothing
"David" wrote:
> Within a SQL Server Job, I am using the following vbscript to connect to a
> FTP server and FTP a file:
> strLocalFolderName = "My Folder Name where we put the file to be FTPed"
> strFTPServerName = "FTP Server Name"
> strLoginID = "FTP Server Login ID"
> strPassword = "FTP Login ID Password"
> strFTPServerFolder = "Folder Name on FTP server where the file resides"
> strFile2Get = "This file"
> strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt"
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> If (objFSO.FileExists(strFTPScriptFileName)) Then
> objFSO.DeleteFile (strFTPScriptFileName)
> End If
> Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
> objMyFile.WriteLine ("open " & strFTPServerName)
> objMyFile.WriteLine (strLoginID)
> objMyFile.WriteLine (strPassword)
> objMyFile.WriteLine ("cd " & strFTPServerFolder)
> objMyFile.WriteLine ("ascii")
> objMyFile.WriteLine ("lcd " & strLocalFolderName)
> objMyFile.WriteLine ("get " & strFile2Get)
> objMyFile.WriteLine ("bye")
> objMyFile.Close
> Set objFSO = Nothing
> Set objMyFile = Nothing
> Before I FTP the file, I need to check to see if that file exist. What
is
> the best way for me to check to see if that file exist? Or how can I
> capture the code from the FTP command if it can't find the file?
Showing posts with label exist. Show all posts
Showing posts with label exist. Show all posts
Friday, March 30, 2012
If exists update, if it doesn't exist create
What I am looking for is a table that has two columns we'll say FName and Timestamp. What I want to do is to see if that name exists in my records. If it does I want to update it with a new timestamp. If it does not I want to create a new record in my table. This is going to be a check from vb 6.0 that is running continuously, so it will run the stored procedure to look for Redundancy in the FName column, if it finds it I want it to update the timestamp in that record to show the most up to date time.
Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.|||
You do have a specific name your searching for though right? This method will update all of the records matching your input parameter.
Thank you in advance.First, check if the record exists, if not then add it to the table :
IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table
Now update the table
UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'|||
Quote:
Originally Posted by SkinHead
First, check if the record exists, if not then add it to the table :
IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table
Now update the table
UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'
Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.|||
Quote:
Originally Posted by JReneau35
Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.
You do have a specific name your searching for though right? This method will update all of the records matching your input parameter.
You come in with a name and you want it to search for that name in a table. If it doesnt find it, you want one new record created with a timestamp. If it does find it, no matter how many times it finds it, you want all of them updated.
Am I understanding you right?
Wednesday, March 28, 2012
If exists for temp table
What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
> > What is the syntax to drop a temporary table if exists ? I want to drop a
> > temp table ##temptable if exist before I try to create it.
> >
> > Thanks.
>
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
> > What is the syntax to drop a temporary table if exists ? I want to drop a
> > temp table ##temptable if exist before I try to create it.
> >
> > Thanks.
>
If exists for temp table
What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>
If Exists Column ?
Hello
How do you check if a column exist ?
for a table :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO
but I dont find it for a column
Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?
thank you for helping|||Here we go.......
--alter table tablename drop column columnname
if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO
INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO
SELECT * FROM myTable99
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2
SELECT * FROM myTable99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO
How do you check if a column exist ?
for a table :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO
but I dont find it for a column
Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?
thank you for helping|||Here we go.......
--alter table tablename drop column columnname
if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO
INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO
SELECT * FROM myTable99
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2
SELECT * FROM myTable99
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO
if exists
i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
IF EXISTS
I wanna check the linked server is exist then do nothing and if not then
create linked server. Any command ?
like
if not exists()
begin
EXEC sp_addlinkedserver 'FASAT\FASATBASE'
EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
'testing'
end
Thanks
David
David,
You can try something like:
if not exists
(select * from sysservers where srvname like
'YourLinkedServerName')
-Sue
On Wed, 26 Jan 2005 11:51:32 -0500, "David"
<david@.north.com> wrote:
>I wanna check the linked server is exist then do nothing and if not then
>create linked server. Any command ?
>like
>if not exists()
>begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
>'testing'
>end
>
>Thanks
>David
>
|||IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
Keith
"David" <david@.north.com> wrote in message
news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I wanna check the linked server is exist then do nothing and if not then
> create linked server. Any command ?
> like
> if not exists()
> begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
> 'testing'
> end
>
> Thanks
> David
>
|||Thanks sue
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bvkfv0putlakl00d3qsh4sf91msjhcntg4@.4ax.com...
> David,
> You can try something like:
> if not exists
> (select * from sysservers where srvname like
> 'YourLinkedServerName')
> -Sue
> On Wed, 26 Jan 2005 11:51:32 -0500, "David"
> <david@.north.com> wrote:
>
|||Howdy Yall,
ALA, like this:
<@.sServerName, @.sSrvProduct, @.sProviderString, @.sPathToMdb are passed-in>
--Kill the server IF it exists and get rid of the user logins too!
IF EXISTS(SELECT * FROM master..sysservers where srvname=@.sServerName)
EXEC sp_dropserver @.server = @.sServerName, @.droplogins = 'droplogins'
--This will create the LINK
EXEC sp_addlinkedserver @.server = @.sServerName,
@.srvproduct = @.sSrvProduct,
@.provider = @.sProviderString,
@.datasrc = @.sPathToMdb
IF @.@.ERROR=0
EXEC sp_addlinkedsrvlogin @.sServerName, 'false', NULL, NULL, NULL
Regards,
jetgeek
"Keith Kratochvil" wrote:
> IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
>
> --
> Keith
>
> "David" <david@.north.com> wrote in message
> news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
>
create linked server. Any command ?
like
if not exists()
begin
EXEC sp_addlinkedserver 'FASAT\FASATBASE'
EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
'testing'
end
Thanks
David
David,
You can try something like:
if not exists
(select * from sysservers where srvname like
'YourLinkedServerName')
-Sue
On Wed, 26 Jan 2005 11:51:32 -0500, "David"
<david@.north.com> wrote:
>I wanna check the linked server is exist then do nothing and if not then
>create linked server. Any command ?
>like
>if not exists()
>begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
>'testing'
>end
>
>Thanks
>David
>
|||IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
Keith
"David" <david@.north.com> wrote in message
news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I wanna check the linked server is exist then do nothing and if not then
> create linked server. Any command ?
> like
> if not exists()
> begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
> 'testing'
> end
>
> Thanks
> David
>
|||Thanks sue
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bvkfv0putlakl00d3qsh4sf91msjhcntg4@.4ax.com...
> David,
> You can try something like:
> if not exists
> (select * from sysservers where srvname like
> 'YourLinkedServerName')
> -Sue
> On Wed, 26 Jan 2005 11:51:32 -0500, "David"
> <david@.north.com> wrote:
>
|||Howdy Yall,
ALA, like this:
<@.sServerName, @.sSrvProduct, @.sProviderString, @.sPathToMdb are passed-in>
--Kill the server IF it exists and get rid of the user logins too!
IF EXISTS(SELECT * FROM master..sysservers where srvname=@.sServerName)
EXEC sp_dropserver @.server = @.sServerName, @.droplogins = 'droplogins'
--This will create the LINK
EXEC sp_addlinkedserver @.server = @.sServerName,
@.srvproduct = @.sSrvProduct,
@.provider = @.sProviderString,
@.datasrc = @.sPathToMdb
IF @.@.ERROR=0
EXEC sp_addlinkedsrvlogin @.sServerName, 'false', NULL, NULL, NULL
Regards,
jetgeek
"Keith Kratochvil" wrote:
> IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
>
> --
> Keith
>
> "David" <david@.north.com> wrote in message
> news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
>
if exists
i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?
Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
sql
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?
Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
sql
if exists
i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?what version of sql server is the problem in?
are there perhaps other owners of identically named tables?
for example, is there both a mauro.table and a dbo.table, where the
mauro.table has the b column and the dbo.table does not?
Mauro wrote:
> i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>|||2005., NO, the column does not exists in the whole server, in the 2000
server it works perfectly and in 2005 in another databases it also works
fine.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Of%23ruGI7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> what version of sql server is the problem in?
> are there perhaps other owners of identically named tables?
> for example, is there both a mauro.table and a dbo.table, where the
> mauro.table has the b column and the dbo.table does not?
> Mauro wrote:
not
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?what version of sql server is the problem in?
are there perhaps other owners of identically named tables?
for example, is there both a mauro.table and a dbo.table, where the
mauro.table has the b column and the dbo.table does not?
Mauro wrote:
> i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>|||2005., NO, the column does not exists in the whole server, in the 2000
server it works perfectly and in 2005 in another databases it also works
fine.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Of%23ruGI7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> what version of sql server is the problem in?
> are there perhaps other owners of identically named tables?
> for example, is there both a mauro.table and a dbo.table, where the
> mauro.table has the b column and the dbo.table does not?
> Mauro wrote:
not
if exists
i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
Monday, March 19, 2012
Identity range when rows already exist
I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:
> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.
>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:
> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.
>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place
Subscribe to:
Posts (Atom)