Generating Tables Programatically
Jun 7, 2008
hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005
using the following
create table ip
(
id int IDENTITY NOT NULL,
ip varchar(15) NOT NULL,
hostname varchar(128) NOT NULL,
primary key(id)
) ;
i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens
its been a while since i did sql but i think the sql is ok....
how or more specifically where is the appropriate place to run this code
thanks
simo
stored as a new query
View 1 Replies
ADVERTISEMENT
Oct 4, 2006
Hi,
I want to drop a Global temp table within a stored procedure.
But first, I want to check it's existence.
So, what I would like to do is something like this
if exists
(select * from sysobjects where name like '##globaltemptable')
drop table ##globaltemptable
But I don't think the global temp tables get stored in sysobjects.
Any suggestions?
Thanks in advance
View 2 Replies
View Related
Sep 7, 2007
Hi ,
Can anyone help to generate scripts for all the tables in the db using Transact SQL.
Thank you
Necessity is the mother of all inventions!
View 12 Replies
View Related
Jan 14, 2015
There are two tables as below:
Table 1
IDValue
F001A,B,C
F002B,C,D
F003A,C
Table 2
IDValue
D001A
D002B
D003C
D004D
what is best way to generate one table as below:
New table
F001D001
F001D002
F001D003
F002D002
F002D004
F002D003
F003D001
F003D003
View 4 Replies
View Related
Jul 20, 2005
I would like to find a utility that can scan through an existing SqlServer 2000 database and create scripts to re-insert/re-populate thedata into another DB with the same table structures. Like a back-uputility.Can anyone recommend one (or tell me which ones to avoid)? Iwould like one that takes dependencies between tables intoconsideration.Thanks
View 1 Replies
View Related
May 23, 2007
I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.
I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.
If anyone has any insight to this, it would be greatly appreciated. Thanks,
View 1 Replies
View Related
Feb 8, 2006
hi, I'm using sql server 2005 standard, and I want to be able to move my local database to another server, but I can't figure out how to script the database and the data so that I can just run one script to move the whole database. this can be done right? I can't imagine that such an obiviously necessary tool would be intentionally left out, so I'm figuring that I'm just a doofus and don't know where the option is...
can anyone help? thank you!
-SelArom
View 2 Replies
View Related
Dec 26, 2007
Hi,
I have a little question. I searched google, and could not find good answer for this one.
I have a stored procedure that returns two tables. Usually I generate a dataset out of a stored procedure by dragging it to the dataset.
When I drag this one it creates a DS with only one table, the first one. How can I make it use both tables?
Thank you.
View 2 Replies
View Related
Aug 2, 2007
Hi...
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
View 7 Replies
View Related
Feb 11, 2014
Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
[URL] ....
View 7 Replies
View Related
Sep 19, 2005
Carlos writes "I am trying to create a sp that would jump from one DB to another using the USE command. I built a string and then I tried to run EXECUTE(@SQLString) with no lock. This is the sample code I am using:
declare @DBName varchar(200)
declare @SQlString varchar(300)
set @DBName='model'
set @SQlString = 'use '+@DBName
execute(@SQlString)
Is there any way I can accomplish that?
Thanks
Carlos"
View 2 Replies
View Related
Sep 16, 2005
Using ADO.NET Or regular queries on SQL Server, is there a good way ofdetermining if a Stored Proc will be returning a result set or if it isa non-query that does all I/O through parameters?Thanks.
View 1 Replies
View Related
Dec 27, 2007
Hi
I have a main report and sub report. The subreport has some fields like SYS, LOC, MSG etc.
But the report should display the values filtered basis on system. i.e. I must have a sub report
for SYS1 , SYS2 , SYS3 and so on. I can create a sub report for each system. But I want to create only subreport and
call them from the main report. How do I do this?
Thanks
Sai
View 5 Replies
View Related
Nov 18, 2006
Can anyone please direct me to an easy to understand Description, Article or Video tutorial that shows an example on how to programatically connect to an SQL database and retrieve data from a collumn using C# and VISUAL WEB DEVELOPER 2005 EXPRESS EDITION. Also, what namespace should be imported to do this? (From what I have gathered out of the help, it seem that System.Web.UI.WebControls is the one)
I am trying to create a "news letter" web page. I have a basic form and when I hit "send" then the code in the background should access the database with all the recipient info, and retrieve all the E-mail addresses, Names, etc. and include it in each e-mail.
( I don't know if it is just me, but the help included with VISUAL WEB DEVELOPER 2005 EXPRESS EDITION is extremely difficult to understand - from a beginner point of view).
Thanks!
Regards
Jan
View 1 Replies
View Related
Jul 31, 2007
Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
View 7 Replies
View Related
Oct 1, 2007
Hi All,
I am having problems getting values out of an sqldatasource.
I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.
the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.
The insert is fine, i just don't know how to get the values out of the first 'select source'
Any pointers or suggestions most appreciated.
Cheers
View 2 Replies
View Related
Nov 12, 2007
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
View 3 Replies
View Related
Jun 10, 2008
I have a list box populated by a SqlDataSource with a basic statement of Select name where location = @location. Location is bound to a dropdownlist control. I want to have a checkbox for the Location dropdownlist and a checkbox for another dropdownlist (for the alphabet) that will select only those people with a last name of the letter selected. Changing the checkbox would change the sql statement.
OK so there are two questions here:
1. How can I write a sql statement that would select only people whos last name begins with a certain letter. Would it use some sort of convert function to a string with only 1 letter?
2. How can I programatically change the sqlDataSource's 'select' statement. I need to switch between sorting by location and sorting by alphabetical last name. All of my previous experience has had me configuring the datasource in the asp.net code.
Thanks in Advance.
View 7 Replies
View Related
Nov 25, 2003
Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.
How can I solve this problem.
Any help is appreciated!
Krish Chandra
View 1 Replies
View Related
Nov 24, 2004
OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"
and transforms it into something that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"
My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?
Any help would be appreciated! Cheers.
View 2 Replies
View Related
Jun 16, 2005
I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the following with indexes, RI, ect. :CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL , [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO
CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO
CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]GO
CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]GO
CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ( [CustomerID] ) REFERENCES [dbo].[Customers] ( [CustomerID] ), CONSTRAINT [FK_Orders_Employees] FOREIGN KEY ( [EmployeeID] ) REFERENCES [dbo].[Employees] ( [EmployeeID] ), CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY ( [ShipVia] ) REFERENCES [dbo].[Shippers] ( [ShipperID] )GO
View 1 Replies
View Related
Nov 25, 2005
HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode Case "Category" sqldatasource1.SelectCommand = "productsbycategory" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add querystring parameter Case "Wishlist" sqldatasource1.SelectCommand = "productsbywishlist" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add Session variable parameter Case Else sqldatasource1.SelectCommand = "allProducts" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks
View 2 Replies
View Related
Jul 20, 2001
Hi ,
Could anybody point me to an NT/DOS script ( e.g a batch file ) that could check if the MSSQL Server and SQL Server Agent Services are running ?
I am trying to write the following script to check for the status. Each of the following commands gives a 1 as output if the service is running and 0 if it is not. The problem I am facing is that I am not able to check the output after the command is executed. I am neither able to assign it to a variable or include the whole command in an IF statement.
net start | find /c /i "MSSQLServer"
net start | find /c /i "SQLServerAgent"
Is there any other way to do this programmatically ? Any help is greatly appreciated.
Thank You,
Srini
View 4 Replies
View Related
Apr 11, 2002
What is the best way to programatically stop/start SQL Server? I want to stop it to do backups and then restart it.
I was hoping to do it within SQL Agent but it depends on SQLServer so you can't do a restart.
Any help greatly appreciated.
View 1 Replies
View Related
May 15, 2008
Hi Guys,
I am trying to figure out a way to restore a database programatically. Here is how I do it now. Right click on the database, select Restore. Browse out to my computer and find the .BAK file. Select the database and go into 'Restore Options' I have to physically change the .MDF and .LOG paths since I am getting this backup is not coming from my computer. I select 'Overwrite the existing database' then click restore.
My problem is that I have to change the paths to the MDF and LOG files. How could I automate this? Thank you!!
View 7 Replies
View Related
Jul 23, 2005
use this-is-a-stupid-namereturns an errorFor that matterselect *from this-is-a-stupid-name.dbo.anytablereturns an errorI've tried single quoted, double quotes, escaping. In the SQL editor I canselect the name from the dropdown and I'm OK - but I'm writing a perlscript to connect to the database.I didn't create the database and don't know how it was created.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Oct 5, 2005
Using Sql Srv 7I know I can use the system sp's sp_stored_procedures and sp_sproc_columnsto determine all the sps in a db, and what input parms there are for aparticular sp... but... if the sp returns a result set, is there a way tofind out the stru of that in a similar manner???
View 1 Replies
View Related
Mar 5, 2008
Anybody know how to or if we can programatically insert a pagebreak. in a table. Yes, I know about the ones at group footer and header these arn't doing what I need.
vbwrangler@yahoo.com
View 1 Replies
View Related
Jul 10, 2007
Hi All,
I want to programatically deploy the report in VS2005 environment,ie.from an asp.net page if i click deploy report Button ,the report should be deployed into the report server automatically.So that i can view the report in report server.
the input to the asp page would be the.rdl file...
If anyone could help then it would be good.
thankyou.
View 5 Replies
View Related
Dec 5, 2006
Need to ask three questions regarding this component I am creating programatically:
1) How do you select "Modify the SQL statement" programatically.
2) How do you change the actual sql.
3) How do you add a parameter, also programatically.
I have manged to select "Enable memory restriction" by using
instance.SetComponentProperty("CacheType", 2);
but can't add a paramater and modify sql statement. Have used
instance.SetComponentProperty("SqlCommand", statement); and
instance.SetComponentProperty("SqlCommandParam", statement);
with no luck. Any ideas, have searched the web but found nothing.
Thanks.
View 1 Replies
View Related
Apr 23, 2007
Hi,
I am able to create a rdl file programatically.I want to deploy the RDL file programatically in Report Server.Any help is appreciated.
Thanks,
Prabu
View 6 Replies
View Related
Oct 5, 2006
Lets say you have to retrieve some SQL.Then you have to go through those records and delete some items, change some values, and even add columns and rows...based on logic that depends on the data you got.Thanks.
View 2 Replies
View Related
Oct 10, 2006
This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column... One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization. I need to be able to read this value so I can assign it to the Page.'First Do a Database ConnectionIf Not Page.IsPostBack Then'Start by determining the connection string valueDim connString As String = _ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim myAdapter As New SqlDataAdapter(myCommand)myAdapter.Fill(myDataSet, "SEO")'Create a DataViewDim myDataView As New DataViewmyDataView.Table = myDataSet.Tables("SEO")'Ok...this is where I'm lost. Now how do I look up the value for the column "Title" in this Dataset'Close the connectionmyConnection.Close()'End database connectionsEnd Using
View 6 Replies
View Related
