Hi there
I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.
Thanx in advance
by
yasi
Hey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?)
View 1 Replies View RelatedOur ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.
They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.
This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.
Is it possible to do stuff like that in a SP?
To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.
CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );
INSERT INTO #Tmp
EXEC dbo.MyProc
Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table
EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )
Dear Techies,I making one stored procedure, which does some operation based on aninterface hash (#) table ---- name #mydata.This stored has two section of code (seperated by parameter value 0and 1)But hash table #mydata (same name) number/name of columns changes asper call 0 or 1.e.g.when call for 0, ----> Pass 2 columns as company_cd and section_cd ininterface hash (#) table ---- name #mydata.when call for 1, ----> Pass 3 columns as Section_cd, line_cd andsubline_cd in interface hash (#) table ---- name #mydata.As a result, none of the case (0 or 1) is running properly, It givesproblem.When I execute procedure for 0 by passing #mydata with two columns---> it gives problem in 1 section codeAnd When I execute procedure for 1 by passing #mydata with threecolumns ---> it gives problem in 0 section codePlease suggest !!! If anybody have faced the same problem or have anyidea about this case.(I think passing hash table with 3 column as col1,col2,col3 can servethe purpose, but this may cause rework in my case, so looking foralternate solution)Thanks in Advance,T.S.Negi
View 1 Replies View RelatedThe tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.
View 1 Replies View RelatedI need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
on SQL Server 2005, no SP.
Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:
declare @HTR money
declare @Uniqueid varchar(15)
declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid
open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR
while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end
close cur_htr
deallocate cur_htr
...
This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?
Here's what I've got.
We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.
Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?
Hello,
I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.
Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName)
What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?
Thanks!James
Hi,
I am new to sql...
can anyone help me by giving the code to write a stored procedure to seperate a word.
For example:
If there is a string - Hi, how are you doing.
I want to seperate "Hi," and "How are you doing." using a stored procedure.
Thanks,
Avi.
hi
can i search a word in my all stored procedure
not in system stored procedure
only in my stored procedure
like i need to search ' Getdate() ' in my all stored procedure
TNX
I would like to know , is there a way to save query results in word format.
Liek we use sp_makewebtask to save query output in html format.
Thanks
Hi
Is there a way to select out the contents of sys.comments for an object (in this case, a procedure) so that it is nicely formatted?
The reason being, is that I want to update source control with all objects from the live environment (as it has become out of date.... long story). Currently, we have a single script for each object, split in directories for each object type. Anyway, I also want to wrap the objects in our standards (check for object existence, print out success/fail statements, etc).
Any suggestions to make the process more automated and less painfull? I don't want to have to script each object out, manually wrap it in standards and save as that would be perhaps the most tedious task in the world!!!!
Thanks!
Hearty head pats
Hi everyone,
I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:
Code SnippetUSE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
The thing is, I want to change and use it like this:
Code SnippetUSE pubsIF NOT EXISTS (SELECT name FROM sysobjects
CREATE PROCEDURE ...USE pubsGOALTER PROCEDURE au_info2 ...
But that does not seem to work.. I get the following error:
Code SnippetIncorrect syntax near the keyword 'PROCEDURE'
Any idea's? Any help is appreciated!
Kind regards,
Frederik
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies View RelatedBelow is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies View Relatedhi guys....how do i search a word in a table using MS Access query? it should search all the columns in the table. thanks.
Regards,
Rohit
Hi everybody,
is it possible using SQL Server search for a word on each
table on each field?
Thanks
Fabio
Hello,
I want to store the contents of an MS Word document (or a brief Word extract) in a database field. This document could contain carriage returns, boldface, etc. I have tried storing the Word doc in fields of type nchar, text, and sql_variant but they do not keep the Word formatting. I found that the "Extended Properties" attribute of a field seems to retain these properties. (Though if I recall right, Extended Properties has a length limitation and is a pain in the #*&_*^! to access)
What is the "right" way to do what I'm trying? Keep in mind that we may want to view this document under either "Query Analyzer" or print a hardcopy version. And we'd like both to be intelligible!
TIA,
Barkingdog
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
Hi everyone,
I posted a related thread before about this error below when I process a dimension. And seems that the solution by using "ClearCache" can not fingure out the issue when I want to process a mining structure...... .
OLE DB error: OLE DB or ODBC error: There is not enough procedure cache to run this procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache.
; Sort failed because there is insufficient procedure cache for the configured number of sort buffers. Please retry the query after configuring lesser number of sort buffers.
Could someone please give me some suggestions?
Your help will be very appreciated:-)
Thanks^_^
Winnie
Hello,
I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.
What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?
Any help would be much appreciated.
Thanks
Karl
Hi Alex (Kuznetsov) and All,This is to follow up with my last post, "Link two tables using partialword match".How can I UPDATE table using partial word match?How can I write a SQL statement to do so? (I am using MS-Access.)The two tables and the expected result are listed below:Thank you in advance!- Grasshopper -================================================== =====TABLE: tblStreet_Value (Table to be undated)StreetValue2---------------------123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTABLE: TblWord_NumberWordNumberValue---------------ABC123NYABC125CACBS11MACBS12TXResultStreet Value2---------------------123 ABC Street NY124 ABC Street125 ABC Street CA1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street MA12 CBS Street TX100 Apple Road101 Apple Road102 Apple Road
View 5 Replies View RelatedStructure Number1
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.
Dear all,
how can I use a store procedure to get the structure of a table like data type, length, description etc.
I have used sp_columns to get the information but it can't get the description of each field.
Thanks.
How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>
Thank you,
Dirk
Dirk.Ulrich@gmx.de
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)
go
ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO
CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)
GO
ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO
CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)
GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO
CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)
go
ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO
CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).
Any hints how to incorporate these outstanding items?
Mike B
How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you
Hi
How to write a query for table structure which looks like
SQL Design table.
thanks
asm
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..
Thank you..