hi everyone
i want to execute a sql insert statement everyday at 7 AM .
how to do this ?
here is the query
insert into attendance
{
date,
accountId,
clockIn,
clockOut
}
values
{
now, // current date
accountId, // employee id
null,
null
}
thanks
I need to use FTP task in DTS ( or any other way), but the problem is, the source file name is not constant. It changes every day with a date extension appended to the file name, like PO_2002_6_12_PM.zip. How do I create a task task to grab this file daily as a scheduled task? Any help is appreciated.
thanks.
Di.
i want to know how can i run a CREATE BACKUP stored procedure using windows 2003 scheduled task on a specefic time?
View 5 Replies View RelatedWe just installed SQL Server 2005. I have 3 jobs within SQL Server 2000 that i would like to import into the new server howeber i have no clue on how to achieve this. In fact I dont even know how to make a new job within SQL Server 2005 at all? Can someone please give me the steps on how to do this.
BTW the old job accesses launches a .EXE application every day, once a day at 5pm and the other job launches the same applicationb but at 5am. I had to have 2 seperate jobs because I couldnt figure out how to pass a different parameter for the two launches.
Thanks.
Hello Experts,
I found some similar threads and guides but they didn€™t help me with my special problem.
I converted a dts package (built in SQL 2000) to SQL 2005. Right now it€™s a legacy package.
(I tried the Tool Microsoft SQL Server 2000 DTS Designer Components to open the package. It€™s going well)
I would like to build a scheduled job which runs this dts package.
In SQL 2000 you can right click on the package and create the job.
SQL created string like this:
Dtsrun ASDFHJKSF56A4DFSLAKDHFJKS65646ASDFHSF (very long sting, it€™s the ID of the dts package)
How can I make something like this in SQL 2005?
Where I can get the ID of a dts package from?
Best Regards,
Alex
p.s.
- I red the thread from Jamie Thomason and will directly mark as answer after I get a answer
- of cource I will delete my thread too if I overlooked a thread with the same issue
Hi Guys,
Any assistance you people can provide will be considered grateful. I€™m in search of a generic cookie cutter snippet of code that simulates the creation of an Oracle DBMS_JOB, but I need it for SQL Server 2005.
Beneath, highlighted in red, is how we create a DBMS_JOB in Oracle, but I need the snippet of code to accomplish the same objective on SQL Server 2005. In Oracle, we deploy our scheduled jobs using anonymous pl/sql which is written in black, as shown beneath. To facilitate conversion of our project scheduled task it would be great if someone can assist me in translating the code beneath written in black and red.
REM spool schedule-jobs_a.sql.log
REM declare
REM jobno NUMBER;
REM cursor job_cur is select job from user_jobs
REM where what like 'dbms_utility.analyze_schema%';
REM begin
REM
REM for job_rec in job_cur loop
REM dbms_job.remove(job_rec.job);
REM
REM end loop;
REM dbms_job.submit(jobno,what => 'dbms_utility.analyze_schema(''&&anal'',''compute'');', interval => 'TO_DATE(SUBSTR(TO_CHAR(TRUNC(SYSDATE+1)),1,10)) + 360/1440', next_date => SYSDATE);
REM dbms_output.put_line( 'Next job '||jobno );
REM exception
REM when others then raise;
REM end;
REM /
PS, I€™m not looking for instructions to create a Scheduled Job through Microsoft SQL Server Management Studio, but rather, I need T-SQL code to create a Schedule Job to run daily at some period of time and run some snippet of code.
Thanks,
Vikram
I have a table (currently with no constraints or relationships) with two columns:
Employee_CodeEarned_Leave_Balance
The leave balance should be updated automatically on a daily basis using a formula. I understand this can be done using stored scheduled procedures. To give you a full and clear picture, this is what I need:
IF [Employee_Code] LIKE 'KUW%'
[Earned_Leave_Balance] = (Today - [Employment_Date]) * (42 / 365)
ELSE
[Earned_Leave_Balance] = (31/12/CurrentYear - [Employment_Date]) * (30 / 335)
hi everyone
i want to execute a sql insert statement everyday at 7 AM .
how to do this ?
here is the query
insert into attendance
{
date,
accountId,
clockIn,
clockOut
}
values
{
now, // current date
accountId, // employee id
null,
null
}
im beginner in sql server, so please elaborate with examples
thanks
I need to have an way to automatically submit a sql query in Sql
2000 - the query joins several lables and exports the info to a file.
Right now I've been doing it interactively in Query analyzer and
using the export to option... I can't use DTS (query way too
complicated!) and the file created is >255 lrecl so that seems to be
a big problem too!
Any suggestions? Save me from this same predicament on New Years Day!
Thanks and Happy Holidays! :(
Hi,
I was asked this question by someone and I didn't know the answer (hence I'm here and hoping someone else does!). They have an SQL database which is accessed via a web front end (not sure of the language) to return query results. Sometimes the queries take a long time (?) to return, whilst at other times the same query is very much faster. They have heard that it is possible to setup a query in such a way that if it takes more than a certain time to run it will generate an alert. This could then be scheduled as a job to aid diagnostics. Anyone know of a way to do this - measure the time taken to run a query and generate an alert if longer than a given time.
Thanks.
I'm using SSMS 2012. I have multiple queries that are run daily(I also have a few that are run every Monday) that I want to schedule to run at midnight and export to a new xls file on the network drive to be used the next morning. Is there something that I can add to each query to accomplish this or would I have to set something else up to call the queries?
View 3 Replies View RelatedI can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies View RelatedHi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
I created a query in Sybase SQL Anywhere and used the following column generation instructions. Could someone help with general guidelines as far as using it in SQL Server 2005 Express?
Thanks.
"(if C_Base_Data.PLDate is not null then (Floor(cast((DATEDIFF(day, c_Base_Data.PLDate , _Ref_Date.refdate)/365.25)as Decimal(5,1)))) else 0 endif) as Current_Age,"
"(if Current_Age = 0 then 'T.unpl' else 'Planted' endif) as T_Unpl"
hi , I new in sql reporting services.
I have a report with a query . now for every record it printed I need to create another query to another table and to some calc and print the value . how do I do this .
in other reporting program that I used before, they allow me to create multi body report. each body report allow me to defined a query .
thks
I want to create a empty new table (if it doesn't exist) when the the page/program starts. Maybe with copying field names from other table.It would be nice to have its name personal for each user. But I don't know if it possible.Also, what event is best place for this. Perhaps like this:dim username as string = Page.User.Identity.NameDim tablename as stringtablename= shopping_basket+usernamecreate from t_product new @tablename
View 3 Replies View Related
Hi All
In the following script
What "U" stands for: IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULL
Regards
Abdul
USE AdventureWorks
GO
IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULLDROP TABLE dbo.sample_table
GO
CREATE TABLE dbo.sample_table
(c1 int NOT NULL,
c2 char(10) NULL, c3 datetime NULL,
CONSTRAINT PK_sample_table PRIMARY KEY (c1)
)
GO
Hi Guys
Please help , I have a table that has been created like this :
CREATE TABLE [dbo].[Table1] (
[Field1] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[Field2] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
) ON [PRIMARY]
Could anyone tell me what this means exactly
"COLLATE SQL_Latin1_General_CP1_CS_AS"
Plus I am unable to find anything on BOTH views and triggers , can you create a trigger on a view??
Thanks
stepdefinition has
steptype
flowid
stepid
task has
taskid
flowid
stepid
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
here's a visual
http://www.filecabin.com/up1/1144249279-task.gif
Hello,
With Oracle, we can create a table as following :
CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ....
With MSSQL, can we do the same thing ?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO
CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO
CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO
----------------------------------
CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]
SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)
END
SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)
Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS
INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'
SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1
DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END
SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(3),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)
SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME
SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY
Kapil Arya
I'm an Absolute beginner to SQL
I have a table of baseballTeams with columns baseballTeamName and a baseballTeamID
A have another table (baseballGames) which consists of baseball games which includes a homeTeamID and a visitingTeamID.
Both of these columns are linked to the baseballTeamID from the baseballTeams table
I have been successful at joining the two tables and Selecting the baseballTeamName from the baseballTeams table for either the hometeam or
visiting team as shown below:
SELECT gameID, homeTeamID, baseballTeamID,baseballTeamName
FROM baseballGames, baseballTeams
WHERE baseballGames.homeTeamID=baseballTeams.baseballTeamID
...but I cannot figure out how to select BOTH the homeTeam name and the visitingTeam name from the baseballTeam table
Any help would be greatly appreciated
Thanks in advance
Larry
Code Snippet
CREATE PROCEDURE GetSearchResults
@ParameterValue @varchar(max)
--WITH ENCRYPTION
as
select *
from OPS_Projects
select *
from OPS_Clients
go
I have this stored procedure that has one Parameter ....that parameter value will be a value that can be matched from eithier one of these tables but i am not sure how I need to write query...my logic I would like to read like this
Select * From OPS_Projects Where ThisData = ParameterValue
If this query returns data then, just return that data If not run next query
Select * From OPS_Project Where ThisOtherData = ParameterValue
If this query returns data then, just return that data If not run next query.... and so on....
Is this the best way to do to create a search query???? Any Help I am open for suggestions! Thanks!
Code Snippet
Declare @DBName as varchar(100)
Declare @Query as varchar(8000)
SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue
Set @Query ='
SELECT
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice),
a.MoeenName_L1
FROM
ReciptItems LEFT OUTER JOIN
' + @DBName + '.dbo.Categories AS a
ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
a.MoeenName_L1'
Exec (@Query)
Hello everybody,I want to ask how to create query use the solve the problem of this..Example : I want to display the month january between february... Sorry if my english is not good...Thank you...
View 3 Replies View RelatedHi,I was wondering if someone can help me. I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them. The columns inside them have the exact same design. For purposes sake called 'column1', 'column2', 'column3' and 'column4'. Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records. Can someone specify the syntax I need for this. I'm pretty sure it's a union query I need so am scouring as I type this also. Many Thanks Robert
View 5 Replies View RelatedI need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database. Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?
View 1 Replies View RelatedHi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe
hi, here I create a cross tab query in access 97 this is the code
TRANSFORM Count([1].ORD_NBR) AS [The Value]
SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR]
FROM 1
GROUP BY [1].SRMGR_NAME
PIVOT [1].ASR_SUPP;
I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK
My question is how can I implement crosstab query in sql server 6.5?
any one can help I really do appreciate
Ali
I need to use mssql to create a ranking of some kind. This is the situation:
I need to assign position to a list of students based on thier scores. e.g
Student Score Position
StudentA 56 4
StudentB 78 1
StudentC 66 2
StudentD 56 4
I need to create the positions based on the scores of the ctudents.
I will appreciate any assistance.
Thank you.
I created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.
However, I can't figure out how to use it?
For example
select functiona (parameter) as data_src
this returns the "from" statement above
I then try to run
select * data_src
So how do I reference the contents of data_src in the select?
Thanks for any help