Populate Schema Name Dynamically

Jun 30, 2006

We are designing a server where functions and procedures are to be kept in a single database. Our other databases will call these functions. The functions will need to reference tables in the schema being called from. Is it possible to pass a schema name to a function? If so, then how?

I tried passing something like:


CREATE FUNCTION [dbo].[getPaymentReceived2](@inSchema CHAR(30), @inAraccountid CHAR(15), @inInvoicestart SMALLDATETIME)

RETURNS MONEY

AS

BEGIN

DECLARE @outAmt money

DECLARE @arinvoiceid char(15)

SET @arinvoiceid = (SELECT ai.arinvoiceid

FROM @inSchema.dbo.arinvoice ai

WHERE ai.araccountid = @inAraccountid

AND ai.invoicestart = DATEADD(month, - 1, @inInvoicestart))

I get an incorrrect syntax error.

View 6 Replies


ADVERTISEMENT

Populate Star Schema By DTS

Dec 18, 2002

does any one know how to built a star schema by DTS:confused:

View 4 Replies View Related

RowSampling: How To Dynamically Populate The Number Of Rows

Jan 24, 2006

I'm using the row sampling Transform to perform an A/B split on some data.

Is there anyway that I can dynamically populate the number of rows box ?

I currenlty have to find out the count, then divide it by 2 and manually enter this in to the box. It would be nice if there was a property associated with this that so that I could populate a variable with a count from a SQL query then associate this with the box using an expression.

Has anyone have any ideas how I can do this?

Also is there any other way of getting an A/B split of my data using SSIS or taking it one step further getting an A/B/C split..

Thanks for your help

View 3 Replies View Related

Dynamically Populate Flat File Connection String

Mar 12, 2008

Hi,
I tried to follow the widely talked about method to dynamically populate the connection string property of my flatfileconnection manager from a variable. I keep getting the following non-fatal error.

TITLE: Microsoft Visual Studio
------------------------------

Nonfatal errors occurred while saving the package:
Error at Package [Connection manager "FFCM"]: The file name ""C:ProjectsSSISHLoadTOutputOut.csv"" specified in the connection was not valid.

Error at Package: The result of the expression "@[User::CsvFullFileName]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.


Here is what I am trying to do.
I have a foreach loop that iterates through a list of xml config files and reads the config information including the destination csv file name and does a data transformation.
So I created a flatfile connection to a csv file did my data mappings.
Created a package level variable to hold the destination file path
In the Flat file conn. manager's properties -> expression -> set the @[User::CsvFullFileName] (which even evaluates fine)

When I try to run the package I keep getting the above mentioned non-fatal error..I checked the path and it is valid. I even tried

the c:\projects\...notation and the UNC path notation...all seem to give the same error


Anyone experience this before ? any thoughts would be appreciated.

Thanks

View 5 Replies View Related

SQL Server 2012 :: Removal Of Records Dynamically Using Information Schema

Aug 14, 2015

I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.

DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),

[code]...

Would like to execute the statements generated by the results from the information schema.

View 6 Replies View Related

Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?

Aug 11, 2005

Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student.  The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID".  Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!!  Thank you

View 5 Replies View Related

Create Target Table Dynamically Based On Source Table Schema?

Sep 13, 2005

I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, 
Bryan Ax

View 9 Replies View Related

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

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 Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'.  However, the
current database schema is not compatible with this version.  You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Transferring Objects Form Schema A To Schema B In One Shot....!

May 27, 2008

I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.

I know how to do one by one...!

alter schema SchemaB transfer
SchemaA.TableA

but it will take long time...!

Thanks,

View 3 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET  3.5,  MSSQL 2005I  bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Moving Data From One DB Schema To Another DB Schema Using SSIS

May 8, 2007

Hello,



I would like to use SSIS tool to move the data from one database schema to another database schema.



For example:



Source table has

1. UserName (varchar 20) (no null)

2. Email (varchar 50) (can be null)



Destination table has



1. UserID (uniqueidentifier - GUID)

2. UserName (varchar 50) (no null)

3. EmailAddress (nvarchar 50) (can be null)

4. DateTime



Questions:



1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?

OLE DB Source, OLE DB Destination, Data Converson and .....

How do I insert Guid and Date at the same time?





2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.



Please give me some advices here.



Thanks.

View 3 Replies View Related

Adding A XML Schema To XML Schema Collection

Apr 19, 2006

I used SSEUtil to add a schema to my database but I am having problems.  Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database.  Any ideas on what I am doing wrong or why this might be happening?ThanksKevin

View 3 Replies View Related

Copy Objects From One Schema To Another Schema?

Nov 21, 2011

I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.

In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.

View 5 Replies View Related

Ddl Doesn't Populate

Feb 6, 2008

Anybody see a reason why this list won't populate?
<asp:DropDownList ID="ddlState" DataSourceID="srcStates"    DataTextField="StateName" DataValueField="StateName" runat="server">    <asp:ListItem>Select State</asp:ListItem></asp:DropDownList><asp:SqlDataSource ID="srcStates" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>"    SelectCommand="sp_States" SelectCommandType="StoredProcedure">    <SelectParameters>        <asp:ControlParameter ControlID="ddlState" DefaultValue="Select State" Name="State"            PropertyName="SelectedValue" Type="String" />    </SelectParameters></asp:SqlDataSource>

View 6 Replies View Related

How Do I Populate A Listbox From A SQL DB?

Feb 10, 2004

Sorry if this is to basic but I am just starting out. Any help is appreciated.

Basically I am attempting to populate a listbox with items from a MSSQL DB so the user can select either one or multiple items in that listbox to search on.

View 1 Replies View Related

Need To Populate Columns For Whole ID

Feb 2, 2015

I have data like below, I need to populate the ID_INDICATOR columns with the below condition

ID TASK_ID TASK_COMPLETEDTSTASK_DUETSTASK_INDICATORID_INDICATOR
112014-06-09 00:00:002014-06-11 00:00:00GREEN
122014-06-13 00:00:002014-06-14 00:00:00AMBER
132014-06-17 00:00:002014-06-16 00:00:00RED
142014-06-17 00:00:002014-06-18 00:00:00AMBER

Condition:
##########

Red = If ID due date was overdue, i.e. if last task completed after the ID end date(2014-06-18 00:00:00).
AMBER= If any task in the ID is overdue but completed before the ID end date(2014-06-18 00:00:00)
Green = If all tasks were completed on time.

I am looking for the logic to implement the AMBER for the whole ID, because the TASK_ID 3 is overdue, but completed before the ID end date (2014-06-18 00:00:00).

View 6 Replies View Related

Trying To Populate A Name Column

Sep 18, 2006

I am trying to update a name column in the following way: (I wrote a description, but I think this visual is easier to understand).

This is what I have:

name1 name2 address etc

Bob null 123 street

Sue null 123 street

Jack null ABC circle



This is what I want:

name1 name2 address etc

Bob Sue 123 street

Jack null ABC circle



I'm just trying to get 2 names on the same row if they have the same address and get rid of the spare row. Name2 is currently null. Seems simple enough but I don't know how to do it in SQL. I can do it in FoxPro, but that doesn't help me here.

Thanks for any ideas.





View 3 Replies View Related

How Do You Populate A New Index?

May 5, 2008

I added an index to a SQL Server 2005 table. How do I populate that index? I thought it might be automatically populated but the operation to add the index happened so quickly that I don't think it could have done it that quickly. Also, I expected faster performace when selecting rows based on the indexed column, but performance remains the same.

View 1 Replies View Related

Populate A Variable

Oct 24, 2006

During a dataflow - I like to populate a variable with True or False based on a value in one of the data records.

How do I do this excercise ?

 if I use a script - can someone provide me with simple script on how to populate one variable ?

 

Thanks heaps

 

 

 

 

View 11 Replies View Related

How Can I Populate Data In A One-to-one Relationship

Jul 20, 2006

Here is what I have and (somewhat understand).  I’m using Visual Web
Developer 2005 Express Edition and have setup my application to use form authentication
(which automatically creates the ASPNETDB.MDF file with several default tables
and views).  I’m using the CreateUserWizard
which is fine…but I need to collect additional information like (firstname,
lastname, address…and on..).  What I’ve
done.  I’ve created a tabled named
UserProfile and set UserId as the primary key (uniqueidentifier). 
 I then setup a 1-to-1 relationship
between aspnet_Users and UserProfile (which I think is correct).  On my UpdateContactInfo.aspx page (where
users go to update their personal information) I use a hidden label control
(UserValue) to receive the UserId during the page_load event as below:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        UserValue.Text = Membership.GetUser().ProviderUserKey().ToString()    End Sub Now with the UserID available I need to populate the
UserProfile table with the UserId, firstname, lastname, address of the
currently logged in user.   How can I do this and am I on the
right track..?

View 6 Replies View Related

Populate Arraylist From A Database

Sep 15, 2006

Hi  i'm pretty new to this, how do i connect to my database a put all the values from one column into the arraylist. Any help and a easy example would be nice Thanks in advance  Richard 

View 4 Replies View Related

Populate Binay Data

Oct 16, 2006

We have stored large text data in "Image" data type in SQL Server. Can someone tell me how to get that vlaue from image datafield. Thanks

View 3 Replies View Related

Populate The Array From Database

Oct 18, 2006

Hi,   I want to populate the array with a single column values from database(sqlserver 2000)Ex. name      ageaaaa         23bbbb         43cccc         18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala

 

View 3 Replies View Related

Populate DB Records In Array

Nov 9, 2006

Using the SqlDataReader, I am retrieveing records from a SQL Server 2005 DB table. Since I am using the SqlDataReader to retrieve the records from the DB, I have to use the Read method of the SqlDataReader like this:Dim sqlReader As SqlDataReaderWhile(sqlReader.Read)    Response.Write(sqlReader.GetValue(0) & "<br>")    Response.Write(sqlReader.GetValue(1) & "<br>")    Response.Write(sqlReader.GetValue(2) & "<br>")End WhileThe records retrieved can only be accessed inside the While loop. I want to access the records outside the While loop as well. Is there anyway by which I can do this, maybe by populating the recordset in an array variable & then using it outside the While loop?

