Passing Object Names To Stored Procedures

May 31, 2001

Can anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?

Below is my poor effort at the SQL.

Thanks.

CREATE PROCEDURE InsertOrderLine

@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)

AS

DECLARE @SQL VARCHAR(1000)

SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'

GO

View 1 Replies


ADVERTISEMENT

Passing Variable Table Names To Stored Procedure

May 5, 2000

I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.

Select * from @Passedtablename. This won't work. Any insights.

View 1 Replies View Related

Get Stored Procedures Parameter Names And Types...

Jun 1, 2006

Sorry if I haven't choose appropriate forum for this question.

I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?

It's windows application.

View 3 Replies View Related

Passing Arrays To Stored Procedures

Feb 23, 2004

Dear all,

i want to know how i can pass multiple values in the form of arrays to a stored procedures.

the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:


Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
'
'cmdInsSlabHmst
'
Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)

'Passing multiple values to the procedure with the help of ~ sign

Dim strCode As String
Dim i As Integer
'Dim dblSlabRate As Decimal
'Dim dblSlabFrom As Decimal
Dim strSlabRate As String
Dim strSlabFrom As String
Dim strSlabRateP As String
Dim strSlabFromP As String
Dim intCntr As Integer
'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
'OleDbConnection1.Open()
'strCode = cmdInsSlabHmst.ExecuteScalar
'OleDbConnection1.Close()
For i = 0 To dgSlabDtl.Items.Count - 1
If i = dgSlabDtl.Items.Count - 1 Then
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
Else
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
End If
strSlabRateP += strSlabRate & "~"
strSlabFromP += strSlabFrom & "~"
intCntr += 1
'If dblSlabRate <> "" And dblSlabFrom <> "" Then
'InsDtl(strCode, dblSlabFrom, dblSlabRate)
'End If
Next
If strSlabRateP <> "" And strSlabFrom <> "" Then
With cmdInsSlabHmst
.Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
.Parameters("iREMARKS").Value = txtRemarks.Text
.Parameters("iSLABFROM").Value = strSlabFromP
.Parameters("iSLABRATE").Value = strSlabRateP
.Parameters("iNOOFRECORDS").Value = intCntr
End With
OleDbConnection1.Open()
cmdInsSlabHmst.ExecuteNonQuery()
OleDbConnection1.Close()
End If


to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.

i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.

regards
subhajit

View 1 Replies View Related

Passing Parameters To Stored Procedures

Feb 17, 2005

Hello,

I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.

I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.

Thanks for your help, Gary

Here is the web code:


Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)

cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))

cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue

conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()


Here is the stored procedure:



CREATE PROCEDURE s_ItemLookUp

@Field AS VARCHAR(50),
@Value AS VARCHAR(50)

AS


SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO

View 4 Replies View Related

Passing An Array To A Stored Procedures

Feb 17, 2005

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !

View 3 Replies View Related

Passing Arrays To Stored Procedures?

Feb 20, 2003

Someone recently tried to tell me that it is possible to pass an array to a stored procedure.

I have tried creating procedures with the 'table' datatype for the parameters but the attempts fail with a syntax error.

We currently workaround this limitation by passing the equivalent of an array to temporary tables and selecting from those tables as needed within our stored procedure but if we can circumvent this by passing an array, we'd definetly like to try that instead.

Thanks in advance for any advice you may share.

View 4 Replies View Related

Passing Multivalue In Stored Procedures

Jun 7, 2007

Hi guys,

I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.



Thanks in advance.

View 10 Replies View Related

Passing Null Values To Stored Procedures

Aug 29, 2006

Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!

View 4 Replies View Related

Passing Empty Parameters To Stored Procedures

Aug 9, 2000

Hi

I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.

IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..


CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I


Thanks
VENU

View 3 Replies View Related

Passing Parameters To Extended Stored Procedures

Jul 20, 2005

I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!

View 1 Replies View Related

Using ADOMD.NET Set Object In Stored Procedures.

Mar 18, 2008

Hallo everyone,
I have a problem passing the [Set] object to a stored procedure that we use in a calculation of our Cube. Following Mosha Pasumansky indications in http://www.sqljunkies.com/WebLog/mosha/archive/2007/04/19/stored_procs_best_practices.aspx I avoided to use Tuples.count and Tuples[Index] but stll receive an €œInternal Error: An unexpected exception Occurred€?.
The error arrives randomly (sometimes the sproc works perfectly, like its €œsister€?, that we were forced to implement with arrays and strings, using the old SetToArray and SetToString mdx functions).
Our particularity is that our program is in Vb.Net. and here is the incriminated cycle:

Public Function Redditivita2(ByVal flusso0 As Double, ByVal setRateAMesi As [Set], ByVal fin_sca_map As Double) As Double

