I create a script like below:
GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectID AS NVARCHAR(128),
@TableName AS NVARCHAR(128);
SET @ProjectID = N'EPA';
[code]....
The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'
how to debug?
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
I have a function which accepts table parmeter. i have created a view and i need to use this function.
How can i do it. how can i pass dim table into the @table variable
select * from dim cross apply fnc_user(@table variable)
When I right click on a table and click Script Table as --> Create to --> it scripts out:
CREATE TABLE [dbo].[Test](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Address] [varchar](50() NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Test] ADD [City] [varchar](50) NULL
Why is there an Alter table statement? I’m assuming this table was altered at some point but why is SQL not just doing a create table and how is it keeping track of these changes?
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)
[code]....
first of all hi all, my problem is, i want to create a table which name from my parameter
what should i do ?
here my code
CREATE PROCEDURE ProcedureName
-- Add the parameters for the stored procedure here
@ype nvarchar(15)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE [dbo].[@ype](
[TeklifEM] [bigint] NOT NULL,
[Cinsi] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Adet] [int] NULL,
[Birim] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirimFiyat] [money] NULL,
[ToplamFiyat] [money] NULL,
[TeklifId] [bigint] NULL,
CONSTRAINT [PK_ype] PRIMARY KEY CLUSTERED
(
[TeklifEM] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
Im trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.
Need creating a stored procedure which creates a table.
View 3 Replies View RelatedI pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]
[code]...
what would be the TSQL in trying to create a new table with date-time format ending via a select into like:
select
*
into tblResults_
+
SELECT
CONVERT(VARCHAR(10),GETDATE(),112)
+
'_'
+
REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','_')
from qryResult
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus can you pass a parameter value to the CTE that I just listed above? If so, show t-sql to accomplish this goal?
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
Declare @tragetdb Varchar(max)
SET @tragetdb='xyz1'
Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT='+''+ ' @targetdb'+ ''
print @RestoreCmd
O/p:
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= @targetdb
But i am looking for
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= 'xyz1'
i tried using "" + "" , ' + ' , no luck..
I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.
This is the gist of what I need to do in a stored procedure
DECLARE @ID1 INT,
@SQL1 VARCHAR(8000),
@STATUS VARCHAR(10),
@DATE DATETIME;
SET @ID1 = 350719;
SET @STATUS = 'COMPLETED';
SET @DATE = GETDATE();
SELECT @ID1;
SELECT @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ' + CAST(@ID1 as nvarchar(30)) + ''')'
SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE;
EXEC (@SQL1);
In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast and convert values and that does not work. The query that I am trying to use is the following:
DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)
Thus can you show me the t-sql 2012 that I can use to accomplish my goal?
I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
I've a scalar function which is killing my performance. I've been used the SQL profiler and also DMVs to catch execution information. I'd like to store the value received by this function and also the time that it happened, but I can't think in a way to do it.
View 5 Replies View RelatedIn temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)
[code]...
In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?
create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY
[code].....
I have 3 requests to do and all 3 are below in SQL , i am planning to keep them as job and run on regular intervals as per request ..
1.Poll the contract approval table for no prepared new contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
2.Poll the contract approval table for no new signed contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE())
BEGIN
SEND DBMAIL ---i have this part working already
END
3.Poll the lead table for no new leads. Check every 5 minutes. 24/7.
IF NOT EXISTS (SELECT * FROM Lead WITH ( NOLOCK ) WHERE DateCreated > DATEADD(MINUTE, -5, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?
a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back
b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
I'm pretty new at this, how the heck do you edit and create parameters? Not really looking to edit code yet, just want to know how to use the interface to do this.
View 4 Replies View Relatedhello all i am new here,
my question is how i can activate http in sql server 2005
i want to run a helpdesk application but i cant make a new database.
it has something to do with create endpoint, i have no knowlege off sql server 2005 its not my field
can someone help me please?
I have got a table as follow:
CREATE TABLE [dbo].[NozzleAllTbl] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[nBDId] INT NULL,
[nShellId] INT NULL,
[nTDId] INT NULL,
CONSTRAINT [PK_dbo.NozzleAllTbl] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nBDId] FOREIGN KEY ([nBDId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nShellId] FOREIGN KEY ([nShellId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nTDId] FOREIGN KEY ([nTDId]) REFERENCES [dbo].[NozzleTbl] ([Id])
);
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
NozzleTbl_1 = NozzleAllTbl.NozzleTbls[0]
NozzleTbl_2 = NozzleAllTbl.NozzleTbls[1]
...
NozzleTbl1_1 = NozzleAllTbl.NozzleTbls1[0]
NozzleTbl2_1 = NozzleAllTbl.NozzleTbls2[0]
....
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedWhat I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int
set @compare = 8
declare @table table
(
Number int
)
insert into @table
values(1),
(2),
(3),
(4),
(5),
(10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
Below is the query for my procedure
ALTER PROC [dbo].[sp_GetInvitationStatusTest]
(
@invited_by NVARCHAR (50)
)
AS
BEGIN
-- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by
select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?