View 3 Replies View Related

How To Populate A ArrayList With A IDataReader

Jan 18, 2007

In my data access layer class I have Populate methods on the bottom.One of the the objects, color, is an ArrayList, how do I write that?         private Product PopulateProduct(IDataReader r)        {            Product product = new Product();            product.BrandId = Convert.ToInt32(r["brandId"]);            product.BrandName = Convert.ToString(r["brandName"]);            product.Color = whatGoesHere?  (r["color"]);  return product;        }  thanks 

View 4 Replies View Related

Populate Automactly UpdateParameters

Jan 29, 2008

 Hi there!I'm trying to populate automactly a record in a table. I've tried this:   <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProducaoLigacao %>" SelectCommand="UPDATE [Producao] SET [Prodas] = @Prodas WHERE ([ProdUserID] = @ProdUserID)">
<UpdateParameters>
<asp:Parameter DefaultValue="1" Name="ProdUserID" Type="Int32" />
<asp:Parameter DefaultValue="999" Name="Prodas" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource> Is this possible? Thanks in advance! 

View 5 Replies View Related

Populate Dropdown Box From SQL In Web Matrix?

Jan 22, 2004

I have been attempting to populate a dropdown (combo) box from a SQL query (SQLConnector) in Web Matrix and I have so far been unsuccessful. I have been able to populate a DataGrid just fine from the query, so then I limited it down to one colum (so itd be in the dropdown box) and set all the properties I could find (DataSource, DataMember, DataTextField, DataValueField) (and in all possible combinations) for that dropdown box to the SQL stuff (SQLConnector for the DataSource and then used the dropdowns for the others), and nothing shows up in my dropdown box. How can I make this populate off of my SQL query properly?

Additionally I will need to do the same thing for a Checkbox List so if it is the same and you know how to do that and could let me know for that too that'd be great.

Thanks a million in advance!

View 1 Replies View Related

Two DB Tables To Populate One Control

Jun 20, 2004

I have a dropdownlist that is populated from a DB table. I would like to also include any other values that might be in another table. How do I combine these two queries, so I can get distinct values from both tables combined?SELECT Category
FROM dbo.TABLE1
GROUP BY Category

SELECT Category
FROM dbo.TABLE2
GROUP BY Category

View 1 Replies View Related

Populate Textbox From DataSource

May 15, 2006

Can someone tell me what I am doing wrong... I am trying to populate some textboxes from a table by doing a SELECT statment. 
Any help would be greatly appreciated
T.
 
Code:
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter
myConnection = New SqlConnection("Server=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True")
myCommand = New SqlDataAdapter("SELECT * FROM [CustomerSalesNum] WHERE [WorkSheetID]= '" & lblRWorkSheetID.Text & "'", myConnection)
Dim ds As DataSet = New DataSet("CustomerSalesNum")
ds = New DataSet("CustomerSalesNums")
tbUPPRice.Text = ds.Tables["CustomerSalesNums"].Rows[0]["Price"].ToString();

View 5 Replies View Related

Please Help Me Populate My Date Fields

May 7, 2004

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the {B}following[/B] but that too does not work. Can you PLEASE help. Many thanks in advance:


Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as x

View 3 Replies View Related

Using Cursors To Populate A Column

May 10, 2002

I am trying to populate a column with date/time info, I am doing this so I can use getdate() on the data for other computations (the format is mon/day/yr/hours/mins, i was told only data having mon/day/yr format can be used with getdate()). The code below says that dateyest is an invalid object name. Actually, any kind of insert I do reports the same error, no matter what column it is (securty on my account is all set, as well as for the stored proc.). I must be overlooking something very simple, right? thanks for the help!

DECLARE @dateYest datetime
DECLARE Cur668 CURSOR
Keyset
FOR
SELECT cast(date1 as datetime) dt
FROM baclosetable
OPEN Cur668

fetch next from cur668 into @dateyest

WHILE @@FETCH_STATUS = 0
BEGIN
if @dateyest is not null
Insert dateyest VALUES (@dateYest)
fetch next from cur668 into @dateyest
END
CLOSE Cur668
DEALLOCATE Cur668

View 5 Replies View Related

Using Other Columns To Populate Filename...

Feb 23, 2006

Basically like the topic says, I need to populate a filename with data from other columns and im having a tough time doing it.

Code:


UPDATE nice_cls_calls_0027
SET vcarchivepath = 'G:Nice Storageicestorage.safeautonet.netSafe Auto Task - Logger (807101)2006_feb_1SC_807101_'start_time'_'=stop_time'_'=channel'_'=cls_call_id'.aud'


I need start_time, stop_time, channel, and cls_call_id to populate the filename. The first 2 are datetime fields in the db and no conversion is needed for them, just raw data. The remaining two are smallint and raw data is needed there also. Any help would be greatly appreciated. TY

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved