Need Basic Help

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.Hi
You can use VFP as a front end.
For a starter try checking out:
http://msdn.microsoft.com/archive/d...asp?frame=true
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...tio
n.asp
John
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> At my workplace our ERP software in on a VFP database. I have learned
> quite
> a bit about extracting data with the select statements from the VFP
> database. We are in the talks of converting the the SQL version of our
> ERP
> and I have some very basic questions since I am extremely new to the SQL
> world.
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>|||Hi,
You'll get much better help posting questions like this in a FoxPro
newsgroup. Many FoxPro developers use SQL Server as a back end for their
applications and have much experience with it. SQL Server is a database
only - it has no front end to produce forms.
You can use the exact same SQL commands that you use in Query Analyzer to
access SQL Server via SQL Pass-through. Please look at SQLConnect,
SQLStringConnect, and SQLExec in the VFP Help.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>

Need Basic Help

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Hi
You can use VFP as a front end.
For a starter try checking out:
http://msdn.microsoft.com/archive/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...pplication.asp
John
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> At my workplace our ERP software in on a VFP database. I have learned
> quite
> a bit about extracting data with the select statements from the VFP
> database. We are in the talks of converting the the SQL version of our
> ERP
> and I have some very basic questions since I am extremely new to the SQL
> world.
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>
|||Hi,
You'll get much better help posting questions like this in a FoxPro
newsgroup. Many FoxPro developers use SQL Server as a back end for their
applications and have much experience with it. SQL Server is a database
only - it has no front end to produce forms.
You can use the exact same SQL commands that you use in Query Analyzer to
access SQL Server via SQL Pass-through. Please look at SQLConnect,
SQLStringConnect, and SQLExec in the VFP Help.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>

Need advice on buiding a dynamic TSQL command to run against all DB's

Hi All,

I am putting together a series of TSQL statements to be used as part of a DB maintenance plan on SQL server 2005 on Windows Server 2003. I know that the statement itself works against a single database. The intention is to write one routine that will execute on all non-system DB's. Here are the system specs:

__

Microsoft SQL Server Management Studio 9.00.2047.00

Microsoft Analysis Services Client Tools 2005.090.2047.00

Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324

1447)

Microsoft MSXML 2.6 3.0 6.0

Microsoft Internet Explorer 7.0.5730.11

Microsoft .NET Framework 2.0.50727.42

Operating System 5.2.3790

__

So I've actually tried two methods. The first is without using sp_MSforeachdb

__

-- Declare variables

Declare @.dbname sysname,

@.cmd varchar(4096)

-- Select DB Names

Select @.dbname = min(name) from master.dbo.sysdatabases

-- Loop through each database.

WHILE @.dbname is not null -- <-- (NEED TO NOT INCLUDE SYSTEM DB'S HERE)

Begin

Select @.cmd = 'use ' + @.dbname +

'
-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1)

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1)

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in

('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

'

End -- (NOT SURE IF THIS IS IN THE RIGHT PLACE)

-- Execute the command

Exec (@.cmd)

__

The second attempted method was trying to use sp_MSforeachdb. I modified the following to successfully loop a simple print command to ensure that the looping occurs:

__

declare @.cmd1 varchar(4096)

set @.cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

exec sp_MSforeachdb @.command1=@.cmd1

-- THIS WORKS

__

However after many attempts, I haven't been able to get the syntax right to run my

statements abonve using this method. Seems very unforgiving with spaces and apostrophes.

Any advice is much appreciated. Thank you in advance.

Rick B

Hi Rick,

Try something like this:

--THIS CREATES ONE TEMP TABLE WITH ALL NON SYSTEM DATABASES

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM from master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in ('TableX','TableY','TableZ')
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DB's

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

I thinks this can do your trick

Regards,

|||

One of the ways I use to test the syntax of dynamic sql is to print the SQL rather than run it. Then you can see the outputted SQL and see if its correct and if not, where you have gone wrong

hope this helps

|||

Hey Luis

Upon execution against Master, I'm receiving the following error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'DBNAME'.

After a few tweaks, the following is the current syntax that I am using based off of Luis's feedback. Can anyone ID additional syntax errors? Thanks in advance. Rick B.

__

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

DECLARE @.ID INT, @.DBNAME VARCHAR(250)

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > 0;

WHILE @.ID IS NOT NULL

BEGIN

SELECT @.DBNAME=DBNAME

FROM TEMP_CURSOR

WHERE ID = @.ID;

EXEC(' USE ' + @.DBNAME + '

-- Delete data from table1 and related tables older than 2 Months

DELETE from table1 WHERE DATEDIFF(month, datecreated , GETDATE()) > 2;

DELETE from table1a WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1b WHERE messageid NOT IN (SELECT messageid from table1);

DELETE from table1c WHERE messageid NOT IN (SELECT messageid from table1);

-- Delete Selective Data from table2 older than 6 Months

DELETE from table2 WHERE DATEDIFF(month, AuditDate, GETDATE()) > 6 and tablename in (TableX,TableY,TableZ)
DELETE from table2a WHERE auditid NOT IN (SELECT auditid from table2)

--

-- Delete Data From table3 older than 2 Years

DELETE from table3 WHERE DATEDIFF(Year, ReferralDate, GETDATE()) > 2

-- Shrink all Non System DBs

DBCC SHRINKDATABASE(' + @.dbname + ', 10, TRUNCATEONLY)

')

SELECT @.ID = MIN(ID)

FROM TEMP_CURSOR

WHERE ID > @.ID;

END

DROP TABLE TEMP_CURSOR;

__

|||

Replace this statement:

SELECT IDENTITY(INT, 1,1) AS ID, DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

with this one

SELECT IDENTITY(INT, 1,1) AS ID, NAME AS DBNAME

INTO TEMP_CURSOR

FROM master.dbo.sysdatabases

where SID<>0x01;

Hope it works