I am fairly new at stored procedures. I have created some that will
go through a table and return a start date and an end date that is
dependent upon the fiscal period you want, but I then need to use
those dates in another stored procedure to retrieve the information I
need. My stored procedure looks like this.
CREATE PROCEDURE dbo.R920ExtTotal
@MthsBack Decimal OUTPUT
AS
DECLARE @sSQL AS NVARCHAR(255), @StartDate as SMALLDATETIME, @EndDate
as SMALLDATETIME
Exec @StartDate = GetMthStart @MthsBack
Exec @EndDate = GetMthEnd @MthsBack
SET @sSQL = 'Select count(extension) as Total From r920f00 Where
([date] BETWEEN "' +
CONVERT(nvarchar, @StartDate) +
'" and "' +
CONVERT(nvarchar, @EndDate) +
'")'
The problem is my variables @StartDate and @EndDate do not retain
their values after the EXEC statement and revert to 01/01/1900. How
can I get around this problem?
I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2 How can i do this? This is throwing an error. Thanks.
Hi!Can anybody give me a hint how to put sa resut from EXEC into avariable.EXEC is called:EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)....)Thanks!Mario.
Could someone explain please how to use a variable table name and still output a count from the select to another variable? @Table_Name in reality is an input parameter to a proc.
DECLARE @Table_Name VARCHAR(50); SELECT @Table_Name = 'dbo.[the tables name here]'
DECLARE @RemainingRecords INT;
-- This line does not work
EXEC @RemainingRecords = ('SELECT COUNT(*) FROM ' + @Table_Name);
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
I am building a SQL statement that returns a number.when I execute the Built SQL statment EXEC(@Build). What I need to donow is take that number that comes back and store it in anothervariable so I can do some conditional logic. Any ideas? See SQL below.Something like @Count=Exec(@Build) which I know doesnt work.Thanks,PhilDECLARE @PullDate varchar(12)SET @PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''PRINT(@PullDate)DECLARE @COUNTER BIGINTDECLARE @SELECT VARCHAR(500)DECLARE @SELECT2 VARCHAR(1000)DECLARE @BUILD VARCHAR(5000)SET @SELECT='SELECT COUNTER FROMOPENQUERY(PROD,'SET @SELECT2='''SELECTCOUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTERFROMCOLLECTOR_RESULTS,WHEREWMB.COLLECTOR_RESULTS.ACTIVITY_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCOUNT.END_DATE ) =to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =to_date('''''+@PullDate+''''',''''mm/dd/yyyy''''))GROUP BYWMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'SET @BUILD=@SELECT+@SELECT2PRINT(@BUILD)EXEC(@BUILD)--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD STOREDINTO @COUNTER so I can do a conditional statement.)if @COUNTER>=1beginprint('yes')end
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare.
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
hi , i am working on an application using c#, visual studio 2005, sql server 2005. i have a few tables in sql server 2005. i need to save the history. (i.e) all the inserts, updates, and deleats performed on the tables. can any one suggest me how can i achieve that. should i use triggers and save the changes in another table ??? waiting for your suggestion?? thank you
Please what is the best way to perform index maintenance. I use 7.0 We have been having slow server performance, and one of the options is to do index maintenance. I have researched but could not get a clear picture of what I should do. Has anybody performed the same task before? Thanks for your help!!!
Hello Friends, Iam new to this sql server arena. I have implemented a procedure which does a series of insert and update statements and all of this statements must be implemented all at once or none. But if I got error in some statements , the rest of the statements are been executed. Please suggest me a way or code snippet to achieve atomicity in a sqlserver procedure.
Scenario: For the most part we let SQL Server (2005) maintain our statistics for us. However we do have several large processes written in stored procedures. There is one main controller procedure that can call any number of other procedures. This process can take anywhere from 5 minutes to an hour+ to run (based on the size of the client). Back in the day of SQL Server 2000 we found that the performance of this procedure would diminish over time (while it was running). We implemented a queued concept of issuing UPDATE STATISTICS commands. This was done by adding a SQL Server job that ran every 10 minutes looking for new records in a table. Records where inserted at key points in these stored procedures (after large deletes, updates, inserts).
Goal: Now, with all that background and with 2005, I'd like to review this concept and remove this implementation if possible, or at least remove the close association of maintaining the statistics from the business jobs. In 2005, are there better ways to monitor and maintain statistics at more of an administrative (but automated) way?
Our database(s) are all over the place - no documentation - lot's ofrubbish and unused stuff.I'm managing a project focusing on data quality that covers codechanges, alterations to DTS packages, schema changes etc etc.What I'd like to do is see where the bit I want to change is beingused.that might mean what stored procs use a field and what sprocs use thatsproc.maybe it's which dts packages use a sproc (and again up thehieararchy)The list is a long one but basically I need to know what the effectsare of changes.Is there a tool out there that lets me navigate a database to thatlevel of detail - I understand something along the same lines isavailable for MS Access but I can't find it for SQL Server.Thanks
I am a beginer in SQL Server. I have developed a simple accounting application in VB and SQL. Now I have successfully completed my application. Now I want to deploy it to my client. So I installed SQl Server and required VB components in the clients computer. I also created 'sa' login and secret password only know by me. I thought my data in that clients computer was full safe but later on i found that we can also connect to the sql server using the NT administrative account and easily change the data of the database. So now I am worried that if someone enters and access the clients computer with administrator's password then he/she can change my data resulting the corruption of the data. So is there any way that I can prevent the access the database to the client with the NT administrative account or any way 2 track the way the data changed?
hi all, i am working on portal site where i have created 18 tables in on database, i dont know weather i am right or wrong . Should i continue with the same or create two tables one will be master and another will contain common fields. but if i will create one table for all then what will happend. please tell me what to do and why? asap please............ Thanks for spending ur valuable time for me.
Hello,I have a large set of data that I have set up as a partitioned view.The view is partitioned by a datetime column and the individual tableseach represent one month's worth of data. I need to keep at least twoyear's worth of data at all times, but after two years I can archivethe data. A sample of the code used is below. It is simplified forspace reasons.My question is, how do other people maintain the database in this typeof scenario? I could create all of the tables necessary for the nextyear and then go through that at the end of each year (archive tablesover two years, add new tables, and change the view), but I was alsothinking that I might be able to write a stored procedure that runsonce a month and does all three of those tasks automatically. It seemslike a lot of dynamic SQL code though for something like that.Alternatively, I could write VB code to handle it in a DTS package.So, my question again is, how are others doing it? Any suggestions?Thanks!-Tom.CREATE TABLE [dbo].[Station_Events_200401] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE TABLE [dbo].[Station_Events_200402] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE VIEW Station_EventsASSELECT event_time,another_columnFROM Station_Events_200401UNION ALLSELECT event_time,another_columnFROM Station_Events_200402GO
Hi,What is the preferred way to maintain SQL tables on a remote host?I am a newbie to building ASP.NET websites on a remote host.A stumbling point has been the maintenance of SQL tables on the remote host.I understand about doing complete backup and restores,but I am seeking a quicker way to maintain individual files.I would like to click and edit but instead am going through the following 30+ clicks.Is there a easier way?Thanks. For example, what I do now to build a new data table for a hosted website.1) Design table 1a) Name 1b) Fields & Types 2) SQL Server Management Studio Express (assuming existing database) 2a) Select Database & Tables 2b) Add new table 2c) Add fields, Key must be INT for ACCESS 2d) Save as (Name_Table) 3) MS Access (requires ODBC to be setup first through the Windows control panel) 3a) Tables / New / Link / ODBC /Machine_Data_Source 3b) Pick table 3c) Edit data, as needed 4) To transfer data, first:Select the database in the VWD solution explorer, then right-click and select the new "Publish to Provider" 4a) Database Publishing Wizard 4b) Choose table to script a backup from 4c) Build script & Copy 5) Start Ipswitch FTP ( this step can be rplaced by 6e below) 5a) locate folder & sql script file and choose destination directory 5b) Transfer file 6) Login to remote host host (1and1) 6a) MS SQL Administration 6b) Admin (MyLittleTools Admin) 6c) Tools 6d) Quey Analyser 6e) Paste script (from step 4) 6f) Submit (Run) 6g) Verify table built FYI: Script to build and populate the new table "Name_Table"Built by step 4c above, pasted into remote Hosts Query Analyzer by step 6e above. /****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))DROP TABLE [dbo].[Name_Table]GO/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[Name_Table]( [ID] [int] NOT NULL, [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Acsz] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Fax] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Name_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))ENDGOINSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (3, N'Third name', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables in SQL dbs which hold a lot of date consisting of statistics, errors, logs etc. They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.
Currently all of our tables in several databases have the following columns:
user_added (this is nvarchar) host_added (this is nvarchar) date_added (this is datetime) user_modified (this is nvarchar) host_modified (this is nvarchar) date_modified (this is datetime)
Right now our policy is that (a) the _added columns use defaults to populate the data on INSERTS and triggers are generated to update the _modified fields upon an UPDATE of the table.
Our practice has been (a) to manually create these fields in our scripts as we create new tables in our system and (b) create triggers to perform the update anytime we create a new table.
This practice has been fine until recently where we have been outsourcing some of our development and not all of our standards have been adhered to, including this one. I'd like to look at alternatives for somehow maintaining these concepts outside of our development workflows.
The first thing I'd like to inquire about is regarding options to eliminate having developers include these columns in the CREATE TABLE statements. Is it possible in SQL Server 2005 to capture when an CREATE TABLE statement is executed and override/append to the initial CREATE TABLE statement?
The second thing I'd like to inquire about is regarding options to eliminate having developers write the initial trigger that maintains the _modified fields. I guess if there are options to capture when a CREATE TABLE statement is executed, we could possibly generate a CREATE TRIGGER statement against that object as well?
Another idea I would like thoughts on are using some sort of 'table inheritence' to store this information for all objects in our database? This idea come up when I saw this article - http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server. Do you think the situation I explained here would fall into this concept?
I'm also open to any other thoughts and/suggestions.
I have a asp.net website with SQL 2005 DB . DB size of 1.5GB with ~10 tables in it. The largest table has 200k of records in it (website users table), with 500 new records every day.
I've setup this database 4 months ago and didn't touch it since then. I really have no knowledge what SQL needs in terms of index maintenances / hard drive maintenances.
Lately , the website searches started to be really slow , and I started to get timeout error and deadlock errors. I have a few indexes for each table based on the recommendation MS-SQL Database tuning advisor gave me.
Some of the index's are : Page fullness : 99% Total Fragmentation: 24%
Other are : Page fullness : 65% Total Fragmentation: 99%
I guess I need to start maintaining the DB , defragmenting index or hard-drive? Can anyone help me and provide me with guide/information on what is needed to be done in order to keep SQL running fast and happily? or a guide on defragmenting index's and how ofen do i need to defrag?
If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?
Example: Both salespeople sync the following data down: OrderId Desc 1 Order 1 2 Test Order
Both salespeople go offline and add orders Salesperson 1 adds: OrderId Desc 3 Joes Order
Salesperson 2 adds: OrderId Desc 3 Kathys Order
Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.
There are some perculiar goings on happening on my servers. Plans across servers have been doing funny things like dumping back ups in different folders, some jobs have been hanging, so on and so forth.
And I think it's occuring after modifying SQL Server 2000 plans with SSMS. It's so intermitent, it's hard to put my finger on exactly when it's happening. It may have not even been reported yet. Fixing the anomolies are achieved by recreating the jobs (a matter of unticking and ticking the boxes in the Maintenance Plans).
In SQL 2000, if you create more than 1 schedule on a job that was itself created using a maintenance plan, SQL Server returns a message along the lines of "Dude, do this and weird things might happen". The message doesn't say what exactly, just that it can no longer guarentee the integrity of the plan. And indeed, weird things does happen, I tested it once.
I'm guessing this is a similar problem, but has not yet been pinned down. Has anyone come across this when using SSMS to maintain 2000 plans.
At this stage, I would like to point out that this is not a slagging off Maintenance Plans thread... I'm looking in the direction of anyone who's name starts with the letter Kristen or Tara
Drew
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."
I am using RGB HEX #s in my report for some colors. It shows up fine when rendering to a browser, however when exporting to Excel my custom colors don't come over. I've tried creating a report.xlt file with my custom colors in it, but when I export to Excel it uses the standard Excel palette instead of the custom palette in my report.xlt.
Any ideas on how to retain my custom colors when exporting to Excel?
I am very new to SQL Server and am just starting to look into replication as a possible solution to a problem I have. I have a local database which will be running on either MSDE or SQL Express 2005. I have a database on a webserver running SQL Server 2005 Standard which I wish to keep as an exact copy of the MSDE/Express one. If data is changed in the local db then I want the webserver db to be updated with this data, preferably as the change occurs, but it could be on an hourly schedule if required.
If data in the webserver db is altered then I do not wish for it to update the local db, but I would like it to be assumed that the change is erroneous and reset the item to whatever exists in the local db. In other words, should a table be erroneously dropped or something from the webserver db then the table would be recreated when it syncs with the local db (rather than propogating the change back to the local db and thus losing local data).
Basically I want the web db to be an exact mirror of the local db as often as possible. Given that the webserver is running SQL Server 2005 Standard and the local machine will be running either MSDE or SQL EXPRESS, is this achievable and what would be the best way of achieving it? Should I use merge replication or database mirroring? I have succeeded in getting 1-directional merge replication working by setting the webserver up as a publisher and distributor and setting up a push merge subscription on the local machine with an ExchangeType of 1. However, this won't correct the data in the web db if it is compromised until it is altered locally.
It crossed my mind that I could write a small application to utilise tablediff to compare each table in the DBs and get it to correct the web db if required on a regular basis. Would this be a silly approach?
What are the basic processes for maintaining an SQL SERVER 2005 Database? Is there a top ten list of things you should do periodically to all SQL SERVER 2005 databases?
In my scenario I have about a dozen of flat files (Text files),that I have to import in SQL Server 2005. I am using Flat File connection manager to carry out tha task.Flat files contains data generated from oracle. When I import data from these text files into SQL ,the main problem lies in converting number(p,s) data type column of Oracle(In text file) to numeric(p,s) data type of sql server 2005.
Number(p,s) data type looses all it's digits after decimal to zero during import process. For example
1.2434234390 (from text file,number(p,s) type of oracle) converts to 12.0000000000 (numeric(p,s)) of sql server 2005.
Is this this any workaround to this problem.I urgently need help.
I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.
I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.
Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
