Query To Build Table With Nvarchar(max)

Jan 8, 2007

an error, incorrect syntax, was generated when the following query was tried: 

CREATE TABLE ASPNET2
(id smallint IDENTITY(1,1) PRIMARY KEY,
word nvarchar(50) NOT NULL,
definition nvarchar(MAX) NOT NULL)

 

I wanted to use nvarchar(MAX) because something I read suggested that I couldn't do editing with the controls if a variable was set to text.

 Thanks.

-Larry

View 3 Replies


ADVERTISEMENT

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.

Jan 10, 2008

HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View 6 Replies View Related

Error On Build Solution, If SSIS Project Is Part Of Build

Mar 22, 2006

Hello,

we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..

C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild

Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View 5 Replies View Related

I Need Help To Build This Query.

Jun 10, 2008

Hi,
    I have two tables called Actcodes and a another table called FundBalances...
 
The Act codes have the following format..
 
PlanId int,
ClientId int,
 StartDate datetime,
EndDate datetime,
ClientActCode char(2)
CodeDescription char(15)
so based on what the user has selected i am getting the names of each codes
 
and they have the following Rows.
PlanId         ClientId           Startdate     EndDate  ClientActCode     CodeDescription
111               1                   1/1/2008      3/31/2008     01                    Begininng balance
111               1                   1/1/2008       3/31/2008     02                   Contributions 
111               1                   1/1/2008      3/31/2008     03                   something
111               1                   1/1/2008       3/31/2008     04                   sdkfjdkf
111               1                   1/1/2008      3/31/2008     05                    dfdfd
111               1                   1/1/2008       3/31/2008     06                  dfddfs 
111               1                   1/1/2008      3/31/2008     09                  dfdf
111               1                   1/1/2008       3/31/2008     15                   dfdkfdlfk
 
and my fund balance i have the following rows..
PlanId int
Participantid int
StartDAte datetime,
end date datetime,
FundId int
Loans
Act1 char(2)
TotAct1 money
.
Act20 char(2)
TotAct20 money
and the data is as follows
PlanId   ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20
111         1212           1/1/2008  3/31/2008  15   NULL   01    15.15    02    15.48           20    12.4561
111          1212           1/1/2008 3/31/2008   45   0        01        45.12 02    453.123      20          54.00
so on and so so forth
 
tht Act1 matches the ClientActCode in the Act..
So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
 
so can i get the its respective Acts and Totacts column as  Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
 
Any help will be appreciated..
Thanks
Karen

View 23 Replies View Related

How To Build This Query

Mar 8, 2004

Hi,
Please Help me to build this query.

I have got a "User" Table
---------------------
UserID UserName
---------------------

1 Tuffy


Another Table "Groups" Table
---------------------
GroupID GroupName
---------------------
1 Manager
2 Employee
3 Sales

I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this

---------------------
UserID GroupID
---------------------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
---------------------

Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"

So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"

Please help me how to write this query.

Regards

View 5 Replies View Related

Query Build Help

Apr 24, 2007

I have a table with 2 columns:
Col1 Col2
stat1,stat2 AV,AD
stat3 TD
I need to build a query like this:
SELECT *
FROM table3
WHERE stat1=AV and stat2=AD and stat3=TD
How do I do this?

View 6 Replies View Related

Build A New Sql Query

Apr 10, 2007

Here is my problem.

I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.

I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.

Table 1

Asset Name | InstanceID | Site | Department



Table 2

InstanceID | User Name |



Table 3

User Name | Site | Department

Help will be really appreciated!

Thanks

View 6 Replies View Related

Help To Build Query

Sep 13, 2007

I need to build following query:

Each item has one or more manufacturers:

Is any way to get all the items with all manufacturers where one of manufacturers fit required expression.

I will try to explain in example:

items | manufacturers
------------------------------
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
ABC2 | skoda
ABC2 | subaru
ABC2 | lada
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge

Example for Input: *olf*
Output:
ABC1 | golf
ABC1 | toyota
ABC1 | citroen

Example for Input: *aul*
Output:
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge

Thanks

View 20 Replies View Related

How Can Build Two Tabels Together With Ms Sql Query

May 15, 2007

Hello to all,
I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together.
I used this query:
select * from Ta where Ta.Id = @ID union all select * from Tb where Tb.IdOfa = @ID
but it doesn't work and the following error message comes
"All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "

View 4 Replies View Related

New, Need Help: Build Query With Variable

Oct 12, 2007

I am building my SELECT on the fly by use of IF statements something like:
SET @searchParam = @searchParam + ' AND tblXYZ.country = ' + @searchStringCountry

Then I am trying to use @searchParam as my WHERE clause:
WHERE@searchParam

How do I get the string out of @searchParam to use as my WHERE clause?

Thanks in advance!

View 1 Replies View Related

Trying To Build An Update Query

Apr 22, 2008

I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.

I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:

This gets all of the organization, their contactId and their primary Address (as long as they have one).

SELECT ContactID, FullName, PrimaryAddressType
FROM tblContactInformation
WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'

This gets a list of all of the individuals that have an organization assigned.

SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType
FROM tblContactInformation
WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL

Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).

UPDATE tblContactInformation
SET PrimaryAddressType =
WHERE PerOrg = '1'

At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?

View 1 Replies View Related

Trying To Build Count Query

Apr 22, 2008

I have a table which tracks changes by user, department and date. I want to construct a query which returns two numbers, the first is all the changes by department on a certain date. The second is all changes by user on the same date. I know that the queries seperately will look like,

SELECT COUNT(User) As NumberByUser
FROM Table
WHERE (Date = 'DateGoesHere') AND (User= 'UserGoesHere')
GROUP BY User

SELECT COUNT(Department) As NumberByDepartment
FROM Table
WHERE (Date = 'DateGoesHere') AND (Department = 'DepartmentGoesHere')
GROUP BY Department

But how do I go about linking the two in the same query? Any help would be appreciated, thanks!

View 2 Replies View Related

Smarter Table Build - Might Split The Table

Jul 10, 2006

Hello,I am using SQL 2005 and Cognos' Data Manager. It is an ETL tool fordata warehousing.I have a problem with time it takes to load new changes, and I amseeking advice on a better way to manage the data.I have a table that tracks student attendance and it contains about 13million records. On a daily basis, there are 5,000 - 20,000 inserts and10,000 - 50,000 updates.The daily data comes for two different text files from my operationsystem; current and historical (CLSFIL and CLSHIS).The data is loaded into a staging area from the operational system,where data cleansing and other fields are added to the table.The final step is delivering the table to my target database, which isused for reporting.Heres the situation: I find it takes 45 minutes to do a relationalupdate, where only the records that changed in the last day will beloaded. However, if I choose the native API load instead of aRelational Load, it can load all 13M records in 7 minutes. The table isheavly indexedAt some point, the API load will take more time than the relationalload, (the changes and new records will remain a constant, but the filewill continue to grow).I'm seeking another solution is more efficient. I'm considering twotables for history and current and creating a view for reporting via aunion.This a good idea? How can I make the view effeicent to use the whereclause? Looking to bounce around ideas.Other Ideas?Thanks in AdvanceRob(I maintain the key relationships in the tool, not the tables. I knowI have lots to learn and improvments)CREATE TABLE "dbo"."F_BI_Class_Attendance_Detail"("CLASS_ATTENDANCE_ID" VARCHAR(50) NULL,"CLASSES_OFFERED_ID" VARCHAR(26) NULL,"CLASS_CAMPUS_ID" VARCHAR(10) NULL,"STUDENT_ID" CHAR(20) NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"SESSION_ID" CHAR(10) NULL,"SECTION_ID" VARCHAR(5) NULL,"MEET_DT" DATETIME NULL,"MEETING" SMALLINT NULL,"PRESENT" CHAR(2) NOT NULL,"SESSION_SKEY" BIGINT NULL,"STUDENT_SKEY" BIGINT NULL,"CLASS_CAMPUS_SKEY" BIGINT NULL,"CLASSES_OFFERED_SKEY" BIGINT NULL,"LOAD_DT" DATETIME NULL,"COMPUTED_DT" DATETIME NULL);

