Running SQL File (view + Procedure) Problem

Oct 15, 2005

I am try to run a .sql file

CREATE VIEW LastReport
AS
SELECT     MAX(ID) AS LastReport, RHost
FROM         Report
WHERE     (RComplete = 1)
GROUP BY RHost

CREATE PROCEDURE [dbo].[MembresAcces_Insert]
 AS
INSERT INTO membresAcces (login, passe, id_membresTypes) VALUES ('admin','admin',50);

.... 10 view and procedure like this one

i get an error view must be the first one

what is wrong ? running CREATE TABLE works perfectly

thank you

View 1 Replies


ADVERTISEMENT

Running A Batch File From A Stored Procedure

Jun 27, 2001

Is there a way to run/call a batch file from a stored procedure?

Or, is there a way to run/call a batch file from a trigger?

View 2 Replies View Related

DB Engine :: Log File Error While Running A Stored Procedure?

Nov 2, 2015

We try to run a stored procedure in management studio - and we see the following error - 
"
Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server. 

[SQLSTATE HY000] (Error 0)  The log for database 'Some-database' is not available. 

Check the event log for related error messages. 

Resolve any errors and restart the database. 

[SQLSTATE HY000] (Error 9001)  During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197). 

Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. 

[SQLSTATE HY000] (Error 3314)  During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1). 

Typically, the specific failure is logged previously as an error in the Windows Event Log service.

Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314).  The step failed. 

View 2 Replies View Related

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

TempDB Log File Running Out Of Free Space While Running DBCC CheckDB On Large Database

May 28, 2015

In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.

What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Transact SQL :: Running Totals In A View

Sep 14, 2015

I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.

There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.

I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.

Table: deposit    This table will hold all deposits

customerId    (string)
balanceType    (string)
transactionDate   (date) 
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)

The data set for the deposit table could be:

1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100

Table: withdrawal  This table will hold all withdrawals

customerId    (string)
balanceType    (string)
transactionDate   (date) 
amount     (decimal)
transactionText   (string)

The data set for the withdrawal table could be:

2 Bonus2 01-04-2015 -100  Needed 100
2 Bonus2 02-04-2015 -100  Needed 100
2 Bonus2 03-01-2015 -100  Needed 100
3 Bonus2 10-04-2015 -50   Took out 50
3 Bonus2 11-04-2015 -100  Took out 100

[Code] .....

Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate

customerId
balanceType
transactionDate
amount
Total
Text

The view must show this, when selecting all records:

1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100

[Code] ....

And a last view that will show distinct totals for each customer and balanceType

customerId
balanceType
totalBalance

1 Bonus -200
2 Bonus -300
2 Bonus2 -200
3 Bonus2 -550

View 8 Replies View Related

View Or Capture Complete Running Sql Text?

Feb 11, 1999

Hello:

We are running some test on our application which involves long-running transaction in an application that was purchased from a vendor. It is a PB
and we don't have access to all of the code. We are not sure why it runs so slow when we start this long_running process. We are not sure if they are using a stored procedure or not.

We ran updaet statistics becuase alot of data was added before we kicked off the process. We want to capture all of the sql code that is running so we can perhaps add indices.

Under EM, we click on the thread and can get a dialog box that shows the running sql code but the dialog box isn't expandable

THerefore I am wondering if there is a storec provedure or if someone has a script, to see what the complete text of sql is for a running proess? Any shareware tools?


Any information that you can provide will be appreciated. Thanks.

David Spaisman

View 2 Replies View Related

How To Stop Running DTS Package If My View Return Zero Record?

Jan 4, 2007

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How??? Can someone please help?? Thanks

View 10 Replies View Related

Dynamic View - Add A Column To Display Running Count In Table

Jul 22, 2014

I have a view created from only one table.

VW_Stats ( Tab_Name,Load_Status,User,....)

Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.

To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection

Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ

View 7 Replies View Related

Problem:long Running Validation When Pointing OLEDB Source To A View.

Jan 24, 2007

One of our developers has written a view which will execute completely (returns ~38,000 rows) in approx 1 min out of SQLMS (results start at 20 sec and completes by 1:10 consistently).

However, if he adds a data flow task in SSIS, adds an OLEDB Data Source and selects Data Access Mode to "Table or view" and then selects the same view, it is consistently taking over 30 minutes (at which point we've been killing it). I can see the activity in the Activity Monitor, it is doing a SELECT * from that view and is runnable the whole time.

If we modify the view to SELECT TOP 10, it returns in a short time.

Has anyone run into this problem? Any suggestions? It is very problematic, as if the views change we have to hack around this problem.

Thanks for any responses.

Jeff

View 5 Replies View Related

Running .sql File

Sep 11, 2005

i am creating a few dynamic tables and views , is there a way to execute a .sql file from asp.net (vb.net) ?

View 2 Replies View Related

Running Dts From Stored Procedure

Mar 21, 2004

Hi

I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.

Asp.net code;

Dim oCmd As SqlCommand

oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()

With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With

Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try

StoredProcedure;

CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO

Thanks
Rachel

View 4 Replies View Related

Running A DB2 Stored Procedure

Nov 3, 2006

I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.

How do i call a DB2 stored procedure?

View 1 Replies View Related

How To Check Whether A Procedure Is Currently Running...

Dec 13, 2007

Hi All,

I have a long running procedure (batch job) which will take quite some time to complete. I need to run a fixed number (configurable value) of copies simultaneously, i.e., either two copies or three copies at a time. (I don't want to use Service Broker for this purporse now)


For this I need to know whether this procedure is currenly being executed, and how many copies. Now I'm maintaing a flag in a table where this will get updated once the procedure starts and ends. This is not 100% reliable and need a manual check occasionally.


I would like to know, whether there is an easier method to find whether this procedure is being executed currently ?


Hope you are clear with my requirement.


SQL Server Version : 2005


Thanks in advance.
Regards
Babu

View 3 Replies View Related

Running Sql Script(.sql File) Through VB

Dec 4, 2000

how can a .sql file (containing script for creating tables etc.) be run in Sql Query Analyzer through VB.
The ultimate task is to create database in sql server 7.0 through VB.

thanks,
Sumit

View 1 Replies View Related

Running A Batch File

Mar 10, 2008

Hi

Is it possible using T-SQL to run a batch file located on different server, ie. PC1 has SQL Server on it, PC2 has the batchfile, I need to run the batchfile stored on PC2 on PC1.

Thanks

View 3 Replies View Related

Running A Batch File

Mar 11, 2008

Hi

I am trying to run a batch file stored on different server from the one that SQL Server is on using T-SQL. I am using the following syntax:

EXEC master..XP_CMDShell '\hit1DEVArchivingArchivingDataFiles.zipdata.bat'

But it doesnt seem to be working, it states that it is not recognised as an internal or external command.

Any ideas?

Thanks

View 1 Replies View Related

Problem Running Stored Procedure

Jan 3, 2005

Hi Guys & Gals

I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:

ALTER PROC sp_get_allowed_growers
@GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC'

EXEC(@SQL)
END
GO


and the code I'm using to execute the procedure is this:


public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
//Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;

}



The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:


Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.



Anyone with any ideas would be very helpful...

View 6 Replies View Related

Temp Db Is Full When I Am Running Procedure

Jun 29, 2000

Hi

i use to run the stored procedure as a task daily,
but since yesterday i am getting the error my tempdb is full ,and that
process is stopping.
does anybody know solution for this problem
thanks in advance

bye
ram

View 1 Replies View Related

Stored Procedure Only Part Running

Dec 10, 2001

I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-

Public Function CheckDigitCalc()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command

On Error GoTo TryAgain

conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn

cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute

MsgBox "Numbers created OK.", vbOKOnly

Exit Function

TryAgain:

MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"

End Function

The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.

View 2 Replies View Related

Running Automated Procedure To Email

Jan 30, 2012

I have an EmployeeReviews table...and basically..when the date is 14 days before their review date..I want to have an automated email sent out to the Reviewer.

View 1 Replies View Related

Error While Running Stored Procedure.

Aug 2, 2007

hi,
i'm using SQL server 2000. i'm getting the below error when i run a store procedure.
"Specified column precision 500 is greater than the maximum precision of 38."
I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).

View 2 Replies View Related

Findout If A Stored Procedure Is Running?

