Executing SQL Statements In Another Database

Apr 9, 2008

I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?

I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.

Heck, even if I execute this code through SSMS it does not work as I need it to:

USE OtherDatabaseName;
EXEC DBA.dbo.usp_CreateSomeAdminObjects

I confirmed this by adding these statements to the proc:

/* Gets list of tables from the current database */
SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name

SELECT DB_NAME();

These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.

Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.

View 10 Replies


ADVERTISEMENT

Executing Sql Statements

Jul 23, 2005

When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?

View 1 Replies View Related

Executing SQL Statements Through Variables

Sep 23, 2002

Hi,

I try to run sql statement by placing them in a variable as given below but receive the following error message

set @sql_string= 'BACKUP DATABASE '+ @temp_db+' TO '+ @bk_device+' WITH FORMAT,INIT, NAME = '''+ @bk_name+''''
EXECUTE IMMEDIATE @sql_string


Error Message

Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'IMMEDIATE'.
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'BACKUP DATABASE DTSTEST TO bk_dev1 WITH FORMAT,INIT, NAME = 'Full backup''.


Thanks
John Jayaseelan

View 2 Replies View Related

Executing Large SQL Statements

Apr 15, 2004

Hello,

I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().

The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.

I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.

Thank you in advance!

-Sergey

View 1 Replies View Related

Executing Statements W/o Explicit Transaction

May 30, 2008

Hi

What is SQL Server 2005 default behavior with statement like this:


SELECT * FROM dbo.Products

Will it take any shared locks on Products table? Is it different than:


BEGIN TRAN



SELECT * FROM dbo.Products WITH NOLOCK

COMMIT



TIA

View 2 Replies View Related

Check If A Remote Server Is Online/available Before Executing A Series Of Sql Statements

Sep 30, 2007

Hi,

We have unreliable circuits connecting our office to remote sql servers. At our office I need to delete/insert a bunch of records from those remote locations. Since the connection is unreliable what is the best way to check if a server is available before executing delete/insert statements. A few points to consider:

1. I want this to be done in SQL
2. I have a cursor that cycles through a tabe that contains the name of the linked servers. The cursure is used to delete/insert a bunch of records from each remote server.
3. If one server is unavailable the stored proc should continue to the next sql server (next record in the cursor.)


Thanks,
Bradley

View 3 Replies View Related

Right Code Statements Of SqlConnection &&amp; ConnectionString For Connecting A Database In Database Explorer Of VB 2005 Express?

Feb 14, 2008

Hi all,

In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................

Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure
........................
etc.

Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?

Please help and advise.

Thanks,
Scott Chang

View 6 Replies View Related

Executing On A Different Database.

Nov 8, 2006

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?



View 1 Replies View Related

How To Get Database Size Using Sql Statements?

Jun 15, 2004

How can I get database size using sql statements or system store procedures? Thanks in advance!

View 2 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Should I Add Lock Statements To My Database Write Querys

Jun 5, 2008

Hello,Could some clarify for me? I’ve been told that I should add  lock statements  to my database write querys (update, delete, insert). I’m not sure If it’s necessary though. The only documentation I’ve found so far is how to implement a lock statement with threading but I’m not using the threading namespace. What best practice and how would I implement this without threading or should I be using threading?Thanks in advance!
 

View 4 Replies View Related

Retrieving 'CREATE' Statements For Database Objects

May 6, 2008

MySql has a statement like:
SHOW CREATE TABLE tablename;

that returns the precise CREATE TABLE statement for the specified tablename.

Sql Management Studio also allows scripting Create Statement for any object by right-clicking it.
But I want to do this programatically, and fetch CREATE statements for Tables, Procedures & Views.

How can I retrive CREATE statements for Database objects progrmatically in Sql Server???

View 9 Replies View Related

Executing SP_SpaceUsed For Tables In A Other Database With EXEC

Jul 20, 2005

HiI'm executing SP_SpaceUsed in a stored procedure like this :Exec ('SP_SpaceUsed '+ @table)This works great but when i want to execute it for a table in a otherdatabase i'm running in to troubles. Things i tried is this :Exec ('USE <DB> ; SP_SpaceUsed '+ @table) -->not working (uncorrectsyntax)Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @table) -->not working(uncorrect syntax)Exec ('SP_SpaceUsed <DB>.dbo.'+ @table) --> not working (uncorrectsyntax)Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @table) -->not working(uncorrect syntax)Could someone give me a clueThanx,Hennie de Nooijer

View 1 Replies View Related

Help In Creating Insert Statements For Retreiving Data From Database

Apr 14, 2008



Hi all,


Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.

Thank you

View 5 Replies View Related

Transact SQL :: How To Find Stored Procedures Without NOLOCK Statements In Database

Jun 15, 2015

I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.

View 8 Replies View Related

Executing Northwind Script To Create The Database On SLQEXPRESS Edition

Jan 19, 2006

I have Visual Studio 2005 Beta 2.00 install which have installed the SQLEXPRESS server.
I have the Script for the Northwind database which I need to run some demos but I can figure out how to execute this script.
Can someone tell me what todo or how to attatched the database to this server. I also have a copy of the database already created. But When I tried login in into the database I get an error login fail.
Which is the default user amd password for the northwind database?
Tia
Charles

View 1 Replies View Related

SQL Security :: Database Level Audit - Query Parameters For SELECT Statements

Aug 31, 2015

I have setup a Database Audit Specification as follows:

Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public

Now, when I perform a SELECT query with a bound parameter such as:

SELECT * FROM myTable WHERE name='queryname'

What I see through the Audit Logs is something like:

SELECT * FROM myTable WHERE name='@1'

I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using

(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.

View 9 Replies View Related

SQL Server 2014 :: Stored Procedure - Add A Record To Local Database Executing In Cloud Environment

May 20, 2015

Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?

View 2 Replies View Related

SQL Statements

Aug 17, 2006

Hi guys,
Need some help in some query processing...
Here goes,this is the results i have obtained so far
|id |cat | name |
1 .....a... apple
1 .....b .. banana
2 .....a ...austria
2 .....b ...brazil
2 .....c ...china
3 .....a ...abraham
3 .....c ...clinton

Column cat can have at most 3 different types of values..{a,b,c}

I wanna write a query such that the result comes out as such
<(....id ......a .......b......... c..) corresponding to these fields.>

|Col 1| Col 2 | Col 3 | Col 4|
....1 ...apple ...banana
....2 ...austria .brazil... china
....3 ...abraham ..........clinton

Anyone can help...thanks guys

View 1 Replies View Related

If Statements In SP

Sep 21, 2006

I use SQL Server 2005.I want to create a SP "Search". With this function a user can serach the member database on several criteria: age minimal, age maximal, name, city..BUT these criteria do not necessarily have to be defined by the user, so it might be that a user searches for all members whose age is between ageMin and ageMax leaving the name and city criteria empty.So in my SP I have to check whether these values are empty or not. If a parameter is not empty I have to add it to my selection query.. BUT, I know that SQL Server makes an execution plan, and understood that when you use if-statements the procedure needs to be recompiled every time?Is this true? If so:how does this work then?Is there another way for me to still be able to create this SP?

View 2 Replies View Related

Help With If-Else Statements In SQL...please...

Dec 11, 2006

Hello guys. How is this declared in SQL?
Select * FROM my_table
     if my_column = "1" UPDATE my_table SET  my_other_column= "a"       ////my_column and my_other_column belong to my_table
     else if my_column="2" UPDATE my_table SET  my_other_column= "b" ////my_column and my_other_column belong to my_table
   

View 4 Replies View Related

Sql Statements Per Second

Jan 27, 2008

Hi, I would like to know the number of SQL statements per second being generated by my web site. How can I know this?  Best Regards,MeeNge  

View 6 Replies View Related

If Statements

Apr 8, 2008

 Hello I have an SQL Data Source  i want to compare 2 dates if one is equal to or less than the other i want to return either a string or a true i have been trying combinations for about an hour and it's getting a little frustrating i;ve tried searching for an example but cant find one so somesthing like Select ValuesWhere Date1 <= Date2 Return "String"(orTrue?) also IF date1 <= Today() Return "String OR True" thanks Chris 

View 3 Replies View Related

Sql If Statements

Feb 22, 2006

I have some trade data. One colum is tran_status_mtf. within that column is "settled/traded", "cancelled", and "revised". I want to write and SQL statement that says if the trade is "settled" or "traded" display "A" in my output. How do I do this? I am new to SQL. Thanks in advance guys!!!

View 14 Replies View Related

IF Then Statements

Jun 3, 2008

I need to create a brief statement in Design View for SQL 2000.

I successfully wrote "isnull(dbo.Data.Parcel,'')in the Column in Design View, which would ensure that a null value would be listed as a blank. Now I need to have dbo.data.status show as 'C&G' in any instance of data, otherwise it will be listed as blank. I tried writing "isnotnull(db0.data.status,'Homestead', '')", but it does not work.

Is there a simple statement I can write in "column" of design view that can get the results that I need?

-Steve

View 4 Replies View Related

Using Sum In Sql Statements

May 21, 2007

I have written an sql statement thats using sum. The problem that I have is that when the value is zero it returns Null. Is there a way that I could return zero instead of null.

View 5 Replies View Related

SQL If Statements

Oct 19, 2007



I am totally new to creating IF statements inside stored procedures. I am passing some parameters from a ColdFusion form to a SP and if the form is empty for the small date I get an error. I would like to be able to check in the procedure to see if the parameter is empty and if it is set a default value for it. Here is my code.
@JOB1,
@COLLEGEDATE smalldatetime,
If(@COLLEGEDATE <> ''")
@COLLEGEDATE smalldatetime,
ELSE
@COLLEGEDATE = getDate(),
RETURN
@JOB2

View 3 Replies View Related

Different T-SQL Statements?

Sep 16, 2006

Hi, i just want to know if the T-SQL statements in SQL server 2005 are different from SQL Server 2000???

If they are different, where can i find the Server 2005 T-SQL Statements?

View 6 Replies View Related

SUM And Iif Statements

Apr 16, 2007

Hello,



I have a report with 1 field that I need to sum into 2 different textboxes based on another field's value.



Specifically, Is it simply doing something like this below?



=Iif(Fields!iBB.Value=1, Sum(Fields!Billed.Value)

=Iif(Fields!iBB.Value=0, Sum(Fields!UnBilled.Value)



Which will not get past intellisense checking, -or- do I have to do something different here?



Thanks in advance.

View 10 Replies View Related

Help With Insert Sql Statements...

Dec 15, 2006

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.
Is there a way for the insert statements to be merged so that I won't be executing two statements? 
Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)
Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo   (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)     
 
Thanks.

View 1 Replies View Related

Several Select Statements?

Jan 16, 2007

Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View 5 Replies View Related







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