View 3 Replies View Related

Please Help Me About Build New Table

Dec 12, 2005

please help me
i need for example
when my user clicke in the button a new table build in my sql database
please help me
 

View 1 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

ASP/SQL Query Build - Myth Breaking

Jul 20, 2005

Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan

View 1 Replies View Related

BUG In SSIS OLE DB Source Build Query

Jun 28, 2007

Can anyone help me with this ?




I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :



OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)



SSIS parses this query succesfully but when i build the query it shows query



SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)


Please note :- SYSDATE IN " "




This query returns no Result Set.

View 6 Replies View Related

How Can I Build A Table Using A SP In A For Each Loop

Feb 20, 2008

I have a table of CategoryIDs and I want to increment through it passing each categoryID as a parameter to a stored procedure that returns the TOP 1 row, and build a new table from the the result.    I do not know how to pass the categoryID into the stored procedure.  I do not know how to begin building the new table.
public DataTable GetContractorCats()    {        // Generates table of all Contractor Category IDs no parameters        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings  ["ConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("GetContractorCatIDs", con);        cmd.CommandType = CommandType.StoredProcedure;        SqlDataAdapter da = new SqlDataAdapter();        da.SelectCommand = cmd;        DataSet ds = new DataSet();        try        {            da.Fill(ds, "ContractorCats");            return ds.Tables["ContractorCats"];        }        catch        { throw new ApplicationException("Data error"); }    }protected void Top1EachCategory(){    // need to build a new table row by row using a stored procedure that finds the top result from a database query    int RowIncrement;    RowIncrement = 0;    DataTable dt1 = GetContractorCats();    foreach (DataRow row in dt1.Rows)    {       I need to pass value of dt1 table ["CG_ID"] as a parameter to Stored Procedure called "GetTop1"       Run the stored procedure which returns a single row if the CG_ID is found and use its field values to build each row in a new table.       The stored procedure returns C_Name, Category_Name, C_Email, C_RCode and C_City
      RowIncrement++;     }    
 
}
 

View 1 Replies View Related

Trying To Build A Query For A TreeView / Navigation Menu

Mar 5, 2007

Hi Everyone,I'm drawing a blank here and I am hoping someone can point me in the right direction. I have a table with the following columns (some omitted)IDGUIDPageNameParentPageID I want to build a hierarchical navigation system (2-tier). The conceptual problem that I am running into was getting this information from the same table. Initially I was going to use a nested repeater but I am thinking a treeview would be better. Anyway, the problem is the query. How would I start with something like this? Let's use the following as an exampleRows(ID '1', GUID '888....', PageName 'Page A', ParentPageID '-1')(ID '2', GUID '111....', PageName 'Page B', ParentPageID '-1')(ID '3', GUID '222....', PageName 'Page C', ParentPageID '-1')(ID '4', GUID '375....', PageName 'Page 1', ParentPageID '1')(ID '5', GUID '562....', PageName 'Page 2', ParentPageID '1')(ID '6', GUID '874....', PageName 'Page 3', ParentPageID '2')
(ID '7', GUID '388....', PageName 'Page 4', ParentPageID '3') So, I want to be able to build a query so that I can do the followingPage A    Page 1    Page 2Page B    Page 3Page C    Page 4 Any help would be greatly appreciated. Thanks!  

View 4 Replies View Related

I Need To Generate The Query To Build A Database I've Designed. Please Help..

Apr 3, 2007

I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.

View 1 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Build A Table View From 2 Others Tables ???

Apr 17, 2008

Dear all,

I have the following 2 tables structure whcih I need to collect information in a single consolidation table
For that lest call Tabel1 as OnStart and table2 as OnEnd
Here is how my data tables looks like in both table

OnStart :

Name value
===============
str1 1
str2 2
str3 3
str4 4


OnEnd :

Name value
===============
str5 11
str6 12
str7 13
str8 14


What I need to do is to return data from those 2 tables in following format :

Table results that should be return from procedure call

Name value TriggerStatus
=========================
str1 1 true
str2 2 true
str3 3 true
str4 4 true
str5 11 false
str6 12 false
str7 13 false
str8 14 false

The result above means that from the TriggerStatus fields I will know if data are originated from the OnEnd or OnStart table


How can I perform this ?
Thanks for your help

regards
Serge

View 10 Replies View Related

SQL Server 2008 :: Build A Query That Put Different Types In Columns

Jun 5, 2015

I have 3 tables, a supplier table, a types table and a relationship table between the two..I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.

CREATE TABLE #Suppliers(
id INT,
nam NVARCHAR(50)

[code]...

It's possible to do this in "one" query?

View 2 Replies View Related

How To Build Gui Or Forms To Wrap SQL Queries That Are Normally Run In Query Analyzer

Jul 23, 2005

Hi all,I'm a newbie to SQL and I need help with investigating what ways arepossible to build an interface of some sort that wraps around a SQLquery script.I have a simple SQL query which we normally run inside Query Analyzerto update certain fields in the SQL DB based on FQDN provide by theuser. The user normally opens up the query, edit the script to replaythe xxxxx in the line "set @FQDN="xxxxx" with the node name and thenfires it off.I would like to know what are the ways to wrap a simple user interfacearound it so the user does not have to open and edit the scriptdirectly. A simple GUI or Excel type form interface that has a freeform text field for input and then a button to run the script.Obviously, I would like to see the output in some sort of output paneas well.Is this something that can be done in Query Analyzer ?Thanks in advance,Michael.

View 1 Replies View Related

Build Query Dialog For OLEDB Source Editor

Mar 23, 2006

Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?

View 1 Replies View Related

Build Dynamic Table Columns Issue

Jun 4, 2004

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!

J827

View 4 Replies View Related

SQL 2012 :: Build Tree From Child To Parent Table

Jan 15, 2015

I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:

/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))

INSERT INTO #T1 VALUES
( 200,3, '200-3-2-1' ),
( 100 , 14 , '100-14-1'),
( 3 , 2 , '3-2-1'),
( 2 , 1 , '2-1'),
( 14 , 1 , '14-1'),
( 1 , NULL , '1');

[Code] .....

View 2 Replies View Related

Build Hierarchy - Store Data Link In Table

Sep 3, 2013

I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this

Id-Hreid

Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get

Emp3-Emp2
Emp3-Emp1
Emp2-Emp1

View 2 Replies View Related

What Am I Missing Here? Using A Stored Proc Output To Build Table Rows

Nov 27, 2007

Hi all,

I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.

To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS

select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from

(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc

GO

The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>

This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.

Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.

I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.

Any thoughts?

View 6 Replies View Related

Access Table Import As NVARCHAR

Sep 4, 2001

When importing a Microsoft Access97 database table, I noticed it imported Text fields as datatype NVARCHAR instead of VARCHAR. Why is this, and will this cause me any problems in the future (i.e. related fields in other tables may be VARCHAR,etc)? If so, what can I do about it?
Randy

View 2 Replies View Related

Ms Access Linking Table With Nvarchar(max)

Aug 30, 2006

I'm having problems seeing the correct data type when linking my Access tables to MS SQL Server 2005. My varchar(max) fields are showing as text(255). I'm using the SQL Native Client.Has anyone else ssen this issue?

View 3 Replies View Related

Pass NVARCHAR (or Other String-type) Var As A Column Or Table Name In SQL

Jun 21, 2004

Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.

Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?

Here's a basic idea of what I'd like to do:

DECLARE @myVar AS NVARCHAR(50)

SELECT *
FROM @myVar

or

DECLARE @myVar AS NVARCHAR(50)

SELECT @myVar
FROM MyTable



I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...

View 1 Replies View Related

Compare Nvarchar(10) With Nvarchar(1000)

Sep 4, 2007

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

View 6 Replies View Related







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