Feb 5, 2008

How can I find out if a stored procedure is currently being executed?

sp_who2 and sys.sysprocesses, Command, Cmd fields just gives me parts of the sql inside the stored procedure.

View 5 Replies View Related

Stored Procedure Running Slow In ADO.NET

Jan 9, 2008

We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks

View 22 Replies View Related

Running A Stored Procedure In Code

Apr 14, 2008

Hi,

I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.

I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)

I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.

Here is my SP


ALTER PROCEDURE uspSelectBarItemID2

(

@BarTabID INT,

@DrinkID INT,

@ReturnBarItemID INT OUTPUT

)

AS

BEGIN

SELECT @ReturnBarItemID = barItemID

FROM [Bar Items]

WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)

END

In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.

What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.

Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.

Cheers.

View 11 Replies View Related

Running A Sql Script File Through .net Code

Feb 20, 2008

can anyone tell me how to run the sql server script file saved on my disk through .net code and get the changes done in the database?
 

View 2 Replies View Related

Running A Script File In SQL Server

Dec 11, 2001

Hi guys,
I am a new person to sql. I wanted to know how you execute a script file in Query analyzer.
and also I wanted to know how you get the output to a file.
Thanks for the help . Please I need this info asap.

View 1 Replies View Related

Running The Script From A Batch File ( Bcp )

Jun 1, 1999

Exec master..xp_cmdshell "bcp sdnetpro..nbtorder11 out d:databtorder11.txt /c /t /Sservername /Usa /Ppassword"

Hi, this code run fine from within sql server query window. I want to put this code in a batch file and run the batch file.. it did not work. someone told me the revised code is :

bcp sdnetpro..nbtorder11 out d:databtorder11.txt /c /t,/r -Sservername -Usa -Ppassword"

This is not working. Please if anyone knows how to fix this, I would appreciate. One more thing, how can I confirm that the bcp has successfully done. do I have to create a log file and if so what is the code to create this log file.
thanks
Ali

View 1 Replies View Related

Running A Sql Server Mdf File From A Usb Drive

Apr 9, 2008

Hi All,
I've been trying to find the answer but been unable to. My question is it possible to create a SQL Server (2005) database in a usb2 drive? I have a large usb drive that i would like to store my database into instead of my local drive which is not that big.

Thanks

View 2 Replies View Related

Running A Vbs File As A Step Of An SqlServer Job

Dec 11, 2007

HiI have a VBScript file which runs fine when I click it or when I run itfrom a command prompt level.I added it as the 1st step of an SqlServer job and the job always failswith the error message"Executed as user: GVIANTAdministrator. The process could not becreated for step 1 of job 0xBE3A0405EB9F624282C1A9474AA9F719 (reason: %1is not a valid Win32 application). The step failed."Cn anyone help me understanding this error message.Thanks 1David Greenberg

View 2 Replies View Related

File Group Is Running Out Of Space?

Jul 21, 2015

I am currently SQL admin since our ordinary SQL folks are at summer vacation.

Yesterday SCOM alerted that File Group is running out of space on one not so critical database "The file group "PRIMARY" for the database "loganalys" in SQL instance "MSSQLSERVER" on computer "sqlserver2" is running out of space".

I logged in to the SQL server and checked the database in question. It is a very large DB with a size  of 577 GB.The storage on which the database files resides has 123 GB free space so that isn't the issue.

The database is set to autogrowth 1 MB at a time (unrestricted) so that doesn't seem to be the issue either.However, in the database properties under General, it says Space avaliable: 570 MB.

I guess that this may be the issue, that this is under some treshold for SCOM to alert on.I have looked at the other databases and they have everything between 0,25MB to 270MB space avaliable (they are all set to autogrowth 1 MB, unrestricted).

I am not sure what this means and if I have to do something about it?

View 6 Replies View Related

How To View The .LDF And .MDF File

Aug 27, 2004

Dear Members

How to View the .LDF and .MDF file

Can anybody tell me how to see the details of .ldf and .mdf file which is there in Data directory under MS SQL installed directory

Is there any viewer of these files.

I need help on this becuase I want to knwo what sort of activites are going on a particular database.

Regards
Praveen Kumar Pandey

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved