Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Hi all, I'm trying to create a temporary table with one of the columns with a datetime datatype. Sql server 7.0 is giving me an error: Error 195 date is not a recognized function name. Example of my create table looks something like this: create table #temp_table(column_one int, column_two(15,2), Column_three datetime)
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem? Thanks, Dan Collins
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
I am using vs2008 with c#.I have table in my database that has email addresses stored in a column. I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task.
I am trying to write a stored procedure that will select information from a SQL table based on a specific time. For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm. Any thoughts?
I am trying to create a stored procedure that Deletes Table if there is Data.
Also stored procedure will Insert new data into table.
I have already created table. This is part of my current stored procedure.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMACjmerlos].FHLMC_Trials') AND type in (N'U')) DELETE srd.[PNMACjmerlos].FHLMC_Trials
I have a table which has data like shown in below image , I want to get count of case numbers which has responses 123 AND 125 ( case should have both responses), like this I have requirement for other responses, how can I do it in Stored procedure .
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from ( (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join )
Hello: Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows: Jan - 1 - 27 Feb - 28 - 24 Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so. Please help. Thanx
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
I have a table which is referenced in multiple stored procedures .Among them only one procedure loads data (INSERT statement) to the table. In all other procedures table is referenced in FROM clause.
My question is how do i find the stored procedure that is actually loading data into that table.
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.
Like LAT ( N38 28.025' ) and LONG (W105 52.098' )
The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.
I hope I have explained this right. The numbers in BOLD are what I need to maintain.
I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can´t find how to fix it. In my stored procedure in PL/SQL exists this lines:
TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50); A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();
Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this. this is my table info table = BusinessInfo Columns = BusinessID, BusinessName, BusinessAddress how can i create a stored procedure ?
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:
SELECT B.COMPONENT, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS) FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B WHERE B.COMPONENTID = A.COMPONENTID GROUP BY B.COMPONENT
GO
Any help getting this to run correctly would be appreciated.
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
I have a stored procedure a portion of which looks like this:
IF (CAST (@intMyDate AS datetime)) IN ( SELECT DISTINCT SHOW_END_DATE FROM PayPerView PP WHERE PP_Pay_Indicator = 'N' ) BEGIN --ToDo Here END
where: @intMyDate is of type int and is of the form 19991013 SHOW_END_DATE is of type datetime and is of the form 13/10/1999
however when I run the procedure in sql query analyzer as:
EXEC sp_mystoredproc param1, param2
i get the error:
Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
what is the proper way of doing the conversion from int to datetime in stored procedure?