...
Dim tp As Tuple
Dim ntp As Long
ntp = 0
i = 0
For Each tp In setRateAMesi
If ntp Mod 2 = 0 Then
numAggr = CLng(tp.Members(0).Name)
mesi(i) = numAggr Mod 1000
proroga(i) = Int(numAggr / 1000)
If (mesi(i) > 120 Or proroga(i) > 120) Then
Err.Raise(vbObjectError + mnTroppeRate, , "Rateazioni a più di 120 mesi di durata o di proroga non previste - contattare Mappamondo Informatica s.r.l. se richieste")
End If
nrateamesi(i) = MDXValue.FromTuple(tp).ToDouble
Else
nmaxrateamesi(i) = MDXValue.FromTuple(tp).ToDouble
i = i + 1
End If
ntp = ntp + 1
Next

The set contains a cross join between a dimension and two measures (in the calculation we have: OLAPExtFunctionOpt!Redditivita2([Measures].[FLUSSO_0],NonEmpty(CROSSJOIN(.children, {[Measures].[RATA], [Measures].[RATA_MAXI]})),[Measures].[SCARTO_PES_GG])).

I installed SP2 and Cumulative HotFix. 3152. Does anybody has any idea? Has the problem any connection with msmgsdrv versions? Thank you very much for attention!

View 1 Replies View Related

TSQL Script For Finding Column Names In Stored Procedures

Feb 23, 1999

Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?

View 1 Replies View Related

Parameter Passing - Table Name & Column Name In Stored Procedures

Mar 9, 2001

How can a Stored Procedure use a variable table name and column name ?

The statement :-

SELECT @columnname FROM @tablename

gives error "Line 5: Incorrect syntax near '@tablename'."

I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).

If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,

i.e. SELECT @columnname FROM employeetable gives :-

surname
surname
surname
surname
surname
surname


How can I get the procedure to accept a variable table name and column name ?


I thought one way to do it would be to use the 'EXEC' statement :-

SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)

but this gives error : "Incorrect syntax near the keyword 'FROM' "

although the following table works :-

SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)

The problem seems to be mainly with variable column names.

Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)


Any advice would be appreciated.

Kind regards,
Ian.
(ian.mitchell@sds.no)

View 1 Replies View Related

Passing Parameters To Action Stored Procedures Using ADO, In Access Project

Jul 20, 2005

There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko

View 2 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Profiler - Object Names

Feb 13, 2002

In SQLServer 7 when we ran profiler we could get objectID and ObjectName.
In SQLServer 2000 these items are available in profiler but the columns are always blank.

Trying to see object name when object is opened.

View 1 Replies View Related

Using Variables For Object Names

Oct 7, 2005

I am writing an sproc for creating a table, but I don't know how I can use parameter values as object names (for example the name of the table needs to be [@sFile1+'_'+@sLinkName+'_'+@sFile2']. It seems that I could concatenate my whole CREATE TABLE string into a single variable and use EXEC to run it, but I'd prefer to be able to do it in the context of the sProc (I read that EXEC always has the current user's permissions). What is a good technique?

CREATE PROCEDURE dbo.CreateLinkTable
@sFile1 varchar(50),
@sFile2 varchar(50),
@sLinkName varchar(50)

AS

CREATE TABLE [@sFile1+'_'+@sLinkName+'_'+@sFile2]
(
[GID_ID]uniqueidentifierROWGUIDCOLNOT NULL,
['GID_'+@sFile1]uniqueidentifierNOT NULL,
['GID_'+@sFile2]uniqueidentifierNOT NULL,
CONSTRAINT ['LNK_'+@sFile1+'_'+@sLinkname+'_'+@sFile2]
FOREIGN KEY(['GID_'+@sFile2])
REFERENCES [@sFile2](GID_ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION,
CONSTRAINT ['LNK_'+@sFile2+'_'+@sLinkname+'_'+@sFile1]
FOREIGN KEY(['GID_'+@sFile1])
REFERENCES [@sFile1](GID_ID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
)

View 2 Replies View Related

Passing Variables And Column Names To EXEC

Oct 9, 2007

Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases.
I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value:
Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] =  ['+@idpartition+'] AND StoreIdentifier IS NULL')
Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'.
If I change the last line to: WHERE [IdPartition] =  2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated.
Best regards, Tim 
 
 

View 4 Replies View Related

Passing Table Names As An Argument In A Procedure

May 7, 2008

hai <br>
<p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p>
CREATE PROCEDURE outputtable @tablename nchar(10) AS<br>
begin<br>
.........................<br>
insert into @tablename (classno) values (@cno)<br>
.................................<br>
END<br>
 
<p> I get the following error message :</p>
Msg 1087, Level 15, State 2, Procedure................<br>
Must declare the table variable "@tablename".<br>
 <p>
Is there anyway i can get around this one.   I will be grateful if anyone can help me</p>

View 5 Replies View Related

Returning Database Object Names

Jul 7, 2004

hey everyone, need some help

I have a query return all the names of the table in my database via this code:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],4))<>"MSYS"));

I need to further refine the search fitting in the critera LIKE 'qry_tbl%' I dont know how to fit this in .. i tried fitting it in to the end, but nothing comes up.. I also tried querying the results of this query with another query :-P but that didnt work either .. any ideas?

In essence i need all the table names in my DB that start with 'qry_tbl' to be in a list for me..

Thx
Doc


This gives me all the names

View 3 Replies View Related

Case Sensitive Object Names?

Mar 19, 2007

My new DB seems to be "case-sensitive". I have a table named Bld_List.

Select * From Bld_List

returns all records in QA. However, if I don't follow the correct case

Select * From bld_list

I get this error: Invalid object name 'bld_list'.

Also, when I try to get rid of some unnecessary records by using this

DELETE
FROM Bld_List
WHERE (PRODUCT IN
(SELECT DISTINCT Product
FROM BldOff_Inv_Daily))

I get this error: Cannot resolve collation conflict for equal to operation.

I imagine I set something up wrong when I first built the DB, but I don't know what to look for.

Thanks

View 7 Replies View Related

Can't Save Server Names In Object Explorer

Jul 10, 2006

In Management Studio, under Registered Servers, I double-click a server name (could be sql 2000 or sql 2005 server) and it apperars in "Object Explorer" where I can work with it. I click the "Save All" button in Management Studio, exit Studio, and re-start Studio. When I do, I find that all the servers I placed under "Object Explorer" are now missing and I need to go through the "double-click the registered server" ritual again. And again, and again.

What am I doing wrong here? Why don't the servers "stick" in Object Explorer?

TIA



barkingdog



P.S. I have Sql 2005 SP1 installed on my box.

View 3 Replies View Related

Passing Table Object Into A Variable

Jul 5, 2007

Good Day guys, sorry i'm just new in SQL Server.

My problem is like this:

I want to pass Table Object to a declared variable and make View.

Usually we make like this:

"Select * from Table1"

I want like this:

"Select * from @Table1"

I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.

Please help me guys, i really appriciated everything.

Thanks,

Ero-Sennin26

View 1 Replies View Related

Passing Object Properties To Variables

Sep 14, 2006

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

View 3 Replies View Related

When To Use Fully Qualified Names ([database].[schema].object)

Dec 29, 2006

I wihsh to discuss whether to use fully qualified names:[database].[schema].objectof objects to operate (create, query..) on is good or not?If someone change order of sql code blocks in my script - this may causelose of it's context (like: use master / use <mydb>..). I wish to have mysript independed on changes like this and always produce correct result.Does using full name make use of 'use <db>' statement unnecessary?

View 8 Replies View Related

Passing SqlDataSource Object An Array As A Parameter

Feb 22, 2007

Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
 
 
        DataTable dt = new DataTable();        DataTable dt2 = new DataTable();        DataView dv = new DataView();                        
       foreach(ListItem liOptions in ListBox1.Items)       {             if(liOptions.Selected)             {                                      SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);                   dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);                   dt2 = dv.Table;                   dt.Merge(dt2);                   dt2.Dispose();                   SqlDataSource1.SelectParameters.Clear();             }       }
        if (dt.Rows.Count > 0)        {           Graph(dt);                             //Pass original datatable (dt) to Graph();           dt.Columns.RemoveAt(2);      //Reformat datatable (dt) and remove last column before binding to Gridview1
           GridView1.DataSource = dt;           GridView1.DataBind();        } else {
    errorMessage.Text = "No data was returned!"; }

View 3 Replies View Related

SQL Server Admin 2014 :: Difference In Alert Object Names

Mar 9, 2015

Same SQL Server DB engine (2012), connected with two different clients (2008 and 2012). When I check objects for performance condition alert, 2008 client shows the objects name with leading "SQL Server".But 2012 client displays just the object name and NOT prefixed with "SQL Server".we have automated alerts creation, so it fails depends on the client version.

View 0 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Copy Table Column Names From SSMS Object Browser To Use In A Query

Nov 6, 2007

I thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO

View 2 Replies View Related

SQL Tools :: Can Make SSMS Parse Command Resolve Object Names?

Jul 29, 2010

When I run the parse command in SSMS, it merely does a syntax check.  When I run through the export data wizard by right clicking on a table, it allows a query as the data source.  When I click on the parse button in the window that accepts that query, it resolves object names and notifies me of invalid ones.  I'd really like the same thing to happen when I parse in SSMS...

View 5 Replies View Related

Passing Object Variable As Input Parameter To An Execute SQL Task Query

Mar 29, 2007

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )



2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).



Please give me solutions for the above two..



View 6 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related







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