create and drop table via vb

Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how...
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":

Dim db as ADODB.Connection
'... open connection to database

Dim strCmd As String

strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"

db.Execute strCmd

Thank you in advanceThe code you are using works on an Oracle database. The syntax for SQL Server is "SELECT...INTO...FROM".

SELECT * INTO newTable FROM oldTablesql

Create and Administer SQL Server Agent Jobs via Sprocs?

Hi,

Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?

I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.

Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')

Try this and let us know how it work

Regards|||Thanks Tommy,

Going to give this a shot this afternoon. I'll let you know how it turns out.

CREATE a Temp Table via "EXEC (@SQL)"

I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:

--------------------------
-- Get all SubquestionIDs for this concept
--------------------------
DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR
SELECT QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
FROM RotationMaster AS RM
INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
WHERE RM.Study = @.Study
GROUP BY QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
HAVING QGDM.SubquestionID <> 0

--------------------------
-- Dynamically create a Temp Table to store the data, simulating a pivot table
--------------------------
SET @.Count = 2
SET @.SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'

OPEN curStudySubquestions
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = @.SQL + ', Col' + CAST(@.Count AS VARCHAR(5)) + ' VARCHAR(10)'
SET @.Count = @.Count + 1
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
END
SET @.SQL = @.SQL + ', ShowOrder SMALLINT)'

CLOSE curStudySubquestions
PRINT 'Create Table SQL:'
PRINT @.SQL
EXEC (@.SQL)
SET @.ErrNum = @.@.ERROR
IF (@.ErrNum <> 0)
BEGIN
PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!'
RETURN
END
PRINT '*** Table Created ***'

-- Test that the table was created
SELECT *, 'TEST' AS AnyField FROM #AllSubquestions

The line PRINT @.SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):

CREATE TABLE #AllSubquestions
(Col1 VARCHAR(100),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
ShowOrder SMALLINT)

However, the SELECT statement to test the creation of the table produces this error:

*** Table Created ***
Server: Msg 208, Level 16, State 1, Procedure
sp_SLIDE_CONCEPT_AllSubquestions, Line 73
Invalid object name '#AllSubquestions'.

It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@.sql).

I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.|||The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly :)

What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.

Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:

Col1 Col2 (Question1) Col3 (Question2) Col4 (Question3) Col5 (Question4) ShowOrder
-------------------------------------
Concept 1 25 86 15 47 1
Concept 2 35 89 54 72 2
Concept 3 69 17 48 36 3
Norm A 50 40 40 30 100
Norm B 54 38 42 36 101

This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.

Crearting a new table via SPROCS

I supply a parameter @.TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@.TEMPTABLE" as the name of the table instead of the value of the parameter.

Any ideas would be appreciated.

Thank you.

CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,

your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...

you need to build an cmd-string and execute it. something like this:

declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S

markus

CR as blank report

1. is it possible to create just blank report and pass the value for it via VB?

2. is it possible that in that blank report i want to use ORDER BY certain condition that determined by condition that user want..ex : by date, by supplier, etc

can anyone explain me how to do it..
thx..Why would you want to design a report that has no fields and no data connection?
You use Crystal to establish the data structures used by the report, and how they will be grouped, etc.
How do you propose to test the report to ensure that it work properly, if you don't design it first?

dave|||thx springsoft
sorry for my unclear question

until now..i always use CR to design the report..like what field to display, connection, group by, etc..and i just use vb just to call the report to display (FYI : i'm using just CR control in VB)..using selectionformula which i realize it's took some time if there's alot record..i'm planning using recordset and pass it to CR so that the value is just came from my query in VB (not take some time anymore to display since the 'selection' thing already done at my query)..is it true what i assuming? or is there any better method??

a couple days ago..i read about to 'code' CR with VB (maybe like using ADODC and ADODB in VB) but i don't understand how to do it in VB..

what i mean blank report is..i created report in CR that just use ParameterField..and fill its value from VB..so whenever someone open .rpt..it only show 'blank' report..

now..i'm gonna use CR to display a record that exactly from same table..but different in 'ORDER BY' thing..if i'm using 'old' way..i'm gonna make a several report for several ORDER BY..this isn't a good way
since i'm new at this point..could u or anyone suggest or guide me how to do this and other stuff..

hope u understand what i'm saying
thx|||See my post in this link:

http://www.dev-archive.com/forum/showthread.php?t=309534&highlight=ado|||thx malleyo
that's not what i mean but it certainly can be useful for me

let me clarify my question
first i create a .rpt using CR which the value in detail section will be from parameter field that i pass later through VB

so the question will be..
1. if i use parameter field..can the .rpt be sort by or group by certain field that controlled by my VB app?so i don't need to create alot .rpt if the sort condition is alot too
is it possible to do that? any suggestion how i do it?

thx|||What about this:

The following code can be used to decide the sort order of your rows through VB Code:

1. First, In Crystal Reports, create a Formula Field and add 1 column (a String column works best).
2. Insert a Group and choose your Formula for the GroupBy.
3. Now, you can comment out the Column you entered, or you can leave it there, your choice.
4. In VB, add this line of code, substituting {ttxFileName.ColumnName} for the column you want to sort by: Report.FormulaFields(1).Text = "{ttxFileName.ColumnName} ". The FormulaFields can only take a long for the index, so you need to know what number your Formula is so you don't replace the wrong one. The numbers start at 1 and are incremented based on the order the formulas were created (they are listed in chronological order).

NOTE: This example works with CR 8.5 and VB6, you may have to tweak it a bit if you're using different versions.|||thx malleyo
sorry for late reply..coz it's holiday here and i don't have internet at home :)
so that's how it works for ORDER BY problem..

still have another problem..
i'm want to restricted .rpt just to display certain record like SupplierID = 'Erick'
1. is it the same way as previous just different at record/group selection formula section?
2. can it be done using same report as previous so that i'm not working with alot of .rpt?
3. a couple weeks ago..i realized that using .selectionformula method is not a good way to display certain data(like just display where SupplierID = 'Erick')..any suggestion for this one?

thanks|||If you only want to display certain criteria, use a conditional suppress.

To specify the condition at runtime, create a formula like you did from my previous post. Like this:
'Erick'
Then set the Conditional Suppress for the field or section you want to suppress. Like this:
{FieldName} <> {FormulaYouCreated}

Whenever the FieldName matches what you plugged into the formula from VB, the field will be displayed.

An alternative is to set the {FormulaYouCreated} to this:
{FieldName} <> 'Erick'
and the Conditional Suppress formula to this:
{FormulaYouCreated}

If you do it the second way, you can leave the {FormulaYouCreated} blank and it will display all the records.

Doing it the second way, this will show only records for 'Erick':
Report.FormulaFields(1).Text = "{FieldName} <> 'Erick' "

Doing it the second way, this will show all records:
Report.FormulaFields(1).Text = "//Comment as Placeholder"

Doing it the second way, this will show records for everyone EXCEPT 'Erick':
Report.FormulaFields(1).Text = "{FieldName} = 'Erick' "|||thx alot malleyo

i have to give it a try about